SQL Query

MZein — Sat, 16 Nov 2024 03:31:11 GMT

Hi Friends

I have the below query it work inside DBeaver but in Talend (component tOraclerowInput) it thows the exception table out of index

query is

MERGE INTO PROTEE.T_HISTO_PRODUCTION_AGENT CIBLE
USING (SELECT RUGS.RO_ORGANISME_ID RUG_SOURCE, RUGC.RO_ORGANISME_ID RUG_CIBLE,
RAGS.RA_AGENT_ID,
HPA.RO_SOCIETE,
THPA_NUM_MISSION_PROD,
THPA_EXERCICE,
THPA_MNT_PRODUCTION,
THPA_NBR_VAC_REALISEES
FROM PROTEE.T_HISTO_PRODUCTION_AGENT HPA, PROTEE.R_ORGANISME RUGS, PROTEE.R_ORGANISME RUGC, PROTEE.R_AGENT RAGS
WHERE RUGS.RO_TE_ORGANISME = 'U' AND RUGS.RO_CODE_ORGANISME = 'AMOB1' -- UG source
AND RUGC.RO_TYPE_ORGANISME = 'U' AND RUGC.RO_CODE_ORGANISME = 'COOL1' -- UG cible
AND RAGS.RO_SOCIETE =5242 AND RAGS.RA_MATRICULE = 821525
AND RO_UG = RUGS.RO_ORGANISME_ID
AND HPA.RA_AGENT_ID = RAGS.RA_AGENT_ID
AND THPA_EXERCICE < to_char(sysdate, 'YYYY') )
SOURCE ON ( CIBLE.RO_UG = SOURCE.RUG_CIBLE
AND CIBLE.RA_AGENT_ID = SOURCE.RA_AGENT_ID
AND CIBLE.RO_SOCIETE = SOURCE.RO_SOCIETE
AND CIBLE.THPA_NUM_MISSION_PROD = SOURCE.THPA_NUM_MISSION_PROD
AND CIBLE.THPA_EXERCICE = SOURCE.THPA_EXERCICE)
WHEN MATCHED
THEN
UPDATE SET
CIBLE.THPA_MNT_PRODUCTION = CIBLE.THPA_MNT_PRODUCTION + SOURCE.THPA_MNT_PRODUCTION,
CIBLE.THPA_NBR_VAC_REALISEES = CIBLE.THPA_NBR_VAC_REALISEES + SOURCE.THPA_NBR_VAC_REALISEES
WHEN NOT MATCHED
THEN
INSERT (CIBLE.RO_UG,
CIBLE.RA_AGENT_ID,
CIBLE.RO_SOCIETE,
CIBLE.THPA_NUM_MISSION_PROD,
CIBLE.THPA_EXERCICE,
CIBLE.THPA_MNT_PRODUCTION,
CIBLE.THPA_NBR_VAC_REALISEES)
VALUES (SOURCE.RUG_CIBLE,
SOURCE.RA_AGENT_ID,
SOURCE.RO_SOCIETE,
SOURCE.THPA_NUM_MISSION_PROD,
SOURCE.THPA_EXERCICE,
SOURCE.THPA_MNT_PRODUCTION,
SOURCE.THPA_NBR_VAC_REALISEES)

Best regards

Re: SQL Query

vapukov — Wed, 29 Jan 2020 00:57:47 GMT

which exactly component do you use? (there no component tOraclerowInput, only tOracleRow or tOracleInput)
what exactly error code?

regards, Vlad

Re: SQL Query

manodwhb — Wed, 29 Jan 2020 10:36:35 GMT

@MZein , you need to use tDBRow component to execute that query .

Re: SQL Query

MZein — Wed, 05 Feb 2020 08:29:33 GMT

Hi all

Thank you very much for your replies and I highly appreciate your interaction. I found the error. It is raised because i use tConnection component and I did some processing (read from csv file check data ) then I use the component tOracleRow. but When I use tconnection then tOracleRow the job run smoothly without error

Regards

topic Re: SQL Query in Talend Studio

SQL Query

Re: SQL Query

Re: SQL Query

Re: SQL Query