Jump to content
Changes to the Jaspersoft community edition download ×

Query with conditional on parameters


Recommended Posts

  • Replies 3
  • Created
  • Last Reply

Top Posters In This Topic

  • 3 years later...

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...