In my daily tasks related to SQL statement, I often need to swap between to fields in the where clause like the following code:
where pi.parent_program_id = ( case when stp.parent_program_id is not null then stp.parent_program_id else pi.parent_program_id end
The first part of the case is the actual field that you want to match when the case condition is true, the else part should always return true, as shown by the code above if stp.parent_program_id is null then the resulting statement would be pi.parent_program_id = pi.parent_program_id which look very not useful but in this case it will act like a neutral statement because it will always return true, but be careful if there is an “OR” statement that adjacent with the code above. This trick only safe when all condition in the where clause use “AND”.
I found an example on Stackoverflow.
I hope it is useful for anyone that have the same problem as mine.

[...] leave a comment » Please view the post here. [...]