Column header sorting- ProblemofVariables in query

[chetan_33]

I have a report where I want column sorting and so query defined in JRXML with ORDER BY section has a variable whcih acts on input paramters

 

So wanted to know

1>> if this approach is fine or not.

2>> Me workign in iReport but it always moves variable tag after queryTag so is this causing problem for my query.

3>> Is point 2 a bug in iReport?

4>> I was getting "Parameter #8 not set" error if I use the expression of variable below direclty(tried both with and without double quotes) in query. there are only total 5 parameters in query. Can anyone tell me what is going wrong in that case or give me a valid code snippet for that part

 

#################

Order by in Query looks like

 

orderÂby Name1, VKORG, Name1, PHier2,($V{ORDER_QUERY})Â,ÂJAHR,ÂMONAT

 

This variable to return me segment for 2 cols colA and colB basd on inputs as PARAM_SORT_ORDER (user inputs Boolean true or false) and PARAM_SORT_ON (user gives a code like A,B to specify the column header ).

So effectively it has to return like

colA Asc, colB

colA Desc, colB

colA , colB Asc

colA , colB Desc

 

So for variable ORDER_QUERY its initial expression part is using code like

( "PH3".equals($P{PARAM_SORT_ON}) )

?

( "PHier3 " + ( (java.lang.Boolean.TRUE==$P{PARAM_SORT_ORDER_ASC}) ? (" ASC,MATNR ") : (" DESC,MATNR ")) )

:

(

("MAT".equals($P{PARAM_SORT_ON}) )

?

( "MATNR "+ ( (java.lang.Boolean.TRUE==$P{PARAM_SORT_ORDER_ASC}) ? ( " ASC,PHier3" ) : (" DESC,PHier3 " )) )

:

( " PHier3, MATNR ")

)

 

######################################

But it gives error

]: java.sql.SQLException: Unrecognized SQL escape 'order' at line position 855.

at java.lang.Throwable.<init>(Throwable.java)

 

Post edited by: chetan_33, at: 2006/08/04 10:05

Post edited by: chetan_33, at: 2006/08/04 10:13

[chetan_33]

Hi All

I have other parameters in SELECT part which gets substituted fine so to make rudimentary column sort work I tried with just a simple pre defined string parameter

PARAM_SORT_CLAUSE = "PHier3 ASC,MATNR "

 

and query part changed to

order by Name1, VKORG, Name1, PHier2,$P{PARAM_SORT_CLAUSE} , JAHR, MONAT

 

It gives me exception as below. Any idea why it is not gettign substituted? Also please answer to above 4 queries since this is not good approach

 

 

ErrorÂfillingÂprint...

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)