topic parameter to SQL query in Talend Studio

parameter to SQL query

Darmesh — Fri, 08 Jun 2018 07:36:02 GMT

parameter file

BBCODE parm1 parm2 parm3
ATXL IN Dec-17 Dec-16 Sep-17

SELECT round(NET_SALES) FROM svg_test.sch6_template WHERE YYYYMM In('" + ((String)globalMap.get("test_log.param1"))+"','" + ((String)globalMap.get("test_log.param2"))+"','" + ((String)globalMap.get("test_log.param3"))+"') and bbcode=''" + ((String)globalMap.get("test_log.BBCODE"))+"'') AS Revenue_Rs_m1;

I am passing multiple parameter in single query using IN operator

Is this correct?

Re: parameter to SQL query

manodwhb — Fri, 08 Jun 2018 07:47:16 GMT

@Darmesh,context parameter file should be link this.

parameter file

BBCODE=ATXL IN

parm1 =Dec-17

parm2 =Dec-16

parm3=Sep-17

Re: parameter to SQL query

Darmesh — Fri, 08 Jun 2018 07:53:14 GMT

i am generating multiple parameter file from the table to excel file and using that in sql filter condition

this is my sql query. n that i have to pass. tell me what is the right way to implement.

SELECT
distinct b.Company,b.Reco,b.Price,
(SELECT round(NET_SALES) FROM svg_test.qtr_template WHERE YYYYMM = 'Mar-18' and bbcode='AL IN') AS Revenue_Rs_m,
(SELECT round(NET_SALES) FROM svg_test.qtr_template WHERE YYYYMM = 'Mar-17' and bbcode='AL IN') AS R_Year,
(SELECT round(NET_SALES) FROM svg_test.qtr_template WHERE YYYYMM = 'Dec-17' and bbcode='AL IN') AS R_Quarter,
(SELECT CAST(((Revenue_Rs_m-R_Year)/R_Year)*100 as decimal(10,1)) 'Revenue_Rs_m YOY%' FROM svg_test.qtr_template WHERE YYYYMM = 'Mar-18' and bbcode='AL IN') AS 'Revenue_Rs_m YOY%',
(SELECT CAST(((Revenue_Rs_m-R_Quarter)/R_Quarter)*100 as decimal(10,1)) 'Revenue_Rs_m QOQ%' FROM svg_test.qtr_template WHERE YYYYMM = 'Mar-18' and bbcode='AL IN') AS 'Revenue_Rs_m QOQ%',
(SELECT CAST(EBITDA_MARGIN as decimal(10,1)) FROM svg_test.qtr_template WHERE YYYYMM = 'Mar-18' and bbcode='AL IN') AS 'EBITDA_MARGIN_Dec-17',
(SELECT CAST(EBITDA_MARGIN as decimal(10,1)) FROM svg_test.qtr_template WHERE YYYYMM = 'Mar-17' and bbcode='AL IN') AS 'EBITDA_MARGIN_Dec-16',
(SELECT round(ADJ_NET_PROF) FROM svg_test.qtr_template WHERE YYYYMM = 'Mar-18' and bbcode='AL IN') AS Adjusted_PAT,
(SELECT round(ADJ_NET_PROF) FROM svg_test.qtr_template WHERE YYYYMM = 'Mar-17' and bbcode='AL IN') AS A_Year,
(SELECT round(ADJ_NET_PROF) FROM svg_test.qtr_template WHERE YYYYMM = 'Dec-17' and bbcode='AL IN') AS A_Quarter,
(SELECT CAST(((Adjusted_PAT-A_Year)/A_Year)*100 as decimal(10,1)) 'Adjusted_PAT YOY%' FROM svg_test.qtr_template WHERE YYYYMM = 'Mar-18' and bbcode='AL IN') AS 'Adjusted_PAT YOY%',
(SELECT CAST(((Adjusted_PAT-A_Quarter)/A_Quarter)*100 as decimal(10,1)) 'Adjusted_PAT QOQ%' FROM svg_test.qtr_template WHERE YYYYMM = 'Mar-18' and bbcode='AL IN') AS 'Adjusted_PAT QOQ%',
(SELECT round(NET_SALES) FROM svg_test.sch6_template WHERE YYYYMM ='Mar-18' and bbcode='AL IN') AS Revenue_Rs_m1,
(SELECT round(NET_SALES) FROM svg_test.sch6_template WHERE YYYYMM ='Mar-17' and bbcode='AL IN') AS R_Year1,
(SELECT CAST(((Revenue_Rs_m1-R_Year1)/R_Year1)*100 as decimal(10,1)) FROM svg_test.sch6_template WHERE YYYYMM ='Mar-18' and bbcode='AL IN') AS 'Revenue_Rs_m YOY1%',
(SELECT round(ADJ_NET_PROF) FROM svg_test.sch6_template WHERE YYYYMM ='Mar-18' and bbcode='AL IN') AS Adjusted_PAT_Rs_m1,
(SELECT round(ADJ_NET_PROF) FROM svg_test.sch6_template WHERE YYYYMM ='Mar-17' and bbcode='AL IN') AS A_Year1,
(SELECT CAST(((Adjusted_PAT_Rs_m1-A_Year1)/A_Year1)*100 as decimal(10,1)) FROM svg_test.sch6_template WHERE YYYYMM ='Mar-18' and bbcode='AL IN') AS 'Adjusted_PAT_Rs_m_YOY1%',
(SELECT round(NET_SALES) FROM svg_test.sch6_template WHERE YYYYMM ='Mar-19' and bbcode='AL IN') AS Revenue_Rs_m2,
(SELECT round(NET_SALES) FROM svg_test.sch6_template WHERE YYYYMM ='Mar-18' and bbcode='AL IN') AS R_Year2,
(SELECT CAST(((Revenue_Rs_m2-R_Year2)/R_Year2)*100 as decimal(10,1)) FROM svg_test.sch6_template WHERE YYYYMM ='Mar-19' and bbcode='AL IN') AS 'Revenue_Rs_m YOY2%',
(SELECT round(ADJ_NET_PROF) FROM svg_test.sch6_template WHERE YYYYMM ='Mar-19' and bbcode='AL IN') AS Adjusted_PAT_Rs_m2,
(SELECT round(ADJ_NET_PROF) FROM svg_test.sch6_template WHERE YYYYMM ='Mar-18' and bbcode='AL IN') AS A_Year2,
(SELECT CAST(((Adjusted_PAT_Rs_m2-A_Year2)/A_Year2)*100 as decimal(10,1)) 'Adjusted_PAT_Rs_m_YOY%' FROM svg_test.sch6_template WHERE YYYYMM ='Mar-19' and bbcode='AL IN') AS 'Adjusted_PAT_Rs_m_YOY%2',
(SELECT round(NET_SALES) FROM svg_test.sch6_template WHERE YYYYMM ='Mar-20' and bbcode='AL IN') AS Revenue_Rs_m3,
(SELECT round(NET_SALES) FROM svg_test.sch6_template WHERE YYYYMM ='Mar-19' and bbcode='AL IN') AS R_Year3,
(SELECT CAST(((Revenue_Rs_m3-R_Year3)/R_Year3)*100 as decimal(10,1)) FROM svg_test.sch6_template WHERE YYYYMM in('Mar-20','Sep-19','Dec-19') and bbcode='AL IN') AS 'Revenue_Rs_m YOY%3',
(SELECT round(ADJ_NET_PROF) FROM svg_test.sch6_template WHERE YYYYMM ='Mar-20' and bbcode='AL IN') AS Adjusted_PAT_Rs_m3,
(SELECT round(ADJ_NET_PROF) FROM svg_test.sch6_template WHERE YYYYMM ='Mar-19' and bbcode='AL IN') AS A_Year3,
(SELECT CAST(((Adjusted_PAT_Rs_m3-A_Year3)/A_Year3)*100 as decimal(10,1)) FROM svg_test.sch6_template WHERE YYYYMM ='Mar-20' and bbcode='AL IN') AS 'Adjusted_PAT_Rs_m_YOY%3',
(SELECT CAST((RT_ADJ_EPS_RS) as decimal(10,1)) FROM svg_test.sch6_template WHERE yyyymm ='Mar-18' and bbcode='AL IN') AS FY_1,
(SELECT CAST((RT_ADJ_EPS_RS) as decimal(10,1)) FROM svg_test.sch6_template WHERE yyyymm ='Mar-19' and bbcode='AL IN') AS FY_2,
(SELECT CAST((RT_ADJ_EPS_RS) as decimal(10,1)) FROM svg_test.sch6_template WHERE yyyymm ='Mar-20' and bbcode='AL IN') AS FY_3,
(SELECT CAST((price/FY_1) as decimal(10,1)) FY18_X FROM svg_test.sch6_template WHERE yyyymm ='Mar-18' and bbcode='AL IN') AS FY18_X,
(SELECT CAST((price/FY_2) as decimal(10,1)) FY19_X FROM svg_test.sch6_template WHERE yyyymm ='Mar-19' and bbcode='AL IN') AS FY19_X,
(SELECT CAST((price/FY_3) as decimal(10,1)) FY20_X FROM svg_test.sch6_template WHERE yyyymm ='Mar-20' and bbcode='AL IN') AS FY20_X
FROM svg_test.price_and_reco b where b.bbcode='AL IN';

Re: parameter to SQL query

Darmesh — Fri, 08 Jun 2018 08:06:39 GMT

This is my flow.

Re: parameter to SQL query

manodwhb — Fri, 08 Jun 2018 08:26:00 GMT

@Darmesh,yes flow looks ok for me.

Re: parameter to SQL query

Darmesh — Fri, 08 Jun 2018 08:40:20 GMT

ok.can you tell in steps. bcoz when i am using the below query it is not loading anything

kindly suggest me what to do.

Re: parameter to SQL query

Darmesh — Mon, 11 Jun 2018 06:43:54 GMT

@TRF, @manodwhb

Still i dont know how to pass multiple parameter in single filter condition. can you help me?

Re: parameter to SQL query

Anonymous — Mon, 11 Jun 2018 09:56:08 GMT

OK, I see your problem. The tricky thing for us is that we do not have access to your system to try the query. You need to use a very useful trick to help YOU resolve this.

Remove your DB component (or disable it) and use the SQL String you are creating in a tJava or tJavaFlex component. Add it to a System.out.println() call, something like this.....

System.out.println("Select myColumns from MyTable Where myColumn1 = '" +((String)globalMap.get("myParam"))+"'");

Then run the job, get your prepared SQL and try it in your SQL query analyzer. This will highlight the errors, or give you a better idea why it isn't working (you may be producing good SQL, but the filtering may be bad). If you still cannot figure it, put the SQL you are generating here and maybe we can have a better idea of what is going wrong.

Re: parameter to SQL query

David_Beaty — Tue, 12 Jun 2018 16:13:56 GMT

You could always attach a tJava via "On Component Error" and print out the contents of the components QUERY global map variable.