Can i use a CASE statement in a SELECT statement ?

[georgesebastian]

Hi,

Can i use a CASE statement in a SELECT statement.This Select statement is quering a hased file which is used inside a routine?

If possible kindly requesting you for the sql syntax

Thanks
George Sebastian

[chulett]

Hmmm... I do not believe so. I think you could use a "decode" to do something similar, a search of the forum should turn something up. Or you could wait for someone who actually knows the answer to respond.

[kduke]

In Universe SELECT you need to create an I-Descriptor or equivalent using EVAL. Then it looks like IF THEN.

[ray.wurlod]

DataStage/SQL does not support a CASE structure.

You can, as Kim noted, use an I-descriptor or EVAL field modifier to build a nested If..Then..Else expression that would be equivalent.

[chulett]

Ah, yes... EVAL was the decode like thing I was trying to think of.

[georgesebastian]

Hi,

Thanks for the reply.
I am sorry i dont know how to use EVAL or I-descriptor:( I went through the PDF (universe Gudie to Retrive) but still not clear about EVAL.

how will i use EVAL insted of CASE in the below SQL
SELECT CASE BEG_AMT WHEN 0 THEN 1 ELSE BEG_AMT END FROM HASH_PS_F_LEDGER

Thanks
George

[georgesebastian]

Hi,

Can somebody tell "how can EVAL be used like DECODE"?

Thanks
George

[ray.wurlod]

Be patient. DSXchange is an all-volunteer site whose members post as and when they can. Most of us also have to earn a living, so posting on DSXchange does not have highest priority. Some of us even manage to have a life.

Code: Select all

SELECT EVAL "IF BEG_AMT = 0 THEN 1 ELSE BEG_AMT" FROM HASH_PS_F_LEFGER;

It's the same style of If..Then..Else expression you would use in a DataStage expression anywhere else in the product.

[DS_SUPPORT]

Thanks a lot Ray.

EVAL worked for me

Thanks
George

[ray.wurlod]

Here's an even weirder variant.

Code: Select all

SELECT BEG_AMT CONV "S;*;1;1" FROM HASH_PS_F_LEDGER;

This is called a "substitution conversion".

[Pravenai]

DataStage/SQL does not support a CASE structure.

You can, as Kim noted, use an I-descriptor or EVAL field modifier to build a nested If..Then..Else expression that would be equivalent. ...

hi Ray,

You said SQL does not support a CASE statement. Do you mean CASE statement cannot be used while fetching records into DB2 stage in Datastage? Case statement works fine when you are using it in select clause to view records from a DB2 table.

[ray.wurlod]

You said SQL does not support a CASE statement.

I did not.

I said that DataStage/SQL (a particular query language, akin to UniVerse/SQL) does not support a CASE statement.