Problems using a parameter in CASE

[thanosk]

I have set up a report in such a way that the people viewing the report can change the ORDER the report is displayed.

I am doing this by passing a parameter $P{OrderBy} and using a CASE syntax in the SQL query to get the appropriate result.

The corresponding code is here 

  <parameter name="OrderBy" class="java.lang.String">
<defaultValueExpression><![CDATA["b"]]></defaultValueExpression>
</parameter>
....
ORDER BY CASE
            WHEN $P{OrderBy} = 'a' then a.LCODE
            WHEN $P{OrderBy} = 'b' then a.LDESC
            WHEN $P{OrderBy} = 'c' then a.total
END

This returns java.sql.SQLException: Error converting data type varchar to float.

The absolutely weird thing is that if I chage the default value of the parameter to 'c' the report executes without any problems.
Also if I remove the last line from the CASE statement () (ie have only 2 WHEN statements in the CASE statement) the report executes with no problems

I am at a complete loss as to why this should be happening.

PS. I am connecting to an MS SQL Server using jdbc on a Windows Server Platform.

 

[kolaitis]

Suggestion :

Prepare your parameter from your calling java form :

 

reportParameters.put("OrderBy", "ORDER BY your_condition");

 

In iReport query just below Where clause , write : $P!{ORDER_BY}

 

Good Luck.

 

[thanosk]

 I should have mentioned that I am using Jasper Server to view the report.

I really think that this must be a bug.

LCODE and LDESK are varchar types and total is double.
If I call the report with a default to sort by total it executes correctly.
If I call the report to execute and sort by any of the other 2 fields I get the error.

Should I file this as a bug or I am doing something seriously wrong?

 

[cbarlow3]

I've run into similar problems when my ORDER BY includes a CASE statement that results in differing data types.  Here's one solution I ended up with:

ORDER BY
  LNTYPE_DESCRIPTION,
  CASE $P{SortOrder}
    WHEN 'AN' THEN ACCOUNT.ACCOUNT_NUMBER
    WHEN 'MD' THEN COALESCE(VARCHAR_FORMAT(LOAN.MATURITY_DATE,'YYYY-MM-DD'),'')
    WHEN 'CL' THEN COALESCE(VARCHAR_FORMAT(LOAN.CREDIT_LIMIT_EXPIRATION_DATE,'YYYY-MM-DD'),'')
    ELSE ACCOUNT.ACCOUNT_NUMBER
  END

Actually, this reminds me of another problem I was having for a while:  my input control was displaying a custom list of values to select from "Accoun Number", "Maturity Date", and "Credit Limit", but I had to settle for making the value that is passed by the parameter always be the same length abbreviation.  If I made the parameter sometimes be "AcctNum" and sometimes be "CredLmtExpDate", I would get errors.  I can't remember the details; I just remember that at some point I gave up and started living with these two limitations in the cases where I need to use a CASE in an ORDER BY.

By the way, I think the solution kolaitis suggests will work, and I've often seen Matt Dahlman also suggest similar solutions, and I really respect his expertise.  But it always makes me a little queasy having the actual text of the query itself be variable.  I like to draw pretty clear boundaries between variables and "code", and something tells me I'm looking for trouble if I start blurring that distinction by storing snippets of a query in a text variable.  Hey, that's MY hangup.

Carl