I have a source table with 9 columns and Target table with 3 columns.
These are date columns which are nullable columns.
On source side they are Varchar and Target its a date field.
So my SOURCE table has 9 Columns
COL_A1, (NULLABLE)
COL_A2, (NULLABLE)
COL_A3, (NULLABLE)
COL_B1, (NULLABLE)
COL_B2, (NULLABLE)
COL_B3, (NULLABLE)
COL_C1, (NULLABLE)
COL_C2 (NULLABLE) and
COL_C3 (NULLABLE)
My TARGET table has 3 Columns
COL_A, (NULLABLE)
COL_B (NULLABLE) &
COL_C (NULLABLE)
Design
Code: Select all
Oracle Tbl (Get Next Val)
|
---> Trans ---> \|/
Src Tbl ---> Trans ---> Lnk_Coll--->Trans ---> Target TBL
---> Trans --->
I do a lookup to get nextval from Dual to get the unique id's and then assign the Id's in Transformer 2.
In my 1st Transformer I create 3 Stage Variable. Their initial value is set to 0.
Stage_Varialbe 1
Stage_Varialbe 2
Stage_Varialbe 3
I want to check if a COL_A1, COL_B1, COL_C1 is null then give a value of 0 else give a value of 1.
Q -1: How do I write this if then else condition?
Then in the 1st Transformer use the above created stage variable's as constraints to filter across only those rows that have value. So I put the constraint Stage_Varialbe 1 <> 0
Take only fields COL_A1, COL_B1, COL_C1 and populate my target table COL_A, COL_B & COL_C
Then I do the 2nd Stage Variable 2 <> 0
Take only fields COL_A2, COL_B2, COL_C2 and populate my target table COL_A, COL_B & COL_C
Then I do a 3rd. Stage Variable 3 <> 0
Take only fields COL_A3, COL_B3, COL_C3 and populate my target table COL_A, COL_B & COL_C
Q 2: This is the correct way to do it?If yes then please help me to write the If then else condition in the Stage Variable.
Another way to do
Code: Select all
Oracle Tbl (Get Next Val)
|
---> Trans ---> \|/
Src Tbl ---> Trans ---> Lnk_Coll--->Trans ---> Target TBL
---> Trans --->
svA1 If Isnull(COL_A1) then 0 else 1
svA2 If Isnull(COL_A2) then 0 else 1
svA3 If Isnull(COL_A3) then 0 else 1
svB1 If Isnull(COL_B1) then 0 else 1
svB2 If Isnull(COL_B2) then 0 else 1
svB3 If Isnull(COL_B3) then 0 else 1
svC1 If Isnull(COL_C1) then 0 else 1
svC2 If Isnull(COL_C2) then 0 else 1
svC3 If Isnull(COL_C3) then 0 else 1
Put constraints svA1 <> 0 in Link 1...Link n of 1st Transformer
But the delima is how do I pupulate COL B & COLC the constraint is only for COL_A?
Please advice.
Thanks in advance.