tELTOracleMap : some suggestions and pb with complex queries
Hi, Sorry for disturbing you again. It's just that I'm discovering and evaluating Talend trying to reproduce some existing procedures, seeing how can I do it, how user-friendly it is, the limitations and the performances. I'm currently encountering some difficulties with the tOracleMap component, with some kind of SQL queries. I have some suggestions too.
1. It is just a notice : we can't assign an alias to an output table in "tELTOracleMAP", so you are obliged to specify the full name of the table in the "where clauses" for updating of deleting in the "tELTOracleOutput" component, if necessary to have one or more (like "EXISTS(SELECT 1 FROM ESSAI_TMPC_SUPP_METI_UPDATE A WHERE TO_CHAR(A.FOU_CDFO) = ESSAI_REF_VEN_VENDOR.VEN_REFERENCE)".
2. About multi inputs/ multi outputs : See the example of the first attached screenshot Talend automatically adds each input table name into the "from" part of the query, whereas each input table may not be necessary for one specific output. It is not very useful, and some problems can occur : Imagine I have to update one table, which should be in input due to a needed conditions between it and another table (see my second capture). You'll obtain a query like that : "UPDATE ESSAI_REF_VEN_VENDOR SET (VEN_NAME,CREATION_DATE,COMMENTS) = (SELECT SUBSTR(SAS.FOU_NM,1,60), SYSDATE, SAS.FOU_TTCOM1 FROM ESSAI_REF_VEN_VENDOR , ESSAI_TMPC_SUPP_METI_UPDATE SAS WHERE ESSAI_REF_VEN_VENDOR.VEN_REFERENCE = TO_CHAR(SAS.FOU_CDFO)) WHERE EXISTS(SELECT 1 FROM ESSAI_TMPC_SUPP_METI_UPDATE A WHERE TO_CHAR(A.FOU_CDFO) = ESSAI_REF_VEN_VENDOR.VEN_REFERENCE)" ... which won't work : "ORA-01427: single-row subquery returns more than one row" In fact, ESSAI_REF_VEN_VENDOR mustn't appear into the "SELECT". >> One solution is not to put the table which has to be updated in entry, and write the "where" condition (for the implicit join) as a filter row, like in my third capture. However, that means i'll have to use another "tOracleMap" component for the other queries that will need my updated table in entry. Maybe you'll tell me this component is not created for such a multi inputs/outputs use... Anyway I want to know it .
3. Due to this limitation, why not allow the user to modify the generated SQL query ? (maybe with a button permitting the desactivation the "real-time" generation for the output).
4. How could I perform a specific SQL query for an insertion or a deletion, with Talend ? I mean, without creating a new little procedure in my Oracle DB that I could run with Talend (a query on several tables, like this one above) ?