where case condition

Question

(case when @InAct = 1 then (pck.packageID = (select pa.packageID  from PakacgeTb pa where (pck.packageID = pa.packageID)

 and 

((cast(pck.expirydate as date) > cast(getdate() as date)) and cast(pck.BalanceVisit as decimal) > cast(0.00 as decimal)))))

i am trying to use the where case condition i don't know how to use it. This whole code is used after a where condition.

You didn't END your CASE statement. You also really didn't begin it properly. — Jacob H, May 16 '17 at 13:35
Please explain what you are trying to do. This case expression can't work. `then x=y` is not how `case` works. `CASE` returns an atomic value. — HoneyBadger, May 16 '17 at 13:37
You need to give us more information on what you are trying to do. Your entire statement and your desired result would help. From your code, all we can tell is that you made obvious errors, but we can't help you solve the problem if we don't know what the actual problem is. — Jacob H, May 16 '17 at 13:37
[This](http://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) answer may be helpful, whatever the question is. — HABO, May 16 '17 at 13:46

Rahul · Answer 1 · 2017-05-16T14:07:36.937

0

You can convert your CASE expression with a compound condition in WHERE clause like

where (@InAct = 1 or @InAct is null)
and pck.packageID in (select pa.packageID  
                       from PakacgeTb pa)
and 
((cast(pck.expirydate as date) > cast(getdate() as date)) 
and cast(pck.BalanceVisit as decimal) > cast(0.00 as decimal))))

edited May 16 '17 at 14:07

answered May 16 '17 at 13:37

Rahul

76,197
13
71
125

i like the method but how do i check on condition say @InAct = 1 if it is null then .. Thats why i was using case condition .. ? – rinki May 16 '17 at 13:39
@rinki, use `coalesce()` function ... see edit in answer if that helps. – Rahul May 16 '17 at 13:40
lets say i want to avoid this code in the condition using @InAct as null and using some other OR,AND condition – rinki May 16 '17 at 13:48
Example IF condition InAct = 1 else InAct is null because i want to show my table with all values and i want this method to filter out my results using the InAct = 1 else InAct is null. – rinki May 16 '17 at 13:55
@rinki, you should clarify this at first and not in comment but edit your post and include them. anyways you can use a `or` condition. see edit again – Rahul May 16 '17 at 14:08

Azhar · Answer 2 · 2017-05-16T14:12:25.607

0

A simple structure of Case

select 
CASE when ISNULL(pa.[packageID],1) = 1 then 'do something' 
else 'do something' 
end   
from PakacgeTb pa

You have to use then for the when condition. see below.

and (CASE when coalesce(@InAct,1) = 1 and pck.packageID in (select 
pa.packageID from PakacgeTb pa) and ((cast(pck.expirydate as date) > 
cast(getdate() as date)) and cast(pck.BalanceVisit as decimal) > cast(0.00 
as decimal))

then 'do something'

else 'this will only execute if when condition does not satisfy'

end

edited May 16 '17 at 14:12

answered May 16 '17 at 13:55

Azhar

107
12

and (CASE when coalesce(@InAct,1) = 1 and pck.packageID in (select pa.packageID from PakacgeTb pa) and ((cast(pck.expirydate as date) > cast(getdate() as date)) and cast(pck.BalanceVisit as decimal) > cast(0.00 as decimal)) else @InAct is null end) ... like this type i want – rinki May 16 '17 at 13:59
here you have not used **then** . – Azhar May 16 '17 at 14:08
`and (CASE when coalesce(@InAct,1) = 1 and pck.packageID in (select pa.packageID from PakacgeTb pa) and ((cast(pck.expirydate as date) > cast(getdate() as date)) and cast(pck.BalanceVisit as decimal) > cast(0.00 as decimal)) then 'do something' else 'this will execute when first when the condition does not satisfy' end` – Azhar May 16 '17 at 14:09
@Azhar, maybe it's clearer if you use `return something` rather than `do something` in your examples. Case doesn't do anything, it returns a value. `do something` sounds too much like an `if` statement in procedural languages. – HoneyBadger May 16 '17 at 14:16
Correct @HoneyBadger here rinki can return any column value as per his requirements. – Azhar May 16 '17 at 14:21

where case condition

2 Answers2