Use variable in SQL Builder

[Anonymous]

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?

[vapukov]

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")+"'

[Anonymous]

Hi vapukov,

I tried it but I have an error. 

Capture.PNG

[vapukov]

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

[Anonymous]

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 

[vapukov]

perfect!

and what is the problem?

[Anonymous]

I have to sql queries now : 

1/

SELECT count(FileName) as numberCondition,
FileName

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

2/ 

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

[vapukov]

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

[Anonymous]

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'.

[vapukov]

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