Using case in select clause?

1
votes

I want to use a CASE Statement to check a parameter conditionally like if parameter is an empty string I want to return Nil if not then string 'test' but this does not work. Also I don't want to use COALESCE

select TEST=CASE '' WHEN '' THEN 'Nil' ELSE 'Test' END,current_timestamp from sysibm.sysdummy1;

I get an error

SQL State: 42601 Vendor Code: -104 Message: [SQL0104] Token = was not valid. Valid tokens: + - AS . Cause . . . . . : A syntax error was detected at token =. Token = is not a valid token. A partial list of valid tokens is + - AS . This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token =. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is , correct the SQL statement because it does not end with a valid clause.

2
sqldb2
While I accept that constrained writing can be a wonderful past time, do you have a reason for avoiding COALESCE()? - onedaywhen
@onedaywhen I had a crystal report parameter which was passed inside my query, that parameter could be empty or otherwise. COALESCE works on a database column if I'm not wrong so yeah constrained writing does work sometimes ;), Can you reproduce my query with COALESCE using parameters? Damien. - Zo Has
Lacking as I do both Crystal and DB2 the answer must be no! - onedaywhen

2 Answers

1
votes
select CASE '' WHEN '' THEN 'Nil' ELSE 'Test' END as TEST,current_timestamp from sysibm.sysdummy1;

Replace something by the intended column.

For more info, check THIS.

0
votes

Your case syntax is incorrect the correct one is,

select TEST=CASE <fieldname> WHEN '' THEN 'Nil' ELSE 'Test' END,current_timestamp from sysibm.sysdummy1;