topic Re: Translating a complex SQL query into a tDBInput in Talend Studio

Translating a complex SQL query into a tDBInput

stephbzr — Fri, 15 Nov 2024 22:18:59 GMT

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.

Re: Translating a complex SQL query into a tDBInput

Anonymous — Wed, 30 Nov 2022 11:14:58 GMT

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.

Re: Translating a complex SQL query into a tDBInput

stephbzr — Wed, 30 Nov 2022 13:37:05 GMT

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.

Re: Translating a complex SQL query into a tDBInput

Anonymous — Wed, 30 Nov 2022 14:05:17 GMT

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

Re: Translating a complex SQL query into a tDBInput

stephbzr — Wed, 30 Nov 2022 14:11:19 GMT

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