Using a parameter in SQL Query

Jateel

Hi there<br />
<br />
Im using Freemarker (and fmpp) to generate a BIRT Report. TThe Birt report query makes use of a parameter called run_id to determine what data is used and displayed in a table of the report.<br />
<br />
The below query needs to be used:<br />
<br />
(<br />
select<br />
1 as mismatch,<br />
stagingbalance.bal_period as BAL_PERIOD,<br />
stagingbalance.netting_legal_entity as NETTING_LEGAL_ENTITY,<br />
sum(stagingbalance.bal_amount_ytd_close) as SUM,<br />
abs(sum(stagingbalance.bal_amount_ytd_close)),<br />
stagingbalance.bal_net as BAL_NET,<br />
count(*)<br />
from stagingtable<br />
left join stagingbalance on stagingtable.karta_staging_table_id = stagingbalance.karta_staging_table_id<br />
left join faslineresult_stagingbalance on stagingbalance.id = faslineresult_stagingbalance.stagingbalances_id<br />
left join faslineresult on faslineresult_stagingbalance.faslineresults_id = faslineresult.id<br />
left join faslinetemplate on faslineresult.faslinetemplate_id = faslinetemplate.id<br />
where stagingtable.faspackrun_id = <span style='color: #FF0000'><strong class='bbc'>run_id</strong></span><br />
and stagingbalance.account_code in ('503300')<br />
group by<br />
stagingbalance.bal_period,<br />
stagingbalance.netting_legal_entity,<br />
stagingbalance.bal_net<br />
having ( (stagingbalance.bal_net in ('EXPENSE') and sum(stagingbalance.bal_amount_ytd_close) < 0)<br />
or (stagingbalance.bal_net in ('INCOME') and sum(stagingbalance.bal_amount_ytd_close) >= 0) )<br />
)<br />
UNION<br />
(<br />
select<br />
0 as mismatch,<br />
stagingbalance.bal_period,<br />
stagingbalance.netting_legal_entity,<br />
sum(stagingbalance.bal_amount_ytd_close),<br />
abs(sum(stagingbalance.bal_amount_ytd_close)),<br />
stagingbalance.bal_net,<br />
count(*)<br />
from stagingtable<br />
left join stagingbalance on stagingtable.karta_staging_table_id = stagingbalance.karta_staging_table_id<br />
left join faslineresult_stagingbalance on stagingbalance.id = faslineresult_stagingbalance.stagingbalances_id<br />
left join faslineresult on faslineresult_stagingbalance.faslineresults_id = faslineresult.id<br />
left join faslinetemplate on faslineresult.faslinetemplate_id = faslinetemplate.id<br />
where stagingtable.faspackrun_id = <span style='color: #FF0000'><strong class='bbc'>run_id</strong></span><br />
and stagingbalance.account_code in ('503300')<br />
group by<br />
stagingbalance.bal_period,<br />
stagingbalance.netting_legal_entity,<br />
stagingbalance.bal_net<br />
having ( not (stagingbalance.bal_net in ('EXPENSE') and sum(stagingbalance.bal_amount_ytd_close) < 0)<br />
and not (stagingbalance.bal_net in ('INCOME') and sum(stagingbalance.bal_amount_ytd_close) >= 0) )<br />
)<br />
<br />
Note:<br />
The parameter is called run_id and is used in the query twice. I dont have a problem when a query only uses the parameter once (in such cases i replace the "run_id" with "?" and add the parameter do the dataset, this works). <br />
<br />
Im do not know how to use the same parameter twice in one query because doing the question mark thing, results in an error (something about the value of param2 being not defined)<br />
<br />
Im quite new to BIRT and Freemarker (and teh working world). I would very much appreciate it if you could help me.<br />
<br />
Thanks in advance<br />
JJ

XFabien

Hello Jateel,

Did u verifiy the type of the parameter is the same? or if use a Report parameter, u need is just select the same parameter. U should try more i think, it's not so hard.

Good day.


Yao