View previous topic :: View next topic
|
Author |
Message |
useit
Active User
Joined: 05 Oct 2006 Posts: 152
|
|
|
|
Hi,
can we do some kind of case statement within the where clause to validate the null value of the column.
please find the example below.
I have a indicator "y" or "n". based on this indicator I need to select the value (where clause using db2 case).
in the below example if the IND = 'Y' i should select only the columns with TERM_DATE IS NULL. else select all the value.
A.TERM_DATE =
CASE WHEN IND = 'Y'
THEN NULL
ELSE A.TERM_DATE
END
i know we cannot check for = NULL and it has to be IS NULL. but IS NUL inside the case statement doesn't work. is there any other way of handling this scenario?
Regards,
useit |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Just curious, are you attempting something like this? Or maybe I am confused about the 'case' requirement and the '= NULL' issue
Code: |
WHERE (IND = 'Y' AND A.TERM_DATE IS NULL) OR
(IND <> 'Y') |
|
|
Back to top |
|
|
useit
Active User
Joined: 05 Oct 2006 Posts: 152
|
|
|
|
no arun,
i have a Terminte indicator Y or N Which would help me to fetch only active accounts and active and terminated accounts both.
so if the Term-ind = Y. I Should fetch only active accounts. (i, e Terminate_date is null). And if the term-ind = 'N' i should fetch both active and non active accounts(I, e Term_dt is null and term_dt is not Null).
where terminate_dt =
case :host-term-ind = 'y'
then null
else null not nul both
end
something like above.
thanks,
useit |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
I was assuming the 'terminate indicator' to be a table column name based on your initial post. But from your recent post above, looks like the indicator is just an input variable and you are trying to adjust your WHERE clause on TERMINATE_DT to select only NULL if the input indicator is 'Y', and to ignore that filtering if the indicator is 'N' (or does not equal 'Y').
If that is the case, would changing the IND in my previous response to your indicator host variable, help? |
|
Back to top |
|
|
useit
Active User
Joined: 05 Oct 2006 Posts: 152
|
|
|
|
thanks arun,
it worked for me. sorry i did not get this idea.... i was just trying with case statement.
thanks again
Regards,
Prahlad Shetty |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
No worries, You're welcome! |
|
Back to top |
|
|
|