case statment
Moderators: chulett, rschirm, roy
case statment
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,
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,
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
Using If-Then-Else
Code: Select all
If COUNTRY = 'US' Or COUNTRY = 'CA'
Then
...
...
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
Code: Select all
If COUNTRY = 'US' Or COUNTRY = 'CA'
Then
'US'
Else
IF COUNTRY = 'IE' Or COUNTRY = 'GB'
Then
'GB'
Else
If ...
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
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
Last edited by DSguru2B on Wed Apr 13, 2011 1:46 pm, edited 1 time in total.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
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...
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...
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted