How do I use a variable in an ORDER BY clause?

[chetan_33]

I am using iReport 1.2.5, jasper set to 1.2.0,SQL Server

and have successfully used a parameter as part of the where clause

 

I wanted to conditionally include columns in order by clause (obviously the ones which are selected) but it fails with expressions.

So I tried to simply substitute by a parameter with default values provided by iReport but it gives exception as follows

 

 

java.sql.SQLException:ÂTheÂSELECTÂitemÂidentifiedÂbyÂtheÂORDERÂBYÂnumberÂ5ÂcontainsÂaÂvariableÂasÂpartÂofÂtheÂexpressionÂidentifyingÂaÂcolumnÂposition.ÂVariablesÂareÂonlyÂallowedÂwhenÂorderingÂbyÂanÂexpressionÂreferencingÂaÂcolumnÂname. ÂÂÂÂatÂnet.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365) ÂÂÂÂatÂnet.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781) ÂÂÂÂatÂnet.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224) ÂÂÂÂatÂnet.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)

 

 

I have following in my query

 

.......

ORDER BY

Name1,VKORG,Name1, PHier2,

$P{PARAM_SORT_CLAUSE}

,JAHR, MONAT

 

 

parameter has value of "PHier3 , MATNR

 

There are 3 more parameter substitutions in whereby clause with which report worked fine till this new parameter is introduced.So cant understand what is 5 in exception.

 

Is there any restriction on parameterising in order by clause and other parts of sql?

 

I am trying to add capability to control sorting on a specific column and the sortorder as assc/desc

 

Appreciate inputs

[Teodor Danciu]

Hi,

 

Use the $P!{} syntax.

The /demo/samples/query sample provided with the project shows how to use a parameter in the ORDER BY clause.

 

I hope this helps.

Teodor

[chetan_33]

Thanks that worked.

 

But its surprising that $P{} works in where clause or part before order by and so i never looked in such a detail in sample

 

What is the secret behind '!'. Are there any such usage specifics at other places as well

 

I will now go ahead by replacing it with variable defintion which is more manageable So will it also require $V{}?.