case statment

times29 · Post by **times29** » Wed Apr 13, 2011 11:34 am

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,

vivekgadwal · Post by **vivekgadwal** » Wed Apr 13, 2011 1:07 pm

Using If-Then-Else

Code: Select all

If COUNTRY = 'US' Or COUNTRY = 'CA'
Then
    ...
    ...

times29 · Post by **times29** » Wed Apr 13, 2011 1:30 pm

Can you put few more lines?

times29 · Post by **times29** » Wed Apr 13, 2011 1:31 pm

Can you put few more lines?

vivekgadwal · Post by **vivekgadwal** » Wed Apr 13, 2011 1:36 pm

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.

DSguru2B · Post by **DSguru2B** » Wed Apr 13, 2011 1:45 pm

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

times29 · Post by **times29** » Wed Apr 13, 2011 1:46 pm

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

DSguru2B · Post by **DSguru2B** » Wed Apr 13, 2011 1:46 pm

Do it in two different stage variables and just concatenate them.

vivekgadwal · Post by **vivekgadwal** » Wed Apr 13, 2011 1:50 pm

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

times29 · Post by **times29** » Wed Apr 13, 2011 1:55 pm

i just renew my membership looks like it takes some time i have to wait for dsguru comment then

vivekgadwal · Post by **vivekgadwal** » Wed Apr 13, 2011 2: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...