Ternary operator for order by clause in main query

Question

I am working on struts 2 web application in which
I have a JasperReports report (jrxml) in which i have main query

<parameter name="getOrder" class="java.lang.String">
    <defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>
<queryString>
    <![CDATA[select USER_ID, USER_NAME,CURRENT_ADDRESS,CURRENT_TOWN,CURRENT_POSTAL_CODE,CURRENT_COUNTRY,USER_ADDRESS group by USER_ID, USER_NAME,CURRENT_ADDRESS,CURRENT_TOWN,CURRENT_POSTAL_CODE,CURRENT_COUNTRY,USER_ADDRESS order by $P!{getOrder}, USER_ID]]>
</queryString>

In JSP, i have screen which has <select> option "Order by" which has 5 option like this

<select name="FormBean.orderByField" id="orderByField">
                                <option value='1'
                                    <s:if test="FormBean.orderByField == 1">selected="selected"</s:if>>
                                    <fmt:message key="name" />
                                </option>
                                <option value='2'
                                    <s:if test="FormBean.orderByField == 2">selected="selected"</s:if>>
                                    <fmt:message key="current address" />
                                </option>
                                <option value='3'
                                    <s:if test="FormBean.orderByField == 3">selected="selected"</s:if>>
                                    <fmt:message key="user address" />
                                </option>
                                <option value='4'
                                    <s:if test="FormBean.orderByField == 4">selected="selected"</s:if>>
                                    <fmt:message key="postal code" />
                                </option>
                                <option value='5'
                                    <s:if test="FormBean.orderByField == 5">selected="selected"</s:if>>
                                    <fmt:message key="country" />
                                </option>

 </select>

I need to get pdf format ordered by the option selected in the select option. So i have done is: I created a HashMap i which i gets me the value that i select in the select option

jasperParameter.put("getOrder", pdfBean.getManageOrder());

Here pdfBean.getManageOrder() will give me String value from the <select> tag which i will pass in report

Now depending of the value selected in "getOrder" i need to replace the data in the above query String of report ORDERED by the appropriate data

sql Order by clause depending on the values selected like this

$P{getOrder}.equals("1")?"USER_NAME":$P{getOrder}.equals("2")?"CURRENT_ADDRESS":$P{getOrder}.equals("3")?"CURRENT_TOWN":$P{getOrder}.equals("4")?"CURRENT_POSTAL_CODE":$P{getOrder}.equals("5")?"COUNTRY":""

if value 3 selected the query should liook like this

  <queryString>
    <![CDATA[select USER_ID, USER_NAME,CURRENT_ADDRESS,CURRENT_TOWN,CURRENT_POSTAL_CODE,CURRENT_COUNTRY,USER_ADDRESS group by USER_ID, USER_NAME,CURRENT_ADDRESS,CURRENT_TOWN,CURRENT_POSTAL_CODE,CURRENT_COUNTRY,USER_ADDRESS order by **CURRENT_TOWN**, USER_ID]]>
    </queryString>

I want it done in JR report (jrxml) itself the pdf report to be ordered acccording to the value selected in the screen's select box

How can i use ternary operator in order by clause in main query ? — techGaurdian, Dec 31 '13 at 10:04
Yes. You can use parameter, for example: you can use ternary operator in parameters expression. The sample parameter with query is [here](http://stackoverflow.com/a/11877972/876298) — Alex K, Dec 31 '13 at 10:16
yes.its useful.but in my case i don't want to substitute the value's data (hardcoded) directly but i want to use conditional statement i.e(Ternary operator) in jasperReport's report only based on the value (1,2,3, 4, 5)selected on screen and then use it in Order By clause using $P!{paramName} syntax — techGaurdian, Dec 31 '13 at 10:40
some what like this: $P{getOrder}.equals("1")?"USER_NAME":$P{getOrder}.equals("2")?"CURRENT_ADDRESS":$P{getOrder}.equals("3")?"CURRENT_TOWN":$P{getOrder}.equals("4")?"CURRENT_POSTAL_CODE":$P{getOrder}.equals("5")?"COUNTRY":"" for order by clause in jasper report — techGaurdian, Dec 31 '13 at 10:44

score 0 · Answer 1 · edited Dec 31 '13 at 10:23

0

Try this using case instead of Ternary operator:

case $P{getOrder}
WHEN 1 THEN 'USER_NAME' 
WHEN 2 THEN 'CURRENT_ADDRESS' 
WHEN 3 THEN 'CURRENT_TOWN' 
ELSE ''
END

edited Dec 31 '13 at 10:23

Lakshmi

2,204
3
29
49

answered Dec 31 '13 at 10:08

Radhamani Muthusamy

328
4
13

Did you try to use case in *JasperReports*? – Alex K Dec 31 '13 at 10:14
@Alex, I gave this option for SQL statement but its been asked for JasperReports. Thanks for reminding. – Radhamani Muthusamy Dec 31 '13 at 10:29

Ternary operator for order by clause in main query

1 Answers1