Translating a complex SQL query into a tDBInput

[stephbzr]

Hello I have a rather long and complex SQL query and I am finding difficulties in changing its formatting so that it is "accepted" in a tDBInput.

Here is the query :

select distinct a.ID_FAM, a.ID_AY, a.ID_ORG, fp.ID_PROD 

into :idFam%, :idAy%, :idOrg1%, :IdProd$

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

where a.AY_SS = 'TALEND_VAR1'

and a.ID_FAM = fp.ID_FAM

and fp.ID_ORG = a.ID_ORG

and fp.ID_ORG = P.ID_ORG

and fp.ID_PROD = P.ID_PROD

and fp.ID_ORG = O.ID_ORG

and D.DCSE_NOM = 'NOEMIE'

and ((('NOEMIE' <> 'NOEMIE' and D.DCSE_PP = 0) and fp.ID_PROD = 'SANTE') or (('NOEMIE' = 'NOEMIE' or D.DCSE_PP = 1) and (fp.ID_PROD = O.ORG_PROD or P.PROD_PRESTATION_SANTE = 1)))

and fp.ID_FAM = a.ID_FAM

and ((a.AY_DATNAIS = decode('TALEND_VAR2', 1, to_date('TALEND_VAR2', 'dd/mm/yyyy'), a.AY_DATNAIS)) or (a.AY_DATNAIS2 = 'TALEND_VAR2'))

and a.AY_RNG = TALEND_VAR3

and exists (select 'a' from ADC ad

      where ad.ID_AY = a.ID_AY

      and ad.ID_PROD = fp.ID_PROD

      and nvl(ad.id_fprod, fp.id_fprod) = fp.id_fprod

      and to_date(TALEND_VAR4', 'DD/MM/RR') between ad.ADC_DATDEB and nvl( ad.ADC_DATRAD, sysdate+1))

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

Here is my start : 

select distinct a.ID_FAM, a.ID_AY, a.ID_ORG, fp.ID_PROD, a.AY_SS, a.AY_DATNAIS, a.AY_DATNAIS2, a.AY_RNG

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

where a.ID_FAM = fp.ID_FAM

and fp.ID_ORG = a.ID_ORG

and fp.ID_ORG = P.ID_ORG

and fp.ID_PROD = P.ID_PROD

and fp.ID_ORG = O.ID_ORG

and D.DCSE_NOM = 'NOEMIE'

and ((('NOEMIE' <> 'NOEMIE' and D.DCSE_PP = 0) and fp.ID_PROD = 'SANTE') or (('NOEMIE' = 'NOEMIE' or D.DCSE_PP = 1) and (fp.ID_PROD = O.ORG_PROD or P.PROD_PRESTATION_SANTE = 1)))

and fp.ID_FAM = a.ID_FAM

and exists (select 'a', from ADC ad

      where ad.ID_AY = a.ID_AY

      and ad.ID_PROD = fp.ID_PROD

      and nvl(ad.id_fprod, fp.id_fprod) = fp.id_fprod

      and to_date('TALEND_VAR4', 'DD/MM/RR') between ad.ADC_DATDEB and nvl( ad.ADC_DATRAD, sysdate+1))

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

In bold, these are the values coming from my tMap. But I don't know how to do this with TALEND_VAR4.

Thank you.

[Anonymous]

I am not sure why you are supplying values for your SELECT columns (a.AY_SS, a.AY_DATNAIS, a.AY_DATNAIS2, a.AY_RNG) from your tMap, as these are already in your tMap. Maybe I have misunderstood the formatting here?

But when it comes to your TALEND_VAR4 value in your WHERE clause, this is very simple once you have seen how it works. SQL queries in Talend components are essentially just String values. As such, you can set them up dynamically.

So, if I have the following query.....

"Select col1, col2, col3

From myTable

Where myTable.col4 = 'Hello'"

...and I want to make the value for col4 dynamic, I can do this.....

"Select col1, col2, col3

From myTable

Where myTable.col4 = '"+((String)globalMap.get("TALEND_VAR4"))+"'"

Now, my assumption here is that you are passing your TALEND_VAR4 variable to the globalMap and that it is a String.

[stephbzr]

Hello @Richard Hall​,

I usually put the values I am looking for in my query after the select. These are then in my schema, then I link to the values in the tMap. Here is an example :

The original query : 

select mr.ID_RISQ 

into id_risq

from MTDMT_RISQ mr, MCO mc

where mr.ID_ORG = TALEND_VAR1

and mr.ID_PROD = TALEND_VAR2

and mc.ID_MCO = mr.ID_MCO

and mc.MCO_CODE = TALEND_VAR3

and mr.ID_ACT = TALEND_VAR4

and mr.MTDMT_RISQ_PRESENCE_DENT = TALEND_VAR5;

With values this gives : 

 

How I translate this into Talend

 

Then I get the result I want.

This is just an example of how I usually do it and I would like to do the same for the "complex" query above. But I must admit that TALEND_VAR4 which is before BETWEEN, I wonder if it is possible to do this and how.

So concerning the dynamic value, I can't do the same as you, to put a global variable because I don't have only one value, but several. Well, I think so, but I have less experience with that. The values come from a file with several columns containing several thousand lines. I don't know if I can explain it well. 

[Anonymous]

OK, you need to set your table that is being used as your lookup to "reload at each row" in your tMap. Then join from your main source to that lookup using the globalMap key field that you should see appear when you set "reload at each row". What this does is take every row from your main source and pass whatever values you choose, from that row to a globalMap value. You can then use those globalMap values in your query for your lookup table. The lookup table will fire for every main row that is returned. So the process of assigning globalMap values and dynamically setting your SQL will be carried out for every row.

This is a mini tutorial I put together for a similar scenario. It shows everything you will need to do, apart from modifying your SQL. In this case, instead of using the values for SQL, I am using them for generating rows with a tJavaFlex.

https://community.talend.com/s/question/0D53p00007vCpvECAS/row-multiplication

[stephbzr]

Thank you @Richard Hall​  for the explanation, I will take the time to look at your tutorial before making a return.