getting no data when using (tFlowToIterate) variable in source input

[sekertu]

Hi,

I have a job that firstly makes union and keeps the result data as a variable(as using tFlowToIterate) after this part is OK,

I joined tables and again keep the result column as a variable.

Last part is using this variable in a source sql. But the result is 0. (no rows loaded)

I added the screenshot below. I guess last part doesn't take the variable result correctly.

I used ('"+((Integer)globalMap.get("key.value"))+"') in where clause in input table.

Would you please help me about this issue?

Thanks a lot.

Tugba

[TRF]

If the row just before the tFlowToIterate component is called row1 and field is code and you've used the default settings for the component, the global variable name is "row1.code".
Beware to respect this rule and it should work.

[sekertu]

Hi,

Thank you for your answer.

But I still have the problem.

My column datatype is integer. I am using the components like that.

tDBInput-main-Tmap-main-FlowToIterate-Iterate-tDBInput2

I am using the filter like WHERE `table`.`column` IN ('"+((Integer)globalMap.get("row.column"))"') in tDBInput2

It works but doesn't load any data to target table.

Then I tried to use the filter like that WHERE `table`.`column` = '"+((Integer)globalMap.get("row.column"))"' but still no data(without error)

when I get rid of the filter it loads data to target table, after adding the filter again 0 data loads.

I think the problem is about variable.

@TRF do you have any different idea about it?

thank you very much in advance.

Tugba

[cterenzi]

You need to confirm that you're referencing the variable correctly.  As TRF pointed out, you reference the variable by combining the name of the flow leading into tFlowToIterate with the column name.  If you make copy of the job and connect tFlowToIterate to a tJava component, you can test that you have the right syntax by printing the variable to the console (or printing the entire SQL statement if you want to take it a step further).  Once you have the right variable syntax, your query should work.

[sekertu]

Hi, thanks for the answer. I think I am using the right syntax but it still returns null. May be I am wrong about the component.

I want to tell you my scenario. Is is possible to do or which component should I use for it?

I have a sub query(that returns id row) and I want to use it's result in dbInput source sql dynamically.

So my source dbInput sql will be like that. select id,x,y,z... from table where id in (variable).

I would be happy if you help me about that issue.

Thank you 

Tugba

[sekertu]

Hi all,

Unfortunately, I couldn't solve my problem.

I used tSetGlobalVar in my job. As I mentioned before I am trying to use the value of union in a new sql input source.

For test it I added tJavaRow. It takes the i_ticket values but when I try to use it in the next sql input it returns 0.

(You can see in the attachments).

If you have an idea or solution, please let me know.

Thank you.

Tugba

5.PNG
4.PNG
3.PNG
2.PNG
1.PNG

[Anonymous]

Hi
1. if you use oncomponentok connector after tSetGlobalVar to link to the next subjob, it will not iterate each row of the first query, the global variable will has the last value, and the next subjob will be executed only one time.
2. you can simply test the value of global variable on a tJava after tSetGlobalVar , eg:
...tSetGlobalVar --oncomponentok--tJava
on tJava:
System.out.println((Integer)globalMap.get("ticket_miss"));
3. the key you defined on tSetGlobalVar is "ticket_miss", but you are using "i_ticket" in the query.

4. If you use tFlowToIterate to iterate each input row, the expression used to get the value of global variable is:

(Integer)globalMap.get("rowName.columnName")

Regards
Shong

[sekertu]

hello @shong ,

Thank you for your advice. I corrected my mistakes.

I decided to use tFlowtoIterate like at the beginning. But my data is big (above 250000) so tFlowtoIterate is very slow.

I want to use the result of a sql in filter as a subselect.

This is what I need.

select a,b,c,d, from table_x

where a in (select a from table_y)

table_x and table_y come from different connections.

Is there a different way to do it?

Thanks in advance.

Tugba