[resolved] tOracleInput subquery

[Anonymous]

Hi there,
I am using a tOracleInput component with a query to compare file names in two tables to determine which files have not been loaded yet.  However my query is not returning the expected results.  It works fine when i use the query in the database but when I use the query in Talend it only returns all records in the first table(DISTR_SALES_FILE_TMP).  My query has a not exists subquery in it.  Is there a limitation with subqueries in Talend 6.0 or thoughts on what else it might be?
Any assistance would be much appreciated
Here is query i am using in the tOracleInput component
select FILE_NAME from DISTR_SALES_FILE_TMP
where not exists (select file_name from DISTR_SALES_TRACE_STG_HIST
where upper(DISTR_SALES_FILE_TMP.FILE_NAME)= upper(DISTR_SALES_TRACE_STG_HIST.FILE_NAME))

[JR1]

No, there is no limitation with subqueries. In fact I have used subqueries numerous times and they work as expected (using Talend 5.6.1 and 6.1.1 - not 6.0.X I have to point out). What do you mean exactly when you say "It works fine when i use the query in the database"? Do you run the statement in SQL*Developer or SQL*Plus? Are you using the same connection and exactly the same query in Talend and your SQL tool? A screenshot of the statement you send "to the database" and one of the configured tOracleInput component may be useful.

[Anonymous]

Yes I was referring to the sql statement against the database using Oracle toad and the sql statement within the tOracleInput component.  In using the same sql statment after having the application closed, the issue no longer exists and is returning what I expected.
Not sure if it needed to be cached but no longer having an issue.
Thanks for your assistance!  

[Anonymous]

I too tried to use a where condition in the tOracleInput and the query was not working.

[Anonymous]

Hi there.... I have the same problem.   I cannot run a subquery with two tables in talend.  Did anyone find a way to do a 'not exists' subquery in talend?
Please help.
Thank you!  Jean

[vapukov]

what the problem? the talent not have limitations for SQL queries. Could You attach Your query?