Netezza - Replace table - Order of Columns not preserved
Posted: Thu Jun 25, 2009 10:27 am
Hello,
I am using the Netezza Enterprise Stage to load data into a Netezza table. I am reading data using an ODBC Enterprise Stage. This is how my design looks like
The order of fields in my input data is as follows
field1
field2
field3
field4
In my transformer I am doing a NullToEmpty on field3 and I am preserving the order of my fields on the output Netezza stage i.e.
field1
field2
field3
field4
My Netezza stage has the following options :
Load Method = nzload and Write Mode = Replace
Since Replace has to drop and recreate my table each time, I expect it to recreate the table with fields in the order I specified in my Transformer i.e.
field1
field2
field3
field4
But it does not do that. In fact it re-orders the fields to have the field with the derivation on top
field3
field1
field2
field4
Has anyone come across this issue? I have seen this with only with the Netezza stage. This happens only when I do some sort of derivation in the Transformer. The field with the derivation is pushed to the top regardless of the order that I specify. This does not happen with any other stage like Filter. The transformer with the derivation is throwing the order of.
Thanks in advance,
Ranjana
I am using the Netezza Enterprise Stage to load data into a Netezza table. I am reading data using an ODBC Enterprise Stage. This is how my design looks like
Code: Select all
ODBC Enterprise Stage => Transformer => Netezza Enterprise Stage
field1
field2
field3
field4
In my transformer I am doing a NullToEmpty on field3 and I am preserving the order of my fields on the output Netezza stage i.e.
field1
field2
field3
field4
My Netezza stage has the following options :
Load Method = nzload and Write Mode = Replace
Since Replace has to drop and recreate my table each time, I expect it to recreate the table with fields in the order I specified in my Transformer i.e.
field1
field2
field3
field4
But it does not do that. In fact it re-orders the fields to have the field with the derivation on top
field3
field1
field2
field4
Has anyone come across this issue? I have seen this with only with the Netezza stage. This happens only when I do some sort of derivation in the Transformer. The field with the derivation is pushed to the top regardless of the order that I specify. This does not happen with any other stage like Filter. The transformer with the derivation is throwing the order of.
Thanks in advance,
Ranjana