[#12136] - $X function does not support column name formatted by built-in SQL function with comma delimited parameters

Category:
Bug report
Priority:
High
Status:
Feedback Requested
Project: Severity:
Major
Resolution:
Open
Component: Reproducibility:
Always
Assigned to:
0

Jasper $X syntax does not support column name formatted by built-in SQL function with comma delimited parameters

For example:
$X{IN, dayname(from_utc_timestamp(timestamp,$P{Time_Zone})),Days}

It will regard that "$P{Time_Zone}" as the name of parameter(the third token)
And throw an exception

hxue's picture
23
Joined: May 14 2019 - 9:14am
Last seen: 12 hours 38 min ago

4 Comments:

#1
  • Assigned:nobody» shertage
#2

Hi,

the $X syntax does not accept parameters in the column name section.
As a workaround, you could define a String parameter and use it in the query:

<parameter name="Days_text" class="java.lang.String">
<defaultValueExpression><![CDATA[$P{Days}.toString().replace("[","(").replace("]",")")]]></defaultValueExpression>
</parameter>
...
<queryString language="SQL">
<![CDATA[select * from my_table where dayname(from_utc_timestamp(timestamp,$P!{Time_Zone})) IN $P!{Days_text}]]>
</queryString>

Please note the $P! notation used in the query, instead of $P.
Try this workaround first and let us know if it works for you.

Thank you,
Sanda

#3
  • Status:New» Feedback Requested
#4

Thanks for your response, but in this way, I will miss the feature from $X syntax, if $P{Days} is Null, this will not work.

Feedback
randomness