topic Re: How to use tELTOracleMap to build SQL statement with correct ORDER BY in Talend Studio

How to use tELTOracleMap to build SQL statement with correct ORDER BY

Anonymous — Sat, 16 Nov 2024 12:31:28 GMT

I am trying to build a job to execute a SQL statement like:
INSERT into TABLE (col1, col2, ...) SELECT ... from ... WHERE ... ORDER BY col1
The component flow I create is basically as:
tELTOracleInput ---> tELTOracleMap ----> tELTOracleOutput
If I add the ORDER BY clause by defining Additional Clauses inside the tELTOracleMap component, the SQL statement generated is incorrect. The statement is:

INSERT into TABLE (col1, col2, ...) ( SELECT ... from ... WHERE ... ORDER BY col1 )

This will trigger SQL Error: ORA-00907: missing right parenthesis
The correct SQL statement should be:
INSERT into TABLE (col1, col2, ...) ( SELECT ... from ... WHERE ... ) ORDER BY col1
The ORDER BY clause needs to be outside the SELECT clause. How do I use ELT components to generate correct SQL?

Re: How to use tELTOracleMap to build SQL statement with correct ORDER BY

Anonymous — Tue, 06 Dec 2011 02:31:47 GMT

Dear user
It was a bug for tELTOraclexxx component now, the select statement is always enclosed by the parenthesis. Please report a bug on our JIRA platform.
http://jira.talendforge.org/secure/Dashboard.jspa
Right now, you can use tOracleRow to execute this SQL statement:
"INSERT into TABLE (col1, col2, ...) ( SELECT ... from ... WHERE ... ) ORDER BY col1"
Best regards
Shong

Re: How to use tELTOracleMap to build SQL statement with correct ORDER BY

Anonymous — Wed, 07 Dec 2011 02:34:14 GMT

Thank you, Shong. I tried tOracleRow and it works. Although I can execute whatever complex SQL statement with tOracleRow, but it lose the beauty to define data flow visually. Hope this bug can be fixed soon.
I have another question about tELTOracleMap in general. My goal is to join tables, filtering by some conditions, order by some fields, and then pipe the output to a CSV file. I discover that I cannot pipe tELTOracleMap to a tFileOutputDelimited directly. Instead, I need to feed output to an intermediate table first, and create another subjob to extract from intermediate table to a tFileOutputDelimited. Under normal JAVA JDBC environment, I would write code to iterate rows fetched and write them directly into a CSV file. I wonder how can this be accomplished by Talend without the need to create a intermediate table because that is a waste of disk space and perhaps runs slower?
I also tried a separate job to use tMap + tSortRow + tFileOutputDelimited to mimic the same functionality. It works, but is slower than the ELT SQL version.
Thank you for prompt response.
-Alice Chang-

Re: How to use tELTOracleMap to build SQL statement with correct ORDER BY

Anonymous — Wed, 07 Dec 2011 02:44:19 GMT

Hi Alice
I think you have some misunderstand on ELT components, the SQL is in fact executed on database engine, not Talend program, and it has no output, the ELT component provider a GUI operation for you and it generate the SQL script, this script will be executed on database engine, so the job must be table to table with ELT components.
Best regards
Shong

Re: How to use tELTOracleMap to build SQL statement with correct ORDER BY

Anonymous — Wed, 07 Dec 2011 03:41:42 GMT

So, if my goal is to join two ORACLE tables, and then write the result sets into a CSV file. What's the best practice to use Talend components to accomplish this task?
A simple example of java code with JDBC would be something like:
.... // write code to open an output file
Statement st = conn.createStatement();
ResultSet rs = st. executeQuery(query); // a select query to join two tables, sorted by key id
while (rs.next())
{
int c1 = rs.getInt("col1");
String c2 = rs.getString("col2");
....... // get data of more columns
// then write this row into output file
}

Re: How to use tELTOracleMap to build SQL statement with correct ORDER BY

Anonymous — Thu, 08 Dec 2011 01:59:15 GMT

Hi
A simple way is to use two tOracleInput components to read these two tables and do a join (inner join or left outer join) on tMap, the job design looks like:
tOracleInput_1---main---tMap---main---tFileOutputDelimited
|
lookup
|
tOracleInput_2
tOracleInput_1: select some columns from table1
tOracleInput_2: select some columns from table2
on tMap, do a inner join or left outer join base on table1.columnName=table2.columnName
Please read the user component documentation and learn tMap.

Best regards
Shong