Case - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Case

Author

Topic

issammansour
Yak Posting Veteran

51 Posts

Posted - 2007-10-08 : 10:45:00

This is my first time using case statement; I am tried to case the value of a field on a select statement as follows:

Case
When len(subcode) <> 0 then (#f.catcode + #f.subcode AS ITEM_NO,)
when len(subcode) = 0 then (#f.catcode) AS ITEM_NO
else '' as item_no

Can anyone help me with this?

Best Regards

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-08 : 10:48:07

select case
when len(#f.subcode) <> 0 then #f.catcode + #f.subcode
when len(#f.subcode) = 0 then #f.catcode
else ''
end as item_no
from #f

(I put and instead of end).
(and left in too many ITEM_NO's and brackets)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

issammansour
Yak Posting Veteran

51 Posts

Posted - 2007-10-08 : 10:51:05

I have the following error:=

Incorrect syntax near '<'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-08 : 10:52:43

How can ELSE be? A length is either zero or not zero.

CASE LEN(SubCode) 
	WHEN 0 THEN #f.CatCode
	ELSE #f.CatCode + #f.SubCode
END AS ITEM_NO

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-08 : 10:52:52

When the ELSE part will be executed?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-08 : 10:54:43

Actually if len((#f.subcode) = 0 then it's an empty string so I think your code is

select ITEM_NO = coalesce(#f.catcode + #f.subcode, '')
but I think you might mean
select ITEM_NO = coalesce(#f.catcode,'') + coalesce(#f.subcode, '')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-08 : 10:56:44

Good catch!

E 12°55'05.25"
N 56°04'39.16"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-08 : 11:00:32

I changed the code I posted a fair bit

select case
when len(#f.subcode) <> 0 then #f.catcode + #f.subcode
when len(#f.subcode) = 0 then #f.catcode
else ''
end as item_no
from #f

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

issammansour
Yak Posting Veteran

51 Posts

Posted - 2007-10-08 : 14:48:01

i am still haveing the case issues with the following final statement:-

SELECT #f.catcode,
#f.subcode,
'' AS sercode,
'' AS ITEMNO,
CASE LEN(SubCode)
WHEN 0 THEN #f.CatCode
ELSE #f.CatCode + #f.SubCode
END AS ITEM_NO,
CASE LEN(SubCode)
WHEN 0 THEN ''
ELSE #f.SubCode
END AS FATHER,
CASE LEN(SubCode)
WHEN 0 THEN category.catdesc
ELSE category.catdesc
END AS ITEMDESC,
'' AS UNIT,
0 AS PIECES,
0 AS qoh,
0 as lastone
into #cur1
FROM #f,category
where #f.catcode = category.catcode or #f.subcode = category.subcode

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-09 : 04:40:29

Whats the issue now?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 04:42:50

Reformatted for easier reading

SELECT		#f.catcode,
		#f.subcode,
		'' AS sercode,
		'' AS ITEMNO,
		CASE LEN(SubCode) 
			WHEN 0 THEN #f.CatCode
			ELSE #f.CatCode + #f.SubCode
		END AS ITEM_NO,
		CASE LEN(SubCode) 
			WHEN 0 THEN ''
			ELSE #f.SubCode
		END AS FATHER,
		CASE LEN(SubCode) 
			WHEN 0 THEN category.catdesc
			ELSE category.catdesc
		END AS ITEMDESC,
		'' AS UNIT,
		0 AS PIECES,
		0 AS qoh,
		0 as lastone
into		#cur1
FROM		#f,
		category
where		#f.catcode = category.catcode
		or #f.subcode = category.subcode

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 04:44:54

[code]~~CASE LEN(SubCode)~~
~~WHEN 0 THEN category.catdesc~~
~~ELSE category.catdesc~~
~~END AS ITEMDESC,~~

category.catdesc AS ITEMDESC,[/code]

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-09 : 04:48:25

Probably he meant:

CASE DATALENGTH(SubCode)
	WHEN 0 THEN ''
	ELSE category.catdesc
END AS ITEMDESC

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources