Page 1 of 1
case statment
Posted: Wed Apr 13, 2011 11:34 am
by times29
Hi,
How can i put case logic in datastage i want to put below logic in datastage
(ID) || ':' ||
case
when (COUNTRY) in ('US','CA') then 'US'
when (COUNTRY) in ('IE','GB') then 'GB'
when (COUNTRY) in ('NZ','AU') then 'AU'
else (COUNTRY)
end ||':' ||
case
when (COUNTRY) in ('US','CA') then 'US'
when (COUNTRY) in ('IE','GB') then 'GB'
when (COUNTRY) in ('NZ','AU') then 'AU'
else (COUNTRY)
end as BUSINESS_KEY,
Posted: Wed Apr 13, 2011 1:07 pm
by vivekgadwal
Using If-Then-Else
Code: Select all
If COUNTRY = 'US' Or COUNTRY = 'CA'
Then
...
...
Posted: Wed Apr 13, 2011 1:30 pm
by times29
Can you put few more lines?
Posted: Wed Apr 13, 2011 1:31 pm
by times29
Can you put few more lines?
Posted: Wed Apr 13, 2011 1:36 pm
by vivekgadwal
Code: Select all
If COUNTRY = 'US' Or COUNTRY = 'CA'
Then
'US'
Else
IF COUNTRY = 'IE' Or COUNTRY = 'GB'
Then
'GB'
Else
If ...
It can get pretty convoluted...this is a brute force attack on your question.
Posted: Wed Apr 13, 2011 1:45 pm
by DSguru2B
Another way is to use the INDEX function. Something like
Code: Select all
if index('US,CA', in.COUNTRY, 1) > 0
then
'US'
else if index('IE,GB', in.COUNTRY, 1) > 0
THEN
'GB'
else if index('NZ,AU', in.COUNTRY, 1) > 0
THEN 'AU'
ELSE in.COUNTRY
Posted: Wed Apr 13, 2011 1:46 pm
by times29
And what about other case so are you saying do in two different columns for same transformer and then use another tarnsformer to merge two case
stmt
Posted: Wed Apr 13, 2011 1:46 pm
by DSguru2B
Do it in two different stage variables and just concatenate them.
Posted: Wed Apr 13, 2011 1:50 pm
by vivekgadwal
DSGuru2B is right. That would make it simpler. What I was trying to say is you can concatenate the second if then else in the same derivation/stage variable or a different one - depending on how aesthetic you want to be
Posted: Wed Apr 13, 2011 1:55 pm
by times29
i just renew my membership looks like it takes some time i have to wait for dsguru comment then
Posted: Wed Apr 13, 2011 2:05 pm
by vivekgadwal
times29,
You can solve this in myriad ways. One of the ways is to place your case statements (2 of them) in two stage variables and then concatenate all that you want in another stage variable. Use this final variable in a derivation column of your choice. Otherwise, do it all in one stage variable or derivation, whichever suits your requirement.
DSGuru2B also mentioned the same. Hope this is detailed enough for you...