Using $P!{} to add a parameter filter to select statement

[janlandry]

I have a string parameter called CompanyCode.  If CompanyCode has a value (notEmpty)  I want to add this line to my select statement.

AND BIlling.CompanyCode = ${CompanyCode}

 

I declared another parameter, CompanyCodeSql which has the default expression

$P{CompanyCode}.isEmpty() ? "" : " AND Billing.`Company_Code` = $P{CompanyCode} "

 

This works when CompanyCode is empty but I get a syntax error when CompanyCode has a value.  I guess the $P{CompanyCode} is not getting evaluated because it is in quotes? 

 

Any help appreciated.

[cbarlow3]

I don't think that building your SQL query from string variables is the most straightforward way to accomplish your goal.

Why not just use this in your query instead:

 

AND ($P{CompanyCode} IS NULL OR BIlling.CompanyCode = $P{CompanyCode})

 

Carl

 

[phantastes]

Hi,

Try this. It might look the same as what you had but what you acually need to to is to concatenate the "AND Billing.Company_Code = " with the parameter $P{CompanyCode}.

You had the parameter inside the already existing string which will not yield the value of the parameter but instead the actual text $P{CompanyCode}.

Hope this helps.

 

 

Code:

$P{CompanyCode}.isEmpty() ? "" : "AND Billing.Company_Code = "+$P{CompanyCode}