topic Re: tELTOutput update table Snowflake in Talend Studio

tELTOutput update table Snowflake

Nandita1s — Fri, 06 Mar 2020 05:12:15 GMT

I have a scenario where I have to insert into a snowflake table and also update it using the ELT approach because the data volume is high. All ELT components are working fine except for the ELTOutput update feature. ELTOutput update is giving an erroneous output (the error being "single row subquery returns multiple rows"). This error is not related to data or keys, it is simply the way the update query is working. Can anyone help here and suggest if I am missing something here? Here's an example data:

Input: MEETING_ID, MEETING_NBR, MEETING_PURPOSE

Insert: INSERT INTO FACT_B (MEETING_ID, MEETING_NBR, MEETING_PURPOSE) (SELECT MEETING_ID, MEETING_NBR, MEETING_PURPOSE FROM STG_B LEFT OUTER JOIN DIM_EMP ON (DIM_EMP.EMP_CD = STG_B.E_CD))

Update: UPDATE FACT_B SET

MEETING_ID = (SELECT MEETING_ID FROM STG_B LEFT OUTER JOIN DIM_EMP ON (DIM_EMP.EMP_CD = STG_B.E_CD)),

MEETING_NBR = (SELECT MEETING_NBR FROM STG_B LEFT OUTER JOIN DIM_EMP ON (DIM_EMP.EMP_CD = STG_B.E_CD)),

MEETING_PURPOSE = (SELECT MEETING_PURPOSE FROM STG_B LEFT OUTER JOIN DIM_EMP ON (DIM_EMP.EMP_CD = STG_B.E_CD))

Re: tELTOutput update table Snowflake

manodwhb — Fri, 06 Mar 2020 06:44:29 GMT

@Nandita1s ,check the below link may help you.

https://help.talend.com/reader/wdWseodGFOv1mDEmWM3C_g/Q5RA4j4yprIWuu_vXLu4YA

Re: tELTOutput update table Snowflake

Nandita1s — Fri, 06 Mar 2020 07:37:22 GMT

Thanks manodwhb for sharing the link. I have already gone through all the ELT related examples, however, none of them explain the ELT update approach. There is one example on ELTOracleOutput which talks about update but that's also limited in the sense that a query has not been shown on how the update takes place in the database. Looking for further help, especially for the query part.

Re: tELTOutput update table Snowflake

phil974 — Mon, 21 Sep 2020 18:29:49 GMT

Same problem ! Do you find a solution ?

Re: tELTOutput update table Snowflake

phil974 — Mon, 21 Sep 2020 18:37:18 GMT

it seems to be an old bug... https://www.talendforge.org/forum/viewtopic.php?id=48109

Any news from Talend ?