How to get the values dynamically in tOracleInput query

[Anonymous]

I wanted to apply filter to source query with different filter condition at every run
so, I need to take the values in where condition using a file( through context variable)
Example:
I have written the below in
toracleInput
"Select * from table1" +context.filename
I have given the filter in the file
"where col3 in (val1,val2, val3)"
It is not taking the filter condition from file
If i try one other ways it is throwing error.
Please let me know the appropriate way to write dynamic queries in talend

[Anonymous]

Missed the Snapshot
Also The query I am using is same
"select  ' " + ((String)globalMap.get("Tns")) + " ' as outs from dual".
Does than mean ,tFlowToIterate is sending 407 rows to tOracleInput but it can't read this due to some reson. Am I missing something?

[vapukov]

Amit, may be time to stop, sleep and look for problem with fresh eyes?

Selecting from the DUAL Table

DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table. Please refer to "SQL Functions" for many examples of selecting a constant value from DUAL.

so any string other  than

SELECT dummy FROM dual,

 
must return error
but not:

"select  ' " + ((String)globalMap.get("Tns")) + " ' as outs from dual"

because it is - SELECT SINGLE_STATIC_TEXT  FROM dual, because ((String)globalMap.get("Tns") - You are include in 'single quotes' making from them text
Check - SELECT 'This is fucking test' FROM dual ... and yes - it work

For what all above?
it mean - You must go from begin and check Your variables (row) after each step, than You understand - where You miss them!
In Your case:

disable all after "assign_unmutched_Tns" and connect it to LogRow - look for result, publish it there
return all back - but now disable tOracleInput, instead add element for print - ((String)globalMap.get("Tns") 

DO not make a lot of movements, relax - and search

[Anonymous]

You need to show us the configuration of your tFlowToIterate component and your tOracleOutput3 (screenshots of how they are configured). I don't think you are assigning a value properly in the tFlowToIterate. It could also be that the value you are trying to assign is null. You can check this by placing a tLogRow in place of the tFlowToIterate as a test.
Your tFlowToIterate is iterating 407 times. This means your tOracleOutput3 is being fired 407 times. 
If you give us some screenshots of your configuration (and prove that some value is being returned by the "assign_unmated_tns" component), we should be able to help. 
When you have more Talend experience this sort of problem will be easy to fix first hand. Unfortunately doing it remotely is a little tricky without being shown the detail we need. 
As Vapukov says, grab some sleep and post some screenshots for us to track down your issue in the morning. 

[Anonymous]

You need to show us the configuration of your tFlowToIterate component and your tOracleOutput3 (screenshots of how they are configured). I don't think you are assigning a value properly in the tFlowToIterate. It could also be that the value you are trying to assign is null. You can check this by placing a tLogRow in place of the tFlowToIterate as a test.

Please see screen shots ..
1. )  Job's flow
2. )  tFlowToIterate configuration

Your tFlowToIterate is iterating 407 times. This means your tOracleOutput3 is being fired 407 times. 
If you give us some screenshots of your configuration (and prove that some value is being returned by the "assign_unmated_tns" component), we should be able to help. 

I added one component assigned_unmatched_Tns before tFlowIterate. In this excel file , I got all 407 Tns. So flow is perfect before  tFlowToIterate.

When you have more Talend experience this sort of problem will be easy to fix first hand. Unfortunately doing it remotely is a little tricky without being shown the detail we need. 

I agree , I am struck before I don't have enough experience in Talend thats why I am having problems in fixing small issues too.

As Vapukov says, grab some sleep and post some screenshots for us to track down your issue in the morning. 

Took good nap and ready to hit this job again . Thanks for your concern.

[Anonymous]

Hi Vapukov ,
Thanks for keeping an eye on this issue.

because it is - SELECT SINGLE_STATIC_TEXT  FROM dual, because ((String)globalMap.get("Tns") - You are include in 'single quotes' making from them text

Here I want to say , this is not what happening here.
If the query in DB is select  ' " + ((String)globalMap.get("Tns")) + " ' as outs from dual;
then it will return  " + ((String)globalMap.get("Tns")) + "  as output each time it is hit. so instead of getting null values I should get value like ...
|                    tLog_Row                        |
|-----------------------------------------
|" + ((String)globalMap.get("Tns")) + "       |
|" + ((String)globalMap.get("Tns")) + "       |
|" + ((String)globalMap.get("Tns")) + "       |
|" + ((String)globalMap.get("Tns")) + "       |
|" + ((String)globalMap.get("Tns")) + "       |
|.... 407 times                                       |
------------------------------------------
In Talend , when we are sending any context variable , We are surround them by ' " + and + " ' . So it can understand that this in not a part of query but a bind variable.

I am sure the problem is in either of two place.
1) Am I sending variables one by one properly to tOracleInput using tFlowToIterate.
2) Am I reading the Variable properly in tOracleInput.

[vapukov]

Amir,
Your problem - You are hurry and do not want read - what we ask (recommend) You 🙂
attached - same as You try todo, but as You can see - all work, so ...
         
In Your case - run Job in Debug mode and look - where You miss information, or as I recommend before - check tLogRow - BEFORE tFlowToIterate - what are You store in variable? if it NULL - all feature correct
Just add - always start error search from SOURCE (LEFT - to - RIGHT) - and step by step You will be sure - all behind is proper!

[vapukov]

and little about feature steps
may be You not understand in this job, but Talend (as many other) - must have defined schema
if for example - You expect run dynamic SQL and have dynamic result - it not true
      
You result will be unexpected again:

so, You must define - full schema before:
   
with subscription version, You can try to use dynamic schema, but also could be surprised:
  

[Anonymous]

Eureka Eureka Eureka !!!!

Its Done finally.
Thanks a lot VOPOKOV, CTERENZI and RHALL_2.0

My flow was correct , but in query I had to use
   ' "+context.output_unmatched+" '
   instead of
    ' " + globalMap.get("TNs1")) + " ' .
   
Thanks guys , because of your help , I manage to get this done.
Only thing it is running query for each Tns.
so if I have 900 Tns, It will run for 900 times.
But its fine. I will find some idea to fix that. This time all by myself without any help so I can learn more.
Finally Thank You All.

[Velu_R]

Any answer for this question?