CASE statement with ODBC in iReport (Jasper)

[linker3000]

Hi,

Is it possible to use a CASE statement with the Sun JDBC-ODBC module, connecting to an Access 97 database? I have done some searching and am getting conflicting advice. In essence, I want to do something like this:

case when CLN_Bad_Debt=1 then 'no' else 'Yes' end as Bad_Debt2

But all the variants of SQL and ODBC syntax I have tried throw up a syntax error.

Thanks

 

[k5tm]

You might try:

IF(CLN_Bad_Debt=1,'no',Yes')

While this is not standard ODBC grammar, it is supported by at least one of the popular SQL engines used by many ODBC drivers.

Tom Morrison

http://www.liant.com

 

[SkipVought]

And if IF fails, use IIF, which is an MS Access function.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[linker3000]

Hi Guys - putting it all together, with a minor tweak for the values returned, and we have a winner:

IIF (CLN_Bad_Debt = 0 , '' , 'Bad Debt') as Bad_Debt

Many, many thanks!

NOW:

Any thoughts on CLN_Client_Title_id which represents the salutation as:
1 = Mr
2 = Mrs
3 = Miss etc.!

I'm off to try some nested IIFs but otherwise I guess I can post-process this later as the output's going into a spreadsheet.

 

[linker3000]

Hi,

You can scrub my last challenge as I have just found a small table containing the salutations and have picked up the right text with a 'join'