Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
XKB
Contributor
Contributor

tELTOracleOutput MERGE : option Use Merge Insert

Context : very basic ELT mapping to merge from source table into target table.
It looks like the tELTOracleOutput "Use Merge Insert" - that should allow to pick which target columns should actually be inserted - is not working properly.
Out of many available schema column, I check those I do want to insert but the final generated statement yields :

MERGE INTO TARGET (...)
    WHEN NOT MATCHED THEN INSERT (COL1,COL2,,,,,,,,,,,,,,,,,,,,,,,,,,,,)
    VALUES (source.COL1,source.COL2,,,,,,,,,,,,,,,,,,,,,,,,,,,,)

...which in turns leads at run-time to :
Exception in component tELTOracleOutput_1 java.sql.SQLSyntaxErrorException: ORA-00947: nombre de valeurs insuffisant

Conversely, if I actually reduce my target schema to the insertable columns (which is extremely unpractical if I want to incrementally develop my merge mapping), it yields the proper MERGE statement.
Is there something I haven't understood from the component's option ?
Thanks
Labels (3)
6 Replies
Anonymous
Not applicable

Hi,
Have you already checked the component reference with related scenario?
https://help.talend.com/search/all?query=tELTOracleOutput&content-lang=en
Best regards
Sabrina
XKB
Contributor
Contributor
Author

Hello Sabrina,
Of course I started by reading the fine manual : it says "Select the check box corresponding to the name of the column you want to insert"
...and it quite makes sense to me.
It just doesn't work in the end because if I do not select all columns, it still generates a bunch of commas for those unselected ones.
Only workaround : suppressing columns from target schema (which is pretty unpractical although it works)
I would expect, whilst keeping all my target schema columns, that those unchecked from Insert list would just not appear in the insert part of the statement (isn't it what is implied by the gui ?).
Thanks
XKB
Contributor
Contributor
Author

Hi,
Little bump on this ; I'm still encountering the problem and generated ELT Oracle code is syntaxically incorrect (when insert check boxes aren't all selected)
Haven't found any similar case in Jira.
Should I open one ? (or first ask to support ?)
Thanks
Anonymous
Not applicable

I just encountered the same issue. Did you find a resolution?
Anonymous
Not applicable

I believe I found the issue in this block of code:
		if(!useMergeInsert)
useInsertWhere = false;
if (useMergeInsert) {
boolean isfirstInsertWhere = true;
String keyColumnName=null;
for(Map<String, String> keyColumn:insertColumns){
if (isfirstInsertWhere) {
isfirstInsertWhere = false;
}else {
mergeInsertName.append(",");
mergeInsertValue.append(",");

}
if (keyColumn.get("INSERT").equals("true")) {
keyColumnName=keyColumn.get("SCHEMA_COLUMN");
keyColumnName = new Column(metadata.getColumn(keyColumnName)).getName();
mergeInsertName.append(keyColumnName);
mergeInsertValue.append("source." + keyColumnName);
}
}
%>
mergeQuery += " WHEN NOT MATCHED THEN INSERT ( <%=mergeInsertName%>) VALUES ( <%=mergeInsertValue%>) ";
<%
}

Commas are being appended to the insert variables before checking whether or not INSERT is true.
Anonymous
Not applicable

Great job jesseg!