Source table has 3 columns and target table has 1 column

[DS_MJ]

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

Code: Select all

                         Oracle Tbl (Get Next Val)
                                    |
        ---> Trans --->            \|/
Src Tbl ---> Trans ---> Lnk_Coll--->Trans ---> Target TBL
        ---> Trans --->

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

Code: Select all

                         Oracle Tbl (Get Next Val)
                                    |
        ---> Trans --->            \|/
Src Tbl ---> Trans ---> Lnk_Coll--->Trans ---> Target TBL
        ---> Trans --->

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.

[ray.wurlod]

What is the mapping to each of the target columns?

You're doing way too much work, but we need to know what has to go into each target column before being able to help further.

[DS_MJ]

What is the mapping to each of the target columns?

You're doing way too much work, but we need to know what has to go into each target column before being able to help further.

The mapping to the Target col is:
- Populate this element (COL_A) with either COL_A1, COL_A2,COL_A3 if the value is not null. There will be a new row if the correspond Process date relief x or blank is not null.
- Populate this element (COL_B) with either COL_B1, COL_B2,COL_B3 if the value is not null. There will be a new row if the correspond Process date relief x or blank is not null.

For Target COL_C
IF COL_C1 is not null
Add 15 days to date for this row.
IF COL_C2 is not null
Add 15 days to date for this row.
IF COL_C3 is not null
Add 15 days to date for this row.

Thanks,
MJ

[paultechm]

Hello


Create 3 stage variables (no need of split and collect the data )

1)if not(isnull(ColumnA1)) Then ColumnA1 Else if not(isnull(ColumnA2)) Then ColumnA2 Else ColumnA3 Else 0= StgA
2)if not(isnull(ColumnB1)) Then ColumnB1 Else if not(isnull(ColumnB2)) Then ColumnB2 Else ColumnB3 Else 0 = StgB
2)if not(isnull(ColumnC1)) Then ColumnC1 Else if not(isnull(ColumnC2)) Then ColumnC2 Else ColumnC3 Else 0 = StgC

Filter based on StgA<>0 and StgB<>0 and StgC<>0

StgA---->ColumnA
StgB---->ColumnB---->trans(to get the sequence number)---> target
StgB---->ColumnC

-Paul

[DS_MJ]

Hello


Create 3 stage variables (no need of split and collect the data )

1)if not(isnull(ColumnA1)) Then ColumnA1 Else if not(isnull(ColumnA2)) Then ColumnA2 Else ColumnA3 Else 0= StgA
2)if not(isnull(ColumnB1)) Then ColumnB1 Else if not(isnull(ColumnB2)) Then ColumnB2 Else ColumnB3 Else 0 = StgB
2)if not(isnull(ColumnC1)) Then ColumnC1 Else if not(isnull(ColumnC2)) Then ColumnC2 Else ColumnC3 Else 0 = StgC

Filter based on StgA<>0 and StgB<>0 and StgC<>0

StgA---->ColumnA
StgB---->ColumnB---->trans(to get the sequence number)---> target
StgB---->ColumnC

-Paul

Thanks Paul, appreciate your quick response.
I was trying to implement what you said and hence delay in reply.

I have a little more complication that I have not mentioned.
That is I have other 2 columns in my Src Table.
COL_ID NUMBER
COL_CODE VARCHAR
which I need to load into the Target table as well.

So SRC TABLE has
COL_A1 VARCHAR
COL_A2 VARCHAR
COL_A3 VARCHAR
COL_B1 VARCHAR
COL_B2 VARCHAR
COL_B3 VARCHAR
COL_C1 VARCHAR
COL_C2 VARCHAR
COL_C3 VARCHAR
COL_ID NUMBER
COL_CODE VARCHAR

TARGET TABLE
UNIQ_ID NUMBER
COL_A DATE
COL_B DATE
COL_C DATE
COL_ID NUMBER
COL_CODE VARCHAR

What i have:
COL Data
COL_A1 '02/01/2011'
COL_A2 '03/01/2011'
COL_A3 NULL
COL_B1 '03/01/2011'
COL_B2 NULL
COL_B3 '05/01/2011'
COL_C1 NULL
COL_C2 '08/01/2011'
COL_C3 NULL
COL_ID 123
COL_CODE 1

What I need to do

Code: Select all

Target  TABLE
UNIQ_ID     COL_A             COL_B         COL_C     COL_ID    COL_CODE  
1          02/01/2011                                 123          1
2          03/01/2011                                 123          1
3                            03/01/2011               123          1
4                            05/01/2011               123          1
5                                     '08/01/2011'    123          1

Did the following in StageVariable

StgA = 0
StgB = 0
StgC = 0

StgA =
if not(isnull(COL_A1)) Then 1 Else if not(isnull(COL_A2)) Then 1 Else if not(Isnull(COL_A3)) then 1 Else 0

StgB=
if not(isnull(COL_B1)) Then 1 Else if not(isnull(COL_B2)) Then 1 Else if not(Isnull(COL_B3)) then 1 Else 0

StgC=
if not(isnull(COL_C1)) Then 1 Else if not(isnull(COL_C2)) Then 1 Else if not(Isnull(COL_C3)) then 1 Else 0




Then in the Transformer I have the following columns on the right side
UNIQ_ID NUMBER
COL_A DATE
COL_B DATE
COL_C DATE
COL_ID NUMBER
COL_CODE VARCHAR

I do the following derivation for COL_A:
if not(isnull(COL_A1)) Then Iconv(Trim(COL_A1), "D-YMD")
Else
if not(isnull(COL_A2)) Then Iconv(Trim(COL_A2), "D-YMD")
Else
if not(Isnull(COL_A3)) then Iconv(Trim(COL_A3), "D-YMD")
Else @NULL

COL_B
if not(isnull(COL_B1)) Then Iconv(Trim(COL_B1), "D-YMD")
Else
if not(isnull(COL_B2)) Then Iconv(Trim(COL_B2), "D-YMD")
Else
if not(Isnull(COL_B3)) then Iconv(Trim(COL_B3), "D-YMD")
Else @NULL

COL_C
if not(isnull(COL_C1)) Then Iconv(Trim(COL_C1), "D-YMD")
Else
if not(isnull(COL_C2)) Then Iconv(Trim(COL_C2), "D-YMD")
Else
if not(Isnull(COL_C3)) then Iconv(Trim(COL_C3), "D-YMD")
Else @NULL

Other columns
UNIQ_ID ---> Lookup Oracle table where I do a get Next val and map to this col.
COL_ID Straight Map
COL_CODE Straight Map

However, how do I put the filter and how do I populate the COL_B and COL_C if I use the StgA filter?

Thanks and Regards,

[paultechm]

Hi,

It is altogether a different requirement , This is for converting single row into multiple rows

Split these records in the transformer with out any constraint then pivot these links such as

Code: Select all

Link1:

COL_A1        			             COL_ID	
COL_A2       			             COL_CODE
COL_A3---------->pivot             CoL_A(COL_A1,CoL_A2)
COL_ID                                   Col_B  
COL_CODE                             Col_C
Col_B(null--'hardcode')
Col_C(null)

Link2:

COL_B1                                              COL_ID
COL_B2                                             COL_CODE 
COL_B3------------->Pivot                     CoL_A
COL_ID                                        Col_B(COL_B1,CoL_B2)  
COL_CODE                                            Col_C
Col_A(null)
Col_C(null)


Link3:

COL_C1                                              COL_ID
COL_C2 ------------->pivot                   COL_CODE 
COL_C3                                              CoL_A
COL_ID                                               Col_B
COL_CODE                                       Col_C(COL_c1,CoL_c2)
ColA
ColB

Collect the above 3 links using link collector then lookup for the sequencenumber and load to the target(sorry for that alignments )



-Paul