One Star RSA
One Star

How to update only particular columns with tELT components Postgresql?

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
1 REPLY
Moderator

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

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.