topic Re: Use variable in SQL Builder in Talend Studio

Use variable in SQL Builder

Anonymous — Mon, 11 Mar 2019 10:12:09 GMT

Hello community !

I want to retrieve a field of input 1 and use it in the SQL query of input 2,
With each execution, the result is null whereas the query works if I use a very precise field.

Do you have ideas?

Re: Use variable in SQL Builder

vapukov — Mon, 11 Mar 2019 10:52:50 GMT

hi,

you must use + for concatenate strings, also if you not set up name you must add flow name to variable name

LIKE '"+(String)globalMap.get("row1.FileName")+"'

Re: Use variable in SQL Builder

Anonymous — Mon, 11 Mar 2019 11:04:54 GMT

Hi vapukov,

I tried it but I have an error.

Capture.PNG

Re: Use variable in SQL Builder

vapukov — Mon, 11 Mar 2019 11:07:09 GMT

variables - do not work in SQL Builder

sorry I not guess - what you are looking for
when you run query in Bulider - variables is not ready, but form which I provide for you will work in the Job

Re: Use variable in SQL Builder

Anonymous — Mon, 11 Mar 2019 11:41:48 GMT

I changed my sql query to

SELECT FileName
FROM mvt_Ods
where Return_Mark!=' ' and PackingList_Type!='P'

Now I want to use the result in order to :

If the number of lines in the result equals the number of lines of a specific FileName => result : delete from table

to summarize, I want to see if the number of rows Return_Mark!=' ' and PackingList_Type!='P' for each FileName

is equal to the number of rows of each FileName

Re: Use variable in SQL Builder

vapukov — Mon, 11 Mar 2019 12:03:08 GMT

perfect!

and what is the problem?

Re: Use variable in SQL Builder

Anonymous — Mon, 11 Mar 2019 14:41:23 GMT

I have to sql queries now :

SELECT count(FileName) as numberCondition,
FileName

FROM mvt_Ods
where Return_Mark!=' ' and PackingList_Type!='P'
group by FileName

SELECT count(FileName) as number,
FileName
FROM mvt_Ods
group by FileName

I don't know how to match between the two queries in order to :

If the number of lines in the result equals the number of lines of a specific FileName and the FileName1 is equal to the FileName2 => result : delete from table

And I can only catch the number in the query result i can't catch the FileName of that number

Re: Use variable in SQL Builder

vapukov — Mon, 11 Mar 2019 21:12:32 GMT

execute both queries and store each result into variable then use tRunIf trigger

Re: Use variable in SQL Builder

Anonymous — Tue, 12 Mar 2019 08:09:27 GMT

Hello vapukov,

My need changed so :

I have a table wich contains Return_Mark, PackingList_Type and FileName and other columns.

For example the result of Return_Mark!=' ' && PackingList_Type !='P' is

for FileName ='A' : 100 rows

for FileName ='B' : 200 rows

And the result of distinct count(FileName) is

for FileName ='A' : 100 rows

for FileName ='B' : 300 rows

FileName='A' have the same number of rows for the two queries so I have create a new table wich contain the other FileName(s) and for this example it's FileName ='B'.

Re: Use variable in SQL Builder

vapukov — Tue, 12 Mar 2019 12:54:30 GMT

needs could be changed, the method still the same 🙂

count -> compare -> choose action -> do it

if you more familar with SQL - do simple - load all in temp table, and run SQL queries with tSQLRow, sometime it make Job much simple