conditional where clause

Hello, I was wondering if you change the query depending on the the value of the report parameters.
For instance if a report takes three parameters if 2 of them are empty can I change the query so it no longer uses the those 2 parameters in the query it just uses the one the user put a value in for

Find more posts tagged with

Comments

mwilliams

Hi starfusion,

You can set the query in your script of your dataSet. In the beforeOpen method you can use the following script:

this.queryText = "select blah from blah where blah = blah";

You could write an if/else statement that surrounds different options for your query.

Hope this helps.

bhanley

If you are going to use the path Michael suggests (which is perfect for what you are trying to do), do not formally bind any parameters to your data set. If you use Question Marks (?) in your query to handle dynamic parameter binding, the parameter count will always need to match up. So if you want to dynamically modify your query using parameters, leave your query on the data set vague, and hone it in the beforeOpen script using conditional logic.

Good Luck!

UtahJerry

You can also leave the dataset query really simple (i.e. 'Select * from xyz')
and conditionally define individual components of the script similar to the following:

// Append Where Clause for Agency
if (params["rprmAgency"].value != "All Agencies"){
this.queryText = this.queryText + " where ROOT.LEASE.CURR_STATUS !='99-CLOSED'AND ROOT.LEASE.COMPANY_ID = '" + params["rprmAgency"]+"'"
}else{
this.queryText = this.queryText + " where ROOT.LEASE.CURR_STATUS !='99-CLOSED'"
}
// Append Date From
this.queryText = this.queryText + " and ROOT.LEASE.START_DATE >= TO_DATE('" + params["rprmDateBegin"].value + "','mm/dd/yyyy')"
// Append Date From
this.queryText = this.queryText + " and ROOT.LEASE.END_DATE <= TO_DATE('" + params["rprmDateEnd"].value + "','mm/dd/yyyy')"
// Append Where Clause for Certifier
if (params["rprmCertifier"].value != "All Certifiers"){
this.queryText = this.queryText + " and ROOT.LEASE.APPROVER = '" + params["rprmCertifier"]+"'"
}
// Append Where Clause for Lease Type
if (params["rprmLeaseType"].value != "All Lease Types"){
this.queryText = this.queryText + " and ROOT.LEASE.LEASE_TYPE = '" + params["rprmLeaseType"]+"'"
}
// Append Order By Clause
this.queryText = this.queryText + "order by ROOT.LEASE.COMPANY_ID,ROOT.LEASE.DEPT_ID,ROOT.LEASE.LEASE_NO"
if (dataSetRow["PO_SHIP_TO_WITH_DESC"] != null) {
dataSetRow["PO_SHIP_TO_WITH_DESC"]
} else {
dataSetRow["PO_REQUESTOR_WITH_DESC"];
}

rpolunsky

Alternately, depending on the data type of your parameter, you can add the parameter reference twice and write your SQL query as:

where (? = '' or stringfield = ?)
or possibly
where (nvl(?,'-') = '-' or stringfield = ?)

I'm using Oracle which supports the NVL() call. I haven't tested to see what you get when you leave the parameter textbox empty, and required/optional settings on the parameter might also be an issue.