ELT merge and target operations

[lucaL]

Hi all,

We are developing an ELT process using oracle merge, where a column to be updated needs to have as the new value the greatest between the new value and the target current value.

In sql it would be something like:

MERGE INTO myTable target USING (

SELECT a, b FROM newTable) Source

ON (target.a = source.a)

WHEN MATCHED THEN UPDATE SET

target.b = greatest(source.b, target.b)

WHEN NOT MATCHED THEN INSERT

(target.a, target.b)

VALUES (source.a, source.b)

Any way to make the same using the tELTOracleOutput? 🤔 

Thanks!

[Dave_Simo]

Hello @lucaL 

From my experience, I don't think you can perform both operations (insert, update) simultaneously in a tELTOracleOutput.

But you can do it with a tSQLTemplateMerge. You can use this component's configuration as a guide, as shown below :

Source : https://help.qlik.com/talend/fr-FR/components/8.0/sqltemplate/tsqltemplatemerge-tsqltemplatecommit-t...

Best Regards

[lucaL]

Hi,

thanks for the repoly. This works almost as the TELTOracleOutput whene you choose the merge operation. The problem is still the same, I think, you cannot use the target table values on the update statement, since talend use just the "target.column = source.column" logic... It seems there is no way to define a different logic, as would be "target.colum = target.column + source.column"... Isn't it?

Regards 🙂