clintv

Hi,

Is it possible to use IN operator in CASE statement? If not, what are the other options i could use in this code:

WHERE CASE @ExceptionStat WHEN 'A' THEN ExceptionStatus IN ('F','O','P','R') WHEN 'P' ExceptionStatus IN ('F','O','P') ELSE ExceptionStatus IN ('O','R') END
AND(A.DatePaid BETWEEN @DatePaidFrom AND @DatePaidTo)
AND A.Currency = @Currency

Thank you so much!

Timbo

Yes, but your CASE construct is wrong:

clintv

Thanks Timbo! Ill check it out il give you feedback later thanks!

clintv

Hi Timbo,

Ive tried what you told me here's my code:

WHERE CASE WHEN @ExceptionStat = 'A' THEN ExceptionStatus IN ('F','O','P','R') WHEN @ExceptionStat = 'P' THEN ExceptionStatus IN ('F','O','P') ELSE ExceptionStatus IN ('O','R') END
AND(A.DatePaid BETWEEN @DatePaidFrom AND @DatePaidTo)
AND A.Currency = @Currency


It doesnt work still

NEOLLE

Hello clintv,

I'm not sure if this is possible but I don't recommend such method to be used in a where clause since you can not optimize the query's performance. This is difficult (if not impossible) to tune.

clintv

ahh i see thanks. any suggestion anyone?

NEOLLE

One way is to use the UNION operator.

clintv

Im afraid i cannot use UNION operator with this one since im accessing one table am i right? Ill get back to you if i found the solution with this one thanks anyway.

NEOLLE

Yes you can use the UNION operator even if you are accessing only one table.

clintv

so i need to have CASE statement whit UNION operator? Can you give me the syntax please i cannot find any in the internet thanks

clintv

I finally got the solution for this heres my code

WHERE ExceptionStatus = CASE WHEN @ExceptionStat = 'A' THEN 'F' END
oR ExceptionStatus = CASE WHEN @ExceptionStat = 'A' THEN 'O' END
OR ExceptionStatus = CASE WHEN @ExceptionStat = 'A' THEN 'P' END
OR ExceptionStatus = CASE WHEN @ExceptionStat = 'A' THEN 'R' END
OR ExceptionStatus = CASE WHEN @ExceptionStat = 'P' THEN 'F' END
OR ExceptionStatus = CASE WHEN @ExceptionStat = 'P' THEN 'O' END
OR ExceptionStatus = CASE WHEN @ExceptionStat = 'P' THEN 'P' END
OR ExceptionStatus = CASE WHEN @ExceptionStat = 'C' THEN 'O' END
OR ExceptionStatus = CASE WHEN @ExceptionStat = 'C' THEN 'R' END
AND(A.DatePaid BETWEEN @DatePaidFrom AND @DatePaidTo)
AND A.Currency = @Currency

Is this process friendly or what? But apparently i still have problem how do i
compare NULL values in CASE expression? This code has syntax error

WHERE ExceptionStatus = CASE WHEN @ExceptionStat = 'A' THEN 'F' END
oR ExceptionStatus = CASE WHEN @ExceptionStat = 'A' THEN 'O' END
OR ExceptionStatus = CASE WHEN @ExceptionStat = 'A' THEN 'P' END
OR ExceptionStatus = CASE WHEN @ExceptionStat = 'A' THEN 'R' END
OR ExceptionStatus = CASE WHEN @ExceptionStat = 'P' THEN 'F' END
OR ExceptionStatus = CASE WHEN @ExceptionStat = 'P' THEN 'O' END
OR ExceptionStatus = CASE WHEN @ExceptionStat = 'P' THEN 'P' END
OR ExceptionStatus = CASE WHEN @ExceptionStat = 'C' THEN 'O' END
OR ExceptionStatus = CASE WHEN @ExceptionStat = 'C' THEN 'R' END
OR ExceptionStatus = CASE WHEN @ExceptionStat = 'C' THEN NULL END
AND(A.DatePaid BETWEEN @DatePaidFrom AND @DatePaidTo)
AND A.Currency = @Currency


How do i do this please help Thanks!!!!

NEOLLE

Hello clintv,

I had second thoughts on the UNION operator. I thought I understood what you need.

Have you tried Dynamic SQL? I think this can address your situation - this technique allows you to dynamically construct DML statements based on your criteria.

Im assuming that you are using MS SQL.

clintv

ok ill study this tomorrow, im 2 days late of my deadline just because of this I have to work on weekends Anyway, have faith

clintv

Unfortunately the info you sent cant help me Im handling large table i cannot use Example 3--Null columns script

Suggestions anyone?

clintv

Hi ive finally come to answer with this query here ... il post it for the benfit of everyone

BEFORE:

WHERE CASE @ExceptionStat WHEN 'A' THEN ExceptionStatus IN ('F','O','P','R') WHEN 'P' ExceptionStatus IN ('F','O','P') ELSE ExceptionStatus IN ('O','R') END
AND(A.DatePaid BETWEEN @DatePaidFrom AND @DatePaidTo)
AND A.Currency = @Currency

AFTER:


WHERE ((@ExceptionStat = 'A' AND ExceptionStatus IN ('F','O','P','R')) OR (@ExceptionStat = 'P' AND ExceptionStatus IN ('F','O','P')) OR (@ExceptionStat = 'O' AND ExceptionStatus IN ('O','R')))
AND(A.DatePaid BETWEEN @DatePaidFrom AND @DatePaidTo)
AND A.Currency = @Currency


total transformation