Adapt a query to a tDBInput

[stephbzr]

Hello,

I need to perform a database search from my tMap data (Lookup connector), but due to the syntax of my query, I don't know how to write it in my tDBInput. Here it is (in bold are the tMap data) :

select distinct(ay.id_fam)

from AY ay, FPROD fp, PROD P, ORG O, DCSE D

where fp.ID_FAM = AY.ID_FAM

and fp.ID_ORG = P.ID_ORG

and fp.ID_PROD = P.ID_PROD

and fp.ID_ORG = O.ID_ORG

and AY.AY_SS = '2721099132075'

and fp.ID_ORG = 1

and ay.AY_RNG = 1

and ay.AY_DATNAIS = '26/10/72'

and fp.ID_PROD = 'SANTE'

and D.DCSE_NOM = 'NOEMIE'

and to_date('26/04/22', 'DD/MM/RR') between fp.FP_DATADH and nvl(fp.FP_DATRAD, sysdate+1);

So there are 6 columns that need to come from tMap. It's especially the last row that I have a problem with, knowing that I have to keep it because my exercise is to check if the query returns a result with or without the last row. So I can't delete it. Also knowing that my dataset contains several thousands of rows, which can affect the performance depending on the solution you can propose me.

 

Here are the 6 columns concerned by the query (knowing that there are more than 100 columns in total because these data are integrated in a large table). This will help you to better understand my query.

Thanks

[Anonymous]

Hi

How many rows of the lookup data? the last row you mentioned is the last row of lookup data?

[stephbzr]

Hello @Shicong Hong​ 

I added more information in the post. Regarding the last line I'm talking about, it's the one in the SQL query : and to_date('26/04/22', 'DD/MM/RR') between fp.FP_DATADH and nvl(fp.FP_DATRAD, sysdate+1);).

I have to keep it to have only one line in return of my query. 

This is just to clarify, but the main problem is that I don't know how to format this in tDBInput with the tMap input data.

[Anonymous]

You need to iterate each row and run the query for each row, the job looks like:

...tMap--out1->tFlowToIterate-iterate-->tDBInput--main-->tLogRow

on tDBInput, write the query as:

"select distinct(ay.id_fam)

from AY ay, FPROD fp, PROD P, ORG O, DCSE D

where fp.ID_FAM = AY.ID_FAM

and fp.ID_ORG = P.ID_ORG

and fp.ID_PROD = P.ID_PROD

and fp.ID_ORG = O.ID_ORG

and AY.AY_SS = '"+out1.column1+"'

and fp.ID_ORG ="+ out1.column2+"

and ay.AY_RNG ="+ out1.column3+"

and ay.AY_DATNAIS = '"+out1.column4+"'

and fp.ID_PROD = '"+out1.column5+"'

and D.DCSE_NOM = 'NOEMIE'

and to_date('"+out1.column6', 'DD/MM/RR') between fp.FP_DATADH and nvl(fp.FP_DATRAD, sysdate+1)"

can you try and let me know if it works!

[stephbzr]

Thank you @Shicong Hong​ 

I'm going to try this idea quietly this weekend and I'll let you know. In my opinion, it might take a long time, but if there is only this solution... 🙂