Derive Column names from Rows

[UAUITSBI]

Shane,
Yes key columns are identifiable but they are not the same in each target table as per my previous example: ABC column is the key for staging table 1 and GHI column is the key for staging table 2.
That's a viable solution RCP didn't struck my mind. For Inserts there will be one job to load 2 tables and as I have 2 target tables probably 2 update jobs where I need to define user-defined UPDATE SQL on target table to update the records, I believe another 2 jobs are required if I want to perform delete again by defining user-defined SQL.
Isn't there a way that I can use a Change Capture stage in the RCP job and do all these 3 actions at once ? I know we need to mention the KEY columns in the Change capture stage but I am wondering if there is something that can be done with the power of RCP utility or may be any other approach?

Thanks Shane !!

[UAUITSBI]

Hello Shane,

I stumbled into a situation where in the Header columns and the first row are being read as ONE row, meaning:

Code: Select all

ROW 1: 
ABC|DEF|GHI|JKL|MNO|XYZ 
123|234|333|222|666|345

Code: Select all

ROW 2:
789||||555|

When I implement RCP from here on I am not sure if I will be able to load the table. I was expecting the logic to yield 3 rows:

Code: Select all

ROW 1: 
ABC|DEF|GHI|JKL|MNO|XYZ 

Code: Select all

ROW 2:
123|234|333|222|666|345

Code: Select all

ROW 3:
789||||555|

When I tried to use

Code: Select all

@OUTROWNUM = 0

then I am not able to get the header column.

Please let me know if I have missed something from the logic.

Appreciate the help.

Thanks

[ShaneMuir]

Are you reading from a file at this point? The stage is probably not reading the new line correctly.

At a guess its probably because of the output derivation I provided when creating the file.

Currently it is probably something like

Code: Select all

If @OUTROWNUM = 1 then svHeaders : Char(10) : svBuildData else svBuildData 

Try changing it to

Code: Select all

If @OUTROWNUM = 1 then svHeaders : Char(13):Char(10) : svBuildData else svBuildData 

Or change the new line setting in the sequential file stage

[UAUITSBI]

Shane,

That worked like a charm, including char(13) did the trick. My source is XML file and I am implementing this logic after parsing the data.

I will try to build the RCP with this data.

Thanks for this awesome solution !!

[chulett]

Char(10) = LF = UNIX record terminator
Char(13):Char(10) = CR/LF = Windows/DOS record terminator

[/pedantic]

[UAUITSBI]

Ahh... That's the miss. A parallel usage of different environments is tricky

[UAUITSBI]

Hello Shane,

Quick question:

After implementing the logic you suggested I was able to get the desired result, but recently I ran into a situation where number of columns are not the same for every row hence I am loosing the delimiters. This wasn't an issue earlier.

Example:

Code: Select all

ROW 1: 
ABC|DEF|GHI|JKL|MNO|XYZ 

ROW 2: 
123|234|333|222|666|345 

ROW 3: 
|789||123

ROW 3 has fewer delimiters hence that particular row is getting dropped while loading the target table in the next job. I expected the ROW 3 to be:

Code: Select all

ROW 3: 
|789||123||

Tried few iterations but couldn't find the reason behind it. Could you or anyone else please let me know if I am missing something here ?

Thanks !!

[UAUITSBI]

I had to make certain logic changes from the source while deriving the data to take error of the above issue. Thanks again for the help !!

[ShaneMuir]

Hello Shane,

After implementing the logic you suggested I was able to get the desired result, but recently I ran into a situation where number of columns are not the same for every row hence I am losing the delimiters. This wasn't an issue earlier.

Code: Select all

ROW 3: 
|789||123

Sorry not to answer earlier - been a bit busy.

The reason behind it is because you don't have all the columns in your input for that row so the output string stops when it runs out of columns to process. So if you are only up to column 4 of 6 then you would be missing 2 columns (ie delimiters).

The way to get around this is to append the number of delimiters to the end of the string on output and then limit that selection to the total number of delimiters expected.

So currently your output is something like

Code: Select all

If @OUTROWNUM = 1 then svHeaders : Char(13) : Char(10) : svBuildData else svBuildData 

What you want to do is have either a stage variable or just on the output so that it appends delimiters for the total number of columns to the end of svBuildData then selects the left number of columns up to that number eg
Create stage variable svOutputColumn

Code: Select all

svOutputColumn = (svBuildData : STR('|',svHeaderCount))['|',1,svHeaderCount]

What the above will do is take whatever the value is in svBuildData and append n pipe delimeters where n = svHeaderCount which is the total number of columns derived. The ['|',1,svHeaderCount] is the same as the Field() function and will look for pipes from the 1st column to the nth column as defined by svHeaderCount.

Then change your output to

Code: Select all

If @OUTROWNUM = 1 then svHeaders : Char(13) : Char(10) : svOutputColumn else svOutputColumn

That should do the trick without having to alter any source.

[UAUITSBI]

Ah okay. Initially I thought the same of appending the missing column delimiters to make it a complete row and load it. But I was not sure on the implementation.

Thanks for the great update. It makes more sense looking at it. I will implement this solution that makes it much reliable than the source logic update.

Again greatly appreciate your input and the help !! :D