case in a where clause issue - 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

SQL Server 2000 Forums

SQL Server Development (2000)

case in a where clause issue

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-08-18 : 11:22:16

Hi guys
Can someone help me with the following syntax please, I don't think the case statement is right and is coming up with a error by the = sign :

SELECT TOP 1 perc
FROM prodcost_hardcode
WHERE prodcode = @prodcode and island = @island and case when @store in (181,270,113,210) then store_type = '1'
when @store in (103,104,108,109,110,111,112,117,180,185,186,201,204,202,203,278) then store_type = '2'
when @store in (114,105,211,173,118,184,205) then store_type = '3'
when @store in (190,271,272,273,274,276,279,208,209) store_type = '4'
when @store in (182,183,206,280,207,281,189) then store_type = '5' end
order by [timestamp] desc, perc desc

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-18 : 11:29:11

did u try :

SELECT ... WHERE prodcode = @prodcode and island = @island and
store_type = case
when @store in (181,270,113,210) then '1'
....
when @store in (114,105,211,173,118,184,205) then '3'
....

Srinika

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-18 : 11:29:56

store_type = case when @store in (181,270,113,210) then '1'
when @store in (103,104,108,109,110,111,112,117,180,185,186,201,204,202,203,278) then '2'
when @store in (114,105,211,173,118,184,205) then '3'
when @store in (190,271,272,273,274,276,279,208,209) then '4'
when @store in (182,183,206,280,207,281,189) then '5' end

Why not create a table of store codes / types?

==========================================
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.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-08-18 : 11:35:03

thank you thank you thank you.......thank you Sri Lanka

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-08-18 : 11:39:16

I would do Nigel thanks but my exsisting table contains store codes and bands within the same column so I need to filter out the bands first then look for the individual store codes.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-18 : 12:05:29

quote:
Originally posted by Jim77

.......thank you Sri Lanka

Its my country

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-19 : 03:41:33

quote:
Originally posted by Srinika

quote:
Originally posted by Jim77

.......thank you Sri Lanka

Its my country

Srinika

Srinika, you do your country proud

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-21 : 01:43:07

>>but my exsisting table contains store codes and bands within the same column

If possible store them in seperate columns

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources