Using cases on PS Query

Question

I want a logic in my query where when prompt 2 (:2) is provided a value or is not blank, the STRM field will automatically be equal to the FIRST_TERM_VALID field, otherwise if :2 is black then the STRM field equals any.

This is the part of the code:

AND A.STRM LIKE ( 
CASE 
WHEN  :2 <> ' ' 
THEN G.FIRST_TERM_VALID 
ELSE '%' 
END
)

The problem is with this part of my query, I can no longer pull any results.

Answer 1

Create an expression on PS Query and use it like a criteria

AND A.STRM = CASE WHEN :2 IS NULL THEN A.STRM ELSE G.FIRST_TERM_VALID END

Answer 2

If prompt :2 is empty it's more likely to be null. This could be what you're searching for:

and (:2 is null or a.strm = g.first_term_valid)

If you want to test for blank too, try this:

and (:2 is null or :2 = ' ' or a.strm = g.first_term_valid)

Feel free to add expressions. The pattern should be visible.

Answer 3

Your logic can be a lot simpler:

Either :2 is null or check if A.STRM = G.FIRST_TERM_VALID

AND (:2 IS NULL OR A.STRM = G.FIRST_TERM_VALID)