Use a named parameter in SQL multiple times

I am using Birt 2.3.

How can you use a parameter multiple times in a SQL without having to declare it every time?

For Example,

SELECT *
FROM CUSTOMERS
WHERE FIRST_AREA = :P_AREA OR SECOND_AREA = :P_AREA

Thanks

Find more posts tagged with

Comments

mwilliams

Hi rafherllu,

If you place the base SQL query in your dataSet, you can add the where statement with the parameters in the beforeOpen script of the dataSet with script like:

this.queryText = this.queryText + " where firstArea = '" + params["param_name"] + "' or secondArea = '" + params["param_name"] + "'";

Let me know if you have questions.

rafherllu

Thanks Michael.
It´s work fine.
The problem is I'm doing a migration from another tool to Birt and what you suggests is quite laborious.
If there is no other way, I will try this method.
Thank you very much.

Rafa.

mwilliams

Rafa, 
 
Currently, this is the best way to do this. I'm pretty sure that named parameters for the SQL query builder has been requested as an enhancement. You may check the bugs/enhancement requests to see if you see it though. If not, you could request an enhancement for this feature. 
 
<a class='bbc_url' href='http://www.eclipse.org/birt/phoenix/reportabug.php'>BIRT: Reporting Bugs and Requesting Enhancements</a>

bhanley

I believe you can bind a parameter to a query multiple times as well. Construct your query like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT *
FROM CUSTOMERS
WHERE FIRST_AREA = ? OR SECOND_AREA = ?
</pre>
 
Then on the parameters tab of the data set editor you can bind each placeholder to the same report parameter. 
 
This is not much different than the previous recommendation, but it does save you writing a custom script to do the dynamic query building.

rafherllu

Thx Brian, this option is less hard.
But with many parameters can be complicated.
The ? in the SQL should be created and assigned to the parameters in order and be careful.
Anyway, thanks.
I hope that in future, be an enhancement.

Chanda

Hi All,

Instead of defining parameters every time I used beforeOpen sript as below.

this.queryText = this.queryText + " and c.subscriber_id=" + params["subid"].value + " and c.cust_region='EMAT' and c.cust_system_code=t.cust_system_code and t.subscriber_id= "+ params["subid"].value;

It worked first time. But later I opened Dataset window to edit my select query, I edited(I just added distinct for select) after that I clicked on OK then window will be hanged. Please advice what might be the reason.

Or is there any other way to use named parameters instead of defining it every time.

I think after I added distinct clause it started hanging, is distinct is not allowed? Please advice.

mwilliams

Hi Chanda,

Distinct should be allowed. Can you recreate this issue with the sample database and attach the report design for me to run? Thanks.

Chanda

Hi Michael,

It works fine with Sample database I tried now. BefeoreOpen script and distinct clause in query both are working fine. I am not sure why only in my datasource it is happening. if table which is used in the query has more data then "dataset window" will hang? Please advice.

I have attached report with sample data source, it resembles my query.

mwilliams

Chanda,

It looks like you might have forgotten to attach the report.