Hello,
I have 2 tables: 1. Country 2. Language
In the country table for each Country there are ALL the languages available - meaning: Country = FR Language = US,NL,DE ....
Country Language
FR______US
FR______NL
FR______FR
What I am trying to create is for each country only the relevant languages + English(US)
In PL/SQL it would look something like the following:
case when country = 'FR' and (language = US or language = FR ) then language
case when country = 'NL' and ( language = US or language = NL ) then language
end case - ( No ELSE statement)
I don't mind Hardcoding the statement.
but I need to do it within DS
Any solutions will be welcome.
HOW TO CREATE A CASE STATEMENT IN TRANSFORMER
Moderators: chulett, rschirm, roy
HOW TO CREATE A CASE STATEMENT IN TRANSFORMER
Best Regards,
Lando
Lando
Lando,
the coding you can do directly in a transformer are limited to single-line constructs; so a classical CASE statement isn't possible. You can write your own "IF THEN ELSE IF THEN ELSE ..." statement but it won't be particularly legible. You could always write your own subroutine with 2 arguments, the Country and the Language, and put a CASE statement in there to return the appropriate value. But you will always need a value for the ELSE clause - even if it is the empty string or a null value.
If you just have a couple of conditions then the single-line derivation would look similar to your Pl/Sql; something like "IF In.Country = 'FR' AND (In.Language = 'US' OR In.Language = 'FR') THEN In.Language ELSE ''"
the coding you can do directly in a transformer are limited to single-line constructs; so a classical CASE statement isn't possible. You can write your own "IF THEN ELSE IF THEN ELSE ..." statement but it won't be particularly legible. You could always write your own subroutine with 2 arguments, the Country and the Language, and put a CASE statement in there to return the appropriate value. But you will always need a value for the ELSE clause - even if it is the empty string or a null value.
If you just have a couple of conditions then the single-line derivation would look similar to your Pl/Sql; something like "IF In.Country = 'FR' AND (In.Language = 'US' OR In.Language = 'FR') THEN In.Language ELSE ''"
</a>
Hi,
First perform a join between two tables based on the country key.
Then the condition can be applied in the transformer.
Create seperate Stage variable for each country
FR_CHECK --> If Country = 'FR' And (Language = 'US' OR Language = 'FR') Then 1 Else 0
NL_CHECK --> If Country = 'NL' And (Language = 'US' OR Language = 'NL') Then 1 Else 0
Later you can check the condition in the constraints as FR_CHECK Or NL_CHECK OR ..
-Kumar
First perform a join between two tables based on the country key.
Then the condition can be applied in the transformer.
Create seperate Stage variable for each country
FR_CHECK --> If Country = 'FR' And (Language = 'US' OR Language = 'FR') Then 1 Else 0
NL_CHECK --> If Country = 'NL' And (Language = 'US' OR Language = 'NL') Then 1 Else 0
Later you can check the condition in the constraints as FR_CHECK Or NL_CHECK OR ..
-Kumar
