Create a birt report with if/else condition.

I need to run a query in the database. If the output (list of numbers) of the query (1) is one of these numbers -100,400,500, I need to run one more query (2), and if the output is either 200 or 300, I need to run a different query (3). 
 
The report will display the output of either (2) or (3) because they both have same columns, but different results based on the input.
 
All queries are from same dataset. The user inputs will be put in query (2) or (3).
 
Can someone help me in achieving it?

Find more posts tagged with

Comments

PaulCooper

gaganthapar,
 
Assuming you mean that Query (1) returns only one column and one row (i.e. one number) then you do:
<pre class="_prettyXprint">
select ... /*Query 2*/
from ...
where ...
and (select /*Query 1*/ ...) in (-100, 400, 500)
union all
select ... /*Query 3*/
from ...
where ...
and (select /*Query 1*/ ...) in (200, 300)</pre>
Make sure Query 2 has all the column aliases you require.
 
Regards,
 
Paul

gaganthapar

Query 1 can return multiple rows but one column. And then based on the output, I will run either query 2 or query 3.

PaulCooper

So what do you want it to do if it returns two rows with -100 and 200?

gaganthapar

<blockquote class="ipsBlockquote" data-author="PaulCooper" data-cid="139217" data-time="1443033336">
<div>
So what do you want it to do if it returns two rows with -100 and 200?
</div>
</blockquote>
Thank you. Then for 100, I will run query 2 and for 200, I will run query 3.

PaulCooper

So the number from Query 1 is a parameter in Queries 2 and 3?
Also if query 1 returns 4 numbers it runs four queries, some 2 and some 3?

gaganthapar

<blockquote class="ipsBlockquote" data-author="PaulCooper" data-cid="139219" data-time="1443033712">
<div>
So the number from Query 1 is a parameter in Queries 2 and 3?
</div>
</blockquote>
 
Sorry. This is what I want to do

.
 
Select distinct wbs_num from item_table.
 
wbs_num can be list of values. If the wbs_num is 100 or 400 or 500. I have to run the query 2. 
 
if wbs_num is 200 or 300, I will run query 3.
 
WBS_NUM is not a parameter in query 2 or 3.
 
Can I write the query 1 in "beforeOpen" script and based on the output, I will run query 2 or 3?

PaulCooper

Ok this sounds like what you need:
<pre class="_prettyXprint">
select ... /*Query 2*/
from ...
where ...
and exists (select wbs_num from item_table where wbs_num in (100, 400, 500))
union all
select ... /*Query 3*/
from ...
where ...
and exists (select wbs_num from item_table where wbs_num in (200, 300))</pre>
There is no need to put distinct in query 1 in the EXISTS clause.
 
Regards,
 
Paul

gaganthapar

<blockquote class="ipsBlockquote" data-author="PaulCooper" data-cid="139221" data-time="1443034779">
<div>
Ok this sounds like what you need:
<pre class="_prettyXprint">
select ... /*Query 2*/
from ...
where ...
and exists (select wbs_num from item_table where wbs_num in (100, 400, 500))
union all
select ... /*Query 3*/
from ...
where ...
and exists (select wbs_num from item_table where wbs_num in (200, 300))</pre>
There is no need to put distinct in query 1 in the EXISTS clause.
 
Regards,
 
Paul
</div>
</blockquote>
I think it should solve the problem. Thank you