Test Query on Before Open() event?

Someone told me before that in Oracle, there is a BEFORE TRIGGER event whereby we can do a test query to see if there are records before proceeding to the main query.

In BIRT, I want to try implementing this as a temporary measure. The test query is merely selecting one field and if the field returns no value, then I get 0.

Of course the query would be like SELECT NVL((SELECT A FROM TABLE

,0) FROM DUAL. If the value returns 0, then the actual query will not run.

I actually saw a method called Before Open(). But then there's not much of explanation in the Field Guide book I've read.

Is it actually possible to implement condition query in Before Open? Is there any example that I can base on to test it?

Find more posts tagged with

Comments

bhanley

You would need to implement a secondary data set with your test query. Each data set can contain only one query.

Using the two data set approach you could check the results of the test query and abort the execution of the main (larger) query if you want to.

mmah

Is there any sample script on this?

bhanley

Check out this post. It manages the execution of one dataset based on the results in another.<br />
<br />
<a class='bbc_url' href='http://www.birt-exchange.org/forum/designing-birt-reports/14466-cancel-execution-dataset.html'>http://www.birt-exchange.org/forum/designing-birt-reports/14466-cancel-execution-dataset.html</a><br />
<br />
Good Luck!

mmah

Hi bhanley,

After reading the post mentioned previously, the solution mentioned in here doesn't fit the scenario I'm in.

I've tried putting in but it's not working. Either my script syntax is wrong or otherwise. On one attempt the scriptment works in the Preview mode but in servlet, I get exception.

I'm enclosing the file attachment. There are two datasets, one of them being the test dataset. If the test dataset returns a 0 then the actual one will not run. It's said to put in BeforeFactory method, right?

mmah

bhanley

I spent some time trying to work through this. The sample I pointed you to had a static condition to determine Data Set execution and as such it was easier to check. Your need to evaluate a dynamic value from an external source certainly complicates things.<br />
<br />
I tried setting up an example where I bound a parameter to the total count on a "control" data set. I then added some logic to the beforeOpen script of the main data set:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>controlSet = reportContext.getDesignHandle().findDataSet("ControlSet");

if(controlSet != null){
reportContext.setParameterValue("pRowsFound", controlSet.getRowData().getColumnValue("TOTALPRODUCTS"));

if(params["pMinRows"].value > params["pRowsFound"].value){
chartSet = reportContext.getDesignHandle().findDataSet("Data Set");
chartSet.setQueryText("SELECT 1 FROM CLASSICMODELS.PRODUCTS");
}
else{
reportContext.setPersistentGlobalVariable("VisualizeChart", "true");
}
}</pre> <br />
<br />
It is not quite working yet, but I am short on time so I thought I would give this to you to experiment with. in the code, if the <strong class='bbc'><em class='bbc'>pMinRows </em></strong>parameter is greater that the <strong class='bbc'><em class='bbc'>pRowsFound </em></strong>parameter I re-set the main data set's query to something innocuous so there will be no impact on the DB at all.<br />
<br />
There is also a chart bound to the main data set that I show/hide based on the total row evaluation. That is why the <em class='bbc'>else</em> block sets the Persistent Global Variable.<br />
<br />
It is not all there yet. One problem I am still working through is ensuring the Control data set executes before the main data set. If I get some time i will try and tinker with this some more. If you get it working before that, please update the thread.<br />
<br />
Thanks!

hemant.kr12@gmail.com

<p>Hi Everyone , can any one help me on this?</p>
<p> </p>
<p> select a.* from(select sl.loc,sum(sl.qty) as total_qty,</p>
<div>case when sum(sl.qty) = 0 then 'EmptyLoc' else 'OccupiedLoc' End LocationState, </div>
<div>case when l.locationhandling='3' then 'StandaradPalletLocation' </div>
<div>when l.locationhandling='4' then 'EuroPalletLocation' </div>
<div>when l.locationhandling='10' then 'ShelfLocation' End LocationType</div>
<div>from skuxloc sl, loc l </div>
<div>where sl.loc=l.loc </div>
<div>and l.locationhandling in ('3','4','10')</div>
<div>and sl.loc between ? and ?</div>
<div>and l.locationhandling in ('HHHH')</div>
<div>group by sl.loc, l.locationhandling order by sl.loc) a where a.total_qty 'QQQQ'</div>
<div> </div>
<div>In above query i want to replace 'QQQQ' with =0 or >0 or >=0 using the script below so that i can get the locations having qty=0 or >0 or >=0 , but when i am running this report I am getting error.</div>
<div> </div>
<div>
<div>this.queryText=this.queryText.replace("HHHH",params["LocType"].value.join("','"));</div>
<div> </div>
<div>if(params["LocType"].value=="0"){</div>
<div>this.queryText=this.queryText.replace("'QQQQ'",params["LocType"].value="='0'");</div>
<div>}</div>
<div>else if(params["LocType"].value=="1"){</div>
<div>this.queryText=this.queryText.replace("'QQQQ'",params["LocType"].value=">'0'");</div>
<div>}</div>
<div>else if(params["LocType"].value=="2"){</div>
<div>this.queryText=this.queryText.replace("'QQQQ'",params["LocType"].valu=">='0'");</div>
<div>};</div>
<div> </div>
<div> </div>
<div>If anyone can help me on this please reply.. thanks in advance..:)</div>
<div> </div>
<div>report is attached below for your reference.</div>
</div>
<p> </p>

hemant.kr12@gmail.com

<p>Hi All,</p>
<p> </p>
<p>Sorry for my earlier Post, I got my mistake. Its working fine now.</p>