Hi,

If I understand your question correctly then you can do somthing like this

AND K.CPLWST_0 = CASE WHEN %3% = '' THEN K.CPLWST_0 ELSE %3% END

Worked beautifully thank you.

Any chance you would know how to do the same thing with a date range (start and end date) - with the between %1% and %2% clause above? If not no worries.

You can do the same idea

WHERE H.TRKLASTC_0 BETWEEN CASE WHEN %1% = '' THEN H.TRKLASTC_0 ELSE %1% END AND CASE WHEN %2% = '' THEN H.TRKLASTC_0 ELSE %2% END

or you can do something like this

WHERE H.TRKLASTC_0 BETWEEN CASE WHEN %1% = '' THEN '01/01/1900' ELSE %1% END AND CASE WHEN %2% = '' THEN '12/31/2099' ELSE %2% END

I did not test before I sent it, I see that it is defaulting the blank date, so use this



WHERE H.TRKLASTC_0 BETWEEN CASE WHEN %1% = '01/01/00' THEN H.TRKLASTC_0 ELSE %1% END AND CASE WHEN %2% = '01/01/00' THEN H.TRKLASTC_0 ELSE %2% END

or this

WHERE H.TRKLASTC_0 BETWEEN CASE WHEN %1% = '01/01/00' THEN '01/01/1900' ELSE %1% END AND CASE WHEN %2% = '01/01/00' THEN '12/31/2099' ELSE %2% END

Awesome. I did it another way before which didn't work, but I'm glad you were able to help me out. Big thank you Israel.

I have one more question for you Isreal, if you are up for the challenge. On a separate report, I have a column with a case statement:

(CASE WHEN H.CUR_0 <> P.CUR_0 THEN 'YES' ELSE NULL END)

I want to be able to optionally filter this column so that if I say 'yes' as a parameter it will only show those 'YES' lines, and if I don't put anything, it will display ALL. I tried:

(CASE WHEN H.CUR_0 <> P.CUR_0 THEN 'YES' ELSE NULL END) = %3% and


(CASE WHEN H.CUR_0 <> P.CUR_0 THEN 'YES' ELSE %3% END)

But neither work.

Thanks again!

CASE WHEN %3% <> '' AND H.CUR_0 <> P.CUR_0 THEN 'YES'
WHEN %3% <> '' AND H.CUR_0 = P.CUR_0 THEN 'NO'
ELSE %3% END = %3%

Thank you once again good sir. You are an SQL wizard.

Hi Israel,

Sorry to bother you again, but I have yet another question. I'm trying to do as you've shown me for search criteria, but I seem to be running into a small issue. I have 2 fields that are joined. I have the following query:

SELECT F.ITMREF_0, I.ITMDES1_0, F.STOFCY_0, S.BPSNUM_0, B.BPSNAM_0, (M.PHYSTO_0), M.ORDSTO_0, M.SALSTO_0, (M.PHYSTO_0+M.ORDSTO_0-M.SALSTO_0), F.REOTSD_0, F.MAXSTO_0, F.REOMINQTY_0, (CASE WHEN F.MAXSTO_0 > 0 THEN F.MAXSTO_0 - ((M.PHYSTO_0)+M.ORDSTO_0-M.SALSTO_0) WHEN F.MAXSTO_0 = 0 AND F.REOMINQTY_0 > 0 THEN F.REOTSD_0 - ((M.PHYSTO_0)+M.ORDSTO_0-M.SALSTO_0) + F.REOMINQTY_0 WHEN F.MAXSTO_0 = 0 AND F.REOMINQTY_0 = 0 THEN F.REOTSD_0 - ((M.PHYSTO_0)+M.ORDSTO_0-M.SALSTO_0) ELSE 0 END), I.STU_0, F.OFS_0

FROM x3v6.CICPROD.ITMMVT M

LEFT OUTER JOIN ITMFACILIT F ON M.ITMREF_0 = F.ITMREF_0 AND M.STOFCY_0 = F.STOFCY_0

LEFT OUTER JOIN ITMMASTER I ON F.ITMREF_0 = I.ITMREF_0

LEFT OUTER JOIN ITMBPS S ON I.ITMREF_0 = S.ITMREF_0

LEFT OUTER JOIN BPSUPPLIER B ON S.BPSNUM_0 = B.BPSNUM_0

WHERE ((M.PHYSTO_0)+M.ORDSTO_0-M.SALSTO_0) - F.REOTSD_0 < 0 AND F.REOMGTCOD_0 = 4 AND I.ITMSTA_0 = 1 AND F.ITMREF_0 = CASE WHEN %1% = '' THEN F.ITMREF_0 ELSE %1% END AND F.STOFCY_0 = CASE WHEN %2% = '' THEN F.STOFCY_0 ELSE %2% END AND S.BPSNUM_0 = CASE WHEN %3% = '' THEN S.BPSNUM_0 ELSE NULL END

ORDER BY F.ITMREF_0 ASC, F.STOFCY_0 ASC

The supplier field is hiding any products that don't have a supplier listed (it won't display null values for that column). Any idea how to solve this one? Here's an idea of the situation (with a regular query). With the current query above, these 2 lines won't show.

Thanks!

Hi Israel,

Sorry to bother you again, but I have yet another question. I'm trying to do as you've shown me for search criteria, but I seem to be running into a small issue. I have 2 fields that are joined. I have the following query:

SELECT F.ITMREF_0, I.ITMDES1_0, F.STOFCY_0, S.BPSNUM_0, B.BPSNAM_0, (M.PHYSTO_0), M.ORDSTO_0, M.SALSTO_0, (M.PHYSTO_0+M.ORDSTO_0-M.SALSTO_0), F.REOTSD_0, F.MAXSTO_0, F.REOMINQTY_0, (CASE WHEN F.MAXSTO_0 > 0 THEN F.MAXSTO_0 - ((M.PHYSTO_0)+M.ORDSTO_0-M.SALSTO_0) WHEN F.MAXSTO_0 = 0 AND F.REOMINQTY_0 > 0 THEN F.REOTSD_0 - ((M.PHYSTO_0)+M.ORDSTO_0-M.SALSTO_0) + F.REOMINQTY_0 WHEN F.MAXSTO_0 = 0 AND F.REOMINQTY_0 = 0 THEN F.REOTSD_0 - ((M.PHYSTO_0)+M.ORDSTO_0-M.SALSTO_0) ELSE 0 END), I.STU_0, F.OFS_0

FROM x3v6.CICPROD.ITMMVT M

LEFT OUTER JOIN ITMFACILIT F ON M.ITMREF_0 = F.ITMREF_0 AND M.STOFCY_0 = F.STOFCY_0

LEFT OUTER JOIN ITMMASTER I ON F.ITMREF_0 = I.ITMREF_0

LEFT OUTER JOIN ITMBPS S ON I.ITMREF_0 = S.ITMREF_0

LEFT OUTER JOIN BPSUPPLIER B ON S.BPSNUM_0 = B.BPSNUM_0

WHERE ((M.PHYSTO_0)+M.ORDSTO_0-M.SALSTO_0) - F.REOTSD_0 < 0 AND F.REOMGTCOD_0 = 4 AND I.ITMSTA_0 = 1 AND F.ITMREF_0 = CASE WHEN %1% = '' THEN F.ITMREF_0 ELSE %1% END AND F.STOFCY_0 = CASE WHEN %2% = '' THEN F.STOFCY_0 ELSE %2% END AND S.BPSNUM_0 = CASE WHEN %3% = '' THEN S.BPSNUM_0 ELSE NULL END

ORDER BY F.ITMREF_0 ASC, F.STOFCY_0 ASC

The supplier field is hiding any products that don't have a supplier listed (it won't display null values for that column). Any idea how to solve this one? Here's an idea of the situation (with a regular query). With the current query above, these 2 lines won't show.

Thanks!

Never mind, I figured it out using an 'or':

SELECT F.ITMREF_0, I.ITMDES1_0, F.STOFCY_0, S.BPSNUM_0, B.BPSNAM_0, (M.PHYSTO_0), M.ORDSTO_0, M.SALSTO_0, (M.PHYSTO_0+M.ORDSTO_0-M.SALSTO_0), F.REOTSD_0, F.MAXSTO_0, F.REOMINQTY_0, (CASE WHEN F.MAXSTO_0 > 0 THEN F.MAXSTO_0 - ((M.PHYSTO_0)+M.ORDSTO_0-M.SALSTO_0) WHEN F.MAXSTO_0 = 0 AND F.REOMINQTY_0 > 0 THEN F.REOTSD_0 - ((M.PHYSTO_0)+M.ORDSTO_0-M.SALSTO_0) + F.REOMINQTY_0 WHEN F.MAXSTO_0 = 0 AND F.REOMINQTY_0 = 0 THEN F.REOTSD_0 - ((M.PHYSTO_0)+M.ORDSTO_0-M.SALSTO_0) ELSE 0 END), I.STU_0, F.OFS_0

FROM x3v6.CICPROD.ITMMVT M

LEFT OUTER JOIN x3v6.CICPROD.ITMFACILIT F ON M.ITMREF_0 = F.ITMREF_0 AND M.STOFCY_0 = F.STOFCY_0

LEFT OUTER JOIN x3v6.CICPROD.ITMMASTER I ON F.ITMREF_0 = I.ITMREF_0

LEFT OUTER JOIN x3v6.CICPROD.ITMBPS S ON I.ITMREF_0 = S.ITMREF_0

LEFT OUTER JOIN x3v6.CICPROD.BPSUPPLIER B ON S.BPSNUM_0 = B.BPSNUM_0

WHERE ((M.PHYSTO_0)+M.ORDSTO_0-M.SALSTO_0) - F.REOTSD_0 < 0 AND F.REOMGTCOD_0 = 4 AND I.ITMSTA_0 = 1 AND F.ITMREF_0 = CASE WHEN %1% = '' THEN F.ITMREF_0 ELSE %1% END AND F.STOFCY_0 = CASE WHEN %2% = '' THEN F.STOFCY_0 ELSE %2% END AND F.STOFCY_0 <> 'VAN' AND (%3% = CASE WHEN S.BPSNUM_0 <> '' THEN S.BPSNUM_0 ELSE '' END OR %3% = '')

ORDER BY F.ITMREF_0 ASC, F.STOFCY_0 ASC

Thanks again Israel.