Oracle Connector as target in PIVOT stage ?

bond88 · Post by **bond88** » Mon Dec 31, 2012 3:59 pm

Hi,
I am able to write pivot (VERTICAL PIVOT) results to a sequential file without having any error but I am getting errors in the case of writing to a Oracle table. First of all, table is creating with all the Vertical PIVOT values instead of values in transformer stage (I am using transformer after PIVOT stage).

Thank you,

chulett · Post by **chulett** » Mon Dec 31, 2012 4:19 pm

You'd have to let us know the errors you are getting before anyone could help. And examples of your data couldn't hurt, either, especially to help clarify your "instead of values in transformer" statement which I don't really follow at the moment.

bond88 · Post by **bond88** » Mon Dec 31, 2012 4:44 pm

Sorry Chulett,

Job is like this

oracle connectors(Source) --> joins --> transformer --> pivot (vertical) --> transformer --> oracle connector (Target).

Columns are ID, PID, Name, Email. I am grouping on ID and pivoting remaining columns PID, Name and Email and I dropped one column intentionally after JOIN stage because I don't need that column in to target (Just need for joining condition). While I was trying to execute this job it is creating table with all columns(ID, PID to PID19, Name to Name19, Email to Email19, Since I took array size as 20) instead of ID, PID, Name, Email.

Data Example:

Before Pivot:

ID PID Name Email

1 10 ABC AB@ex.com
1 11 DEF DE@ex.com
2 9 GHI GH@ex.com

After Pivot:

ID PID Name Email

1 10,11 ABC,DEF AB@ex.com,DE@ex.com
2 9 GHI GH@ex.com

chulett · Post by **chulett** » Mon Dec 31, 2012 5:25 pm

Still haven't specified the error you get. And is the problem happening because you've told it to create the table rather than populating an already existing table?

ray.wurlod · Post by **ray.wurlod** » Tue Jan 01, 2013 12:25 am

That's the way the PX Pivot stage works when executing a vertical pivot. You can parse and rename the columns as desired using, for example, Copy and Column Export stages.

chulett · Post by **chulett** » Tue Jan 01, 2013 9:06 am

A little lost with your answer, Ray. Why would the target connector have any clue about what the data looked like before/during the pivot? That seems to be the behaviour being reported. The transformer between the two should mask that and (it would seem to me) that all the connector should see is the post-pivoted column layout.

What am I missing? How would the stages you mention help? Go easy, was up late and it's still early for me.

ray.wurlod · Post by **ray.wurlod** » Tue Jan 01, 2013 3:03 pm

The PXPivot stage parses into separate columns with generated names based on the first pivot column.

Also, it appears that the OP requires comma-delimited lists in single columns, not multiple columns. This is not what the PXPivot stage provides, hence the need for downstream processing.

bond88 · Post by **bond88** » Wed Jan 02, 2013 9:10 am

Thanks Ray,
I have a doubt, in my job I am not sure how many columns will come with same ID, so thats why I gave array size of 20 in PxPivot Stage. Previously I was writing to a sequential file after transformer stage (PxPIVOT stage --> Transformer --> Sequential file). In transformer stage I was able to check whether the pivot column has data or null values. If there are values then only it has to concatenate to the column. Now how can I handle the scenario by using Copy and Column Export stages.

Issue:

Before Pivot:

ID PID Name Email

1 10 ABC AB@ex.com
1 11 DEF DE@ex.com
2 9 GHI GH@ex.com

After Pivot:

ID PID Name Email

1 10,11 ABC,DEF AB@ex.com,DE@ex.com
2,null or zero 9 GHI,null GH@ex.com,null or zero

I handled this issue by using transformer in the case of writing to a sequential file at that time it didnt create any extra colums. It just created whatever are there in transformer stage but in the case of oracle connector as target it is acting different.