Multiples columns into single one

eph · Post by **eph** » Fri Dec 02, 2011 6:59 am

Hi,

Have you tried column export in which you could concatenate your 200 fields' content in one particular field?

Eric

lote11 · Post by **lote11** » Fri Dec 02, 2011 7:55 am

Eric, can you explain me more about column export and where to do that.

thanks

Mike · Post by **Mike** » Fri Dec 02, 2011 8:03 am

Forget the column export. It's not available for server jobs.

Please clarify your requirement. Are you summing 200 columns? Are you concatenating 200 columns? Are you turning 1 row with 200 columns into 200 rows with 1 column each?

What exactly did you try in the transformer and what was not correct about it?

Mike

lote11 · Post by **lote11** » Fri Dec 02, 2011 8:21 am

Mike, im trying to sum 200 columns. Im using the transformer to sum all the columns from the sequential file output into one from the oracle input, for example : DSLink10.Field004+DSLink10.Field005+DSLink10.Field006

But the result of that sum is not correct when i check it in the database.
thanks

chulett · Post by **chulett** » Fri Dec 02, 2011 9:12 am

Are you certain that all of the fields contain valid numeric data? Are any of them... 'empty'?

Mike · Post by **Mike** » Fri Dec 02, 2011 9:32 am

Your approach with the transformer is fine.

Summing 200 elements is simply tedious and prone to error (e.g. missed elements or duplicated elements).

I'd suggest dumping the transformer results to a sequential or hashed file while you debug the expression.

Mike

lote11 · Post by **lote11** » Fri Dec 02, 2011 9:36 am

chulett wrote:Are you certain that all of the fields contain valid numeric data? Are any of them... 'empty'?

All the field are varchar and without empty fields

chulett · Post by **chulett** » Fri Dec 02, 2011 10:21 am

Didn't answer my 'valid numeric question' question. Can you explain to us what was 'not correct' about the summation you are doing? I think one sample would be good to get us going, an example of all of the field values you are adding plus the result that you got along with the result you expected. Then people can help figure out what might be going on.

lote11 · Post by **lote11** » Fri Dec 02, 2011 10:59 am

This is my sequential file info

Product Code | Product Desc | Sale 1 | Sale 2 | Sale 3
000001 Beverage 15 0 8
000002 Biscuits 10 18 0
000003 Gums 2 3 2

This is my database

Product Code | Product Desc | Total Sales
000001 Beverage 8
000002 Biscuits 0
000003 Gums 2

The thing is that the transform is not summing "Sales 1, Sales 2, Sales 3" into "Total Sales". It only shows the number of the last column.

chulett · Post by **chulett** » Fri Dec 02, 2011 12:29 pm

OK, should be simple enough... post your actual derivation, please.

lote11 · Post by **lote11** » Fri Dec 02, 2011 1:06 pm

My actual derivation is like this: DSLink10.Field004+DSLink10.Field005+DSLink10.Field006+DSLink10.Field007.... in the oracle column

chulett · Post by **chulett** » Fri Dec 02, 2011 1:24 pm

Sorry but you've already shown us what it is like, I was hoping to see the actual, real live derivation. Cut and paste it here unedited, please. If you are certain that all of your input columns have been mentioned (and only once) in the derivation and they only have a plus sign between them, then something else is going on. Perhaps the link to your total field in the target is incorrect? Meaning perhaps it is linked to the wrong transformer output port.

Have you tried running your job in the debugger?

qt_ky · Post by **qt_ky** » Fri Dec 02, 2011 2:19 pm

You said the total sales is wrong in Oracle. Have you written the output to another sequential file and checked the total sales there?

You also said the total sales value matches the last column, which would be Column 200. Have you double checked all around the last column in the total sales derivation?

Is it possible the total is so large that it overflows and becomes incorrect? I would guess it's not overflowing since you said it matches the column 200 value.

Craig asked if you are validating all the columns as having numeric values. Are you? Perhaps column 199 contains nulls or characters, in which case you need to exclude the column. A better practice may be to add number validation logic on every columns used in your derivation. For example a check that it's numeric and if it's not then add zero, handle nulls, etc. You can use find/replace to add stuff like that to all 200 columns at once.

What are the data types of these columns from the file and also the total sales column in Oracle?