topic Re: Compare between two queries result in Talend Studio

Compare between two queries result

Anonymous — Mon, 11 Mar 2019 11:55:59 GMT

Hello community !

My sql query is

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

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 and if it's true i want to delete rows for this FileName

akumar2301 — Mon, 11 Mar 2019 12:33:23 GMT

So you want

Step 1) you have another specifc file , you want to get record count using tFileRowCount

Step 2) Query to be executed in tOracleInput

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

Above will return one/more filename ,

---- you would like to Iterate through each file name using tFlowtoIterator

----------Then this file might exist in your server , you want to count number of line in this file using tFileRowCount

---------------using Run if check if rowcount of {file of SQL query} = rowcount of specific file(Step1)

then you would like to delete record in table using tOracleRow

Delete mvt_Ods FROM where Return_Mark!=' ' and PackingList_Type!='P' and fileName = {file of SQL query};

is my understanding correct ?

Anonymous — Mon, 11 Mar 2019 13:47:25 GMT

Yes

akumar2301 — Mon, 11 Mar 2019 14:51:19 GMT

I already explain the solution flow in my reply.

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

I don't have a file,

This is the same database