| How to make the where clause optional on dataset SQL query?? [message #683125] |
Sun, 12 June 2011 23:45  |
Eclipse User |
|
|
|
Hi,
I am working on a report design which has 3 report parameters; all three report parameters are optional. Now my question is, when no report parameters are selected full report should be generated, how that can be achieved (i.e., the where clause on the dataset sQL query should be set as optional)?
I have removed the where clause from the Data Set SQL query, and I think I need to filter the data, but so far not successful. Is there any example that someone can point me to? Any input on this is appreciated.
Thanks in advance,
Jyo
|
|
|
|
| (no subject) [message #683231 is a reply to message #683125] |
Mon, 13 June 2011 05:29  |
| Eclipse User |
|
|
|
Originally posted by: Donkey Hottie
13.6.2011 6:45, jyo kirjoitti:
> Hi,
>
> I am working on a report design which has 3 report parameters; all three
> report parameters are optional. Now my question is, when no report
> parameters are selected full report should be generated, how that can be
> achieved (i.e., the where clause on the dataset sQL query should be set
> as optional)?
>
> I have removed the where clause from the Data Set SQL query, and I think
> I need to filter the data, but so far not successful. Is there any
> example that someone can point me to? Any input on this is appreciated.
> Thanks in advance,
>
> Jyo
You can do something like this
SELECT A,B,C
FROM ABBA
WHERE A = IFNULL(param1,A)
AND B = IFNULL(param2,B)
AND C = IFNULL(param3,C) ;
If Oracle, change IFNULL() to NVL().
--
You will have a long and boring life.
|
|
|
| Re: How to make the where clause optional on dataset SQL query?? [message #683326 is a reply to message #683149] |
Mon, 13 June 2011 10:21 |
| Eclipse User |
|
|
|
Thank you for the reply.
Initially I had query like below:
select A, B, C from table where A=? or B=? or C=?
And I defined 3 optional report parameters rp_A, rp_B, rp_C that can enter on the parameter Page, and I linked the dataset paramters to report parameters and report rendering fine if user selects/enters values for rp_A, rp_B and rp_C. Now my question is, if user doen't select/enter any value for the report parameter; all the data from the table should be dsiplayed on the report;
I tried below two options Thomas and Donkey Hottie provided; but still not working for me, maybe they should be at specific places during report design that I am missing, can yu point me some detail example?
|
|
|
| (no subject) [message #683427 is a reply to message #683326] |
Mon, 13 June 2011 13:21 |
| Eclipse User |
|
|
|
In this case I would not use dataset parameters. I would put my query
in like select A, B, C from table
I would then write a beforeOpen script on the dataset that checked the
values of A, B and C and modify the query based on those values.
if( params["A"].value != null ){
this.queryText = this.queryText + " where A="+params["A"].value;
}
Jason
On 6/13/2011 10:21 AM, jyo wrote:
> Thank you for the reply.
>
> Initially I had query like below:
> select A, B, C from table where A=? or B=? or C=?
>
> And I defined 3 optional report parameters rp_A, rp_B, rp_C that can
> enter on the parameter Page, and I linked the dataset paramters to
> report parameters and report rendering fine if user selects/enters
> values for rp_A, rp_B and rp_C. Now my question is, if user doen't
> select/enter any value for the report parameter; all the data from the
> table should be dsiplayed on the report;
> I tried below two options Thomas and Donkey Hottie provided; but still
> not working for me, maybe they should be at specific places during
> report design that I am missing, can yu point me some detail example?
>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: (no subject) [message #691115 is a reply to message #691100] |
Thu, 30 June 2011 14:04 |
| Eclipse User |
|
|
|
What data type is rp_tran_au?
Also do you check it for null first and then check the value?
Jason
On 6/30/2011 1:47 PM, jyo wrote:
> Jason Weathersby wrote on Fri, 24 June 2011 17:45
>> What is the parameter type? Did you try removing each condition
>> individually?
>> if((params["rp_tran_au"].value == null |){}
>> then
>> if((params["rp_tran_au"].value == '')){}
>> To see which condition is the issue.
>>
>> Jason
>
>
> Hi Jason, After debug I came to know that below condition causing the
> RHINO USAGE WARNING....And can you correct the syntax I am using?
>
> if((params["rp_tran_au"].value == '')){}
>
> Thx!
|
|
|
Home