Derive Column names from Rows
Moderators: chulett, rschirm, roy
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 !!
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 !!
Hello Shane,
I stumbled into a situation where in the Header columns and the first row are being read as ONE row, meaning:
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:
When I tried to use 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
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
Please let me know if I have missed something from the logic.
Appreciate the help.
Thanks
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
Try changing it to
Or change the new line setting in the sequential file stage
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
Code: Select all
If @OUTROWNUM = 1 then svHeaders : Char(13):Char(10) : svBuildData else svBuildData
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:
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:
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 !!
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
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 !!
Sorry not to answer earlier - been a bit busy.UAUITSBI wrote: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
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
Create stage variable svOutputColumn
Code: Select all
svOutputColumn = (svBuildData : STR('|',svHeaderCount))['|',1,svHeaderCount]
Then change your output to
Code: Select all
If @OUTROWNUM = 1 then svHeaders : Char(13) : Char(10) : svOutputColumn else svOutputColumn
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
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