Can i use a CASE statement in a SELECT statement ?
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 83
- Joined: Tue Dec 19, 2006 8:38 am
Can i use a CASE statement in a SELECT statement ?
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 83
- Joined: Tue Dec 19, 2006 8:38 am
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
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
-
- Participant
- Posts: 83
- Joined: Tue Dec 19, 2006 8:38 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
It's the same style of If..Then..Else expression you would use in a DataStage expression anywhere else in the product.
Code: Select all
SELECT EVAL "IF BEG_AMT = 0 THEN 1 ELSE BEG_AMT" FROM HASH_PS_F_LEFGER;
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 232
- Joined: Fri Aug 04, 2006 1:20 am
- Location: Bangalore
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Here's an even weirder variant.
This is called a "substitution conversion".
Code: Select all
SELECT BEG_AMT CONV "S;*;1;1" FROM HASH_PS_F_LEDGER;
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
hi Ray,ray.wurlod wrote: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. ...
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I did not.Pravenai wrote:You said SQL does not support a CASE statement.
I said that DataStage/SQL (a particular query language, akin to UniVerse/SQL) does not support a CASE statement.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.