How to update only particular columns with tELT components Postgresql?

Anonymous — Wed, 03 Jun 2015 13:41:57 GMT

Hello,
I created a job on a Postgres database with the TOS Postgres ELT components.
My goal: to update a dimension table from a prepared (staging) table.
I select all relevant rows through the tELTPostgresqlInput, connect it to the tELT Map and this to tELT Output.
In tELT Ouput the "action on data" is set to "Update"
When I run the job I get following query (some fields where left out for brevity):
UPDATE "dm_pos_reports"."item_dim"
SET "item_dwh_id"=(SELECT "stg_pos"."stg_item_dim_upd"."item_dwh_id" FROM "stg_pos"."stg_item_dim_upd" WHERE dm_pos_reports."item_dim"."item_dwh_id" = "stg_pos"."stg_item_dim_upd"."item_dwh_id" ),
"viipr_item_id"=(SELECT "stg_pos"."stg_item_dim_upd"."viipr_item_id" FROM "stg_pos"."stg_item_dim_upd" WHERE dm_pos_reports."item_dim"."item_dwh_id" = "stg_pos"."stg_item_dim_upd"."item_dwh_id" ),
"item_name"=(SELECT "stg_pos"."stg_item_dim_upd"."item_name" FROM "stg_pos"."stg_item_dim_upd" WHERE dm_pos_reports."item_dim"."item_dwh_id" = "stg_pos"."stg_item_dim_upd"."item_dwh_id" )
Note: in the tELT Map component I added a where clause, so that each subselect as visibile in above query would contain this where clause. => Is this the way to do it ?
When this query is run, I get an error on item_dwh_id being null, yet the input data does not contain any item_dwh_id is null records.
However the fact that a key column is being updated is incorrect in the generated query (i.e. the item_dwh_id should not be in the update, it's the key column).
How to solve this?
I already tried to remove the item_dwh_id column from the target schema in the Map component.
But then I get a java error:
Exception in component tELTPostgresqlOutput_2
java.lang.ArrayIndexOutOfBoundsException: 10
at posreports.dwh_item_dim_0_1.dwh_item_dim.tELTPostgresqlMap_2Process(dwh_item_dim.java:1677)
Apparently the java code generated is still assuming the original number of columns.
Note: I tried changing the schema of the tELTPostgresqlOutput table. Yet eventhough the table schema is set to "Built-in", the schema edit is not retained.
Every time I open the edit schema window, the original table schema as in the Metadata is shown again.
All help appreciated. Thx.
br,
Ruben

Re: How to update only particular columns with tELT components Postgresql?

Anonymous — Mon, 15 Jun 2015 07:56:04 GMT

Hi,
Would you mind uploading your tELT Map component setting screenshot into forum?
More information will be helpful for us to address your issue.
Best regards
Sabrina

topic Re: How to update only particular columns with tELT components Postgresql? in Talend Studio

How to update only particular columns with tELT components Postgresql?

Re: How to update only particular columns with tELT components Postgresql?