concatenate selected cols into one not hardcoding cols

kaps · Post by **kaps** » Tue Jan 10, 2006 5:42 pm

I want to concat some of the cols from a record into one single column. I was hoping to do this with row merger but it seems it will concat all the columns. I could not figure out a way to concat some of the cols.

Basically I am trying to concat all the cols except key cols to find the CRC value for CDC.

I don't want to use : operator as I don't want to hard code col names.

Any Ideas....

Thanks

chulett · Post by **chulett** » Tue Jan 10, 2006 6:20 pm

Use the : operator.

vmcburney · Post by **vmcburney** » Tue Jan 10, 2006 7:37 pm

From memory if you define the key columns on the input and output links of the row merger stage it wont concatenate them. It treats them as pass through columns and concatenates those columns that are not on the output tab.

chulett · Post by **chulett** » Tue Jan 10, 2006 8:16 pm

Don't believe that's the case. I seem to recall it always concatenating all columns together.

Checked the docs and they don't mention anything special about the key fields, but it would be simple enough to test and see.

kcbland · Post by **kcbland** » Tue Jan 10, 2006 9:25 pm

Spool the data to a Sequential text file with a "," (comma) as the field separator, having the unique row identifier as the first column, an additional new column called "FAKE_SEPARATOR" with a derivation of literal "|" (pipe), and then the rest of the attribute columns after that. Read back the same Sequential text file, this time use field separator in the Format tab of "|" (pipe). You need 2 columns of metadata: "KEY" and "DATA". All of your attributes are magically packed with comma separators, which is important in using the CRC32 function. 123 concatenated with 456 is 12346, but so is 12 and 3456.

kaps · Post by **kaps** » Wed Jan 11, 2006 9:40 am

Thanks for all the inputs. I have tested vmcburney's idea but it does not help. Row Merger concatanates all the cols irrespective of key cols or output tab cols.

I will try Kenneth's Idea...

Thanks again...

ArndW · Post by **ArndW** » Wed Jan 11, 2006 9:49 am

I've used what Ken described many times in jobs to do this; plus this interim file should be declared as a pipe to avoid I/O and speed up processing.

kaps · Post by **kaps** » Wed Jan 11, 2006 1:44 pm

Thanks Arnd and Ken !

I tried Ken's idea and it worked. Then I was trying to use Arnd idea of pipe for performance but ran into problems. It simply hangs if I don't use timeout's and fails if I use timeouts.

Read the forum again and as per some one's suggestion I broke the job into two streams and it worked. I am curious to know the reason for this behaviour ? Anyone care to explain....

Thanks

kcbland · Post by **kcbland** » Wed Jan 11, 2006 2:32 pm

kaps wrote:Read the forum again and as per some one's suggestion I broke the job into two streams and it worked. I am curious to know the reason for this behaviour ? Anyone care to explain....

It's just one of those things.

The pipe solution is great for added performance, but if your performance is acceptable without using it then consider the easier maintenance without using a pipe.