Multiples columns into single one
Moderators: chulett, rschirm, roy
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
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
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
But the result of that sum is not correct when i check it in the database.
thanks
Juan Escalante
IT Consultant
IT Consultant
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
Juan Escalante
IT Consultant
IT Consultant
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?
Have you tried running your job in the debugger?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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?
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?
Choose a job you love, and you will never have to work a day in your life. - Confucius