How to output a query result ? -- SELECT DISTINCT column_name from table

[Anonymous]

In the past, I used to output a SQL result to global variables using:

tDBInput --- Main----  tMap ----Main------ tSetGlobalVar

I knew how many rows the sql with return, and I created the same amount of global var after tMap.

But this time, I am not sure the rows of sql statement will return. I only know, it could be null, or one row or 2 rows. In this case, how should I output the possible results?

Thanks!

TM

[Anonymous]

Replace the tSetGlobalVar with a tFlowToIterate component. This will allow you to iterate over the rows (no matter how many) and will assign each column to a globalMap value using the key {row_name}.{column_name}. So if your row is "row1" and your column is called "name", the globalMap key is "row1.name". 

[Anonymous]

Thanks for your reply!

Now I have a way to iterate over the distinct column_name, is there a way also to COUNT the distinct colume_name? ( without writing another SELECT COUNT (DISTINCT column_name) ... sql)

I initially had the SELECT COUNT (DISTINCT...) query in my tDBInput, but I want both the count and the 

distinct value : when the count < 3 , whichs mean some source data is missing, so I am going to send out an email and then fail the job; In the email, I want to list the distinct column_name, so I will know what data is missing.

Thanks!

[Anonymous]

You can do this with the tFlowToIterate's "Number of line" or "Current iteration of line" globalMap values. If you look at the left hand side of your Talend Studio, you will see a window with a tab heading called "outline". If that doesn't have the focus, click on it. In there you will see all of the components you are using in your job. If you expand those, you will see all of the globalMap variables that those components populate. If you create a tJava in your Studio, you can drag from the variables to the tJava code window and see how they need to be written to be used. You can use these variables anywhere you like.

As an example, the two variables I mentioned above will look like below if your tFlowToIterate component is called tFlowToIterate_1...

((Integer)globalMap.get("tFlowToIterate_1_NB_LINE"))((Integer)globalMap.get("tFlowToIterate_1_CURRENT_ITERATION"))

[Anonymous]

Thank you rhall! I am getting closer to what I want: 

  

I coded the email body like below:

"INVOICE_CHECK_BEGIN_DATE is: " + (String)globalMap.get("myVariable5") + "; "+
"INVOICE_CHECK_END_DATE is: " + (String)globalMap.get("myVariable6") + "; " +
"There are " + ((Integer)globalMap.get("tFlowToIterate_2_NB_LINE")) + " distinct INVOICE_DATE available between these two invoice check dates:" + ((String)globalMap.get("row7.INVOICE_DATE"))

How should I modify my job so I could list all the distinct INVOICE_DATE in my email? I am working on this now.

[Anonymous]

You need to concatenate your dates in something like a tJava component linked to the tFlowToIterate using an iterate link and not a RunIf. Concatenate the values in another globalMap. Then in a subsequent subjob, test to see if the globalMap you have populated has a value, if so then send the mail.

[Anonymous]

 

Can you please help me with the code in tJava to concatenate the dates? Thanks a lot!

[Anonymous]

Lets say that your column you want to concatenate is called "myColumn". Your code in a tJava would look like this. By the way, you can get rid of the tFixedFlowInput and replace it with a tJava.....

String myConcatenatedFields = "";
if(globalMap.get("myConcatenatedFields")!=null){
     myConcatenatedFields = ((String)globalMap.get("myConcatenatedFields"));
}

myConcatenatedFields = myConcatenatedFields + "; "+((String)globalMap.get("copyOfout1.myColumn"));

globalMap.put("myConcatenatedFields",myConcatenatedFields);

Essentially I am first creating an empty String object. I then assign it the value stored by the globalMap variable "myConcatenatedFields" (made up) if this is anything but null. The first time it is used it will be null. I then append that value with a separator ( and then add the current value of "myColumn". This is then assigned back to the "myConcatenatedFields" globalMap variable.

The code may not work perfectly as I have written this free hand.

[Anonymous]

Thank rhall for all your quick and detailed replies!

Unfortunately I am still learning Java and could not make the tJava component work. 

But I found an alternative approach for my need.

 

I will still try to make the original approach work.