Issue With Where Cluase Case Statment with I

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 2005 Forums

Transact-SQL (2005)

Issue With Where Cluase Case Statment with I

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2009-07-16 : 14:09:27

Hello All,

it in oracle , but the problem i am getting in Case statement so please help me

i have below query which is working fine, (it returns all rows that matched with 101 facility Num)

//initially i have a local variable and assigned 0 to it, i will use it in query

define fecval=0

select *
FROM hsi.ChartQueueTxLog cq, hsi.Chart c
WHERE
c.FacilityNum in
(case
When &fecval = 0 then 101
else 108
END)
AND cq.ChtNum = c.ChtNum
AND cq.ExitTime != TO_DATE('1964-01-01','YYYY-MM-DD')
AND cq.AvailableTime != TO_DATE('1964-01-01','YYYY-MM-DD')
AND cq.ExitTime >= cq.AvailableTime

now how can i change the above statment to give results

but i would like to add one more value to inlist
i.e if &fecval=0 then 101,102,103 else 108 how can i achive this

i tried with below query i got error

select *
FROM hsi.ChartQueueTxLog cq, hsi.Chart c
WHERE
c.FacilityNum in
(case
When &fecval = 0 then 101,102,103
else 108
END)
AND cq.ChtNum = c.ChtNum
AND cq.ExitTime != TO_DATE('1964-01-01','YYYY-MM-DD')
AND cq.AvailableTime != TO_DATE('1964-01-01','YYYY-MM-DD')
AND cq.ExitTime >= cq.AvailableTime

Can you please help me to get the results of 101,102,103 please

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-16 : 14:13:13

you can return only a single value based on case conditions. you cant selectively return resultsets for that you need seperate selects within ifs. however you could do this (not sure if this is what you want)


select * 
FROM hsi.ChartQueueTxLog cq, hsi.Chart c
WHERE 
c.FacilityNum in 
(case
When &fecval = 0 then '101,102,103'
else '108'
END)
AND cq.ChtNum = c.ChtNum
AND cq.ExitTime != TO_DATE('1964-01-01','YYYY-MM-DD')
AND cq.AvailableTime != TO_DATE('1964-01-01','YYYY-MM-DD')
AND cq.ExitTime >= cq.AvailableTime

dhani
Posting Yak Master

132 Posts

Posted - 2009-07-16 : 14:19:44

Hello Visakh,

Thanks for your immediate response, while i used your technique it says " invalid number " error because the facilitynum column type is numeric

any ideas please

Best Regards

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-16 : 14:30:11

(
(c.FacilityNum in (101,102,103) AND &fecval = 0)
OR c.FacilityNum = 108
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-16 : 14:47:40

Oops didnt notice that case was in where

dhani
Posting Yak Master

132 Posts

Posted - 2009-07-16 : 16:07:38

Thanks Lamprey,

it is working fine, please help this doubt so that it will get closed the issue,
in extension to this when &fecval=3 then i would like to select ALL values

(
(c.FacilityNum in (101,102,103) AND &fecval = 0)
OR c.FacilityNum = 108 AND &fecval = 1
OR c.FacilityNum = ALL AND &fecval = 3
)

here in the above statement all means (i have in table 101,102,103.......... 134) and also this list may go further if i mention individual numbers then i need to change query everytime that when a new facilitynumber adds to table

so is there any possible way to just get ALL values when the &Fecval = 3

Please Help me

Thanks in advance

Best Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-16 : 16:24:58

Also asked and answered here
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/f818bfd7-c9b0-49a4-adfc-739e5a7683ab

N 56°04'39.26"
E 12°55'05.63"

dhani
Posting Yak Master

132 Posts

Posted - 2009-07-16 : 16:36:23

Thanks To All,

it is worked with below one

WHERE ( (c.FacilityNum in (101,102,103) and &fecval=0)
OR (c.facilitynum = 108 and &fecval = 1) )
OR (c.facilitynum = 109 and &fecval = 2) )
OR ( &fecval = 3 )
)

Thank You Very much,

it is working very fine

i appreciate your help,

Thanks To lamprey, visakh, peso & Thanks to All ,

Best Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources