Query with conditional on parameters

[QWERTY1699955868]

Hi,

Im having some trouble trying to do checks against optional parameters in a basic query..

 

Code:

Post edited by: QWERTY?, at: 2008/01/08 18:53

[kchaudhry]

Try the following query:

Code:

SELECT * FROM Trace WHERE (MODULE = $P{MODULE} or $P{MODULE} is null or $P{MODULE} = '')

[katepassaro]

 I just *days* searching for this - thank you so much! 

[cbarlow3]

I almost never check explicitly for NULL in my SQL queries anymore...I usually find COALESCE to be easier to read and more flexible.  In this case, I would do something like this:

SELECT * FROM   Trace WHERE   MODULE=COALESCE($P{MODULE},MODULE)That's assuming that not selecting a module input parameter value does in fact end up with it being set to null, not blank.  If there's a chance that the parameter will be BLANK and you want that condition to also be interpreted as a "match everything" instruction, then you would have to add OR $P{MODULE}=''CarlP.S.: COALESCE is also really helpful when you are optionally specifying a StartDate and EndDate.  Your solution depends on the range of date values you support, but I find myself using clauses like this pretty often:WHERE  TRANSACTION.POST_DATE>=COALESCE{$P{StartDate},'1800-01-01') AND  TRANSACTION.POST_DATE<=COALESCE($P{EndDate},'2999-12-31')Yes...my clause has a Y3K bug in it.  People will curse my name some day.Carl