Source table has 3 columns and target table has 1 column
Posted: Mon May 02, 2011 6:58 pm
Hello:
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
I need to check if any of COL_A1, COL_A2, COL_A3 are nullable...If yes dont bring them accross and dont assign a new Id. If NOT NULL then bring them accross to my target table and assign a new ID.
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
In the stage varialbe create stage variable for
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.
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.