topic Re: How to pass multiple parameter to a MYSQL table in Talend Studio

How to pass multiple parameter to a MYSQL table

Darmesh — Fri, 23 Feb 2018 07:58:35 GMT

Hi,

I am new to the talend.
I want load the parameter dynamically into the table.

Query:

SELECT
distinct b.bbcode1,
(SELECT NET_SALES FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-18' and bbcode1='Ashok Leyland') AS Revenue_Rs_m1,
(SELECT GROWTH FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-18' and bbcode1='Ashok Leyland') AS 'Revenue_Rs_m YOY1%',
(SELECT ADJ_NET_PRFT FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-18' and bbcode1='Ashok Leyland') AS Adjusted_PAT_Rs_m1,
(SELECT GROWTH_1 FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-18' and bbcode1='Ashok Leyland') AS 'Adjusted_PAT_Rs_m_YOY1%',
(SELECT NET_SALES FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-19' and bbcode1='Ashok Leyland') AS Revenue_Rs_m2,
(SELECT GROWTH FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-19' and bbcode1='Ashok Leyland') AS 'Revenue_Rs_m YOY2%',
(SELECT ADJ_NET_PRFT FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-19' and bbcode1='Ashok Leyland') AS Adjusted_PAT_Rs_m2,
(SELECT GROWTH_1 FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-19' and bbcode1='Ashok Leyland') AS 'Adjusted_PAT_Rs_m_YOY2%',
(SELECT NET_SALES FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-20' and bbcode1='Ashok Leyland') AS Revenue_Rs_m3,
(SELECT GROWTH FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-20' and bbcode1='Ashok Leyland') AS 'Revenue_Rs_m YOY3%',
(SELECT ADJ_NET_PRFT FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-20' and bbcode1='Ashok Leyland') AS Adjusted_PAT_Rs_m3,
(SELECT GROWTH_1 FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-20' and bbcode1='Ashok Leyland') AS 'Adjusted_PAT_Rs_m_YOY3%',
(SELECT RT_ADJ_EPS_RS FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-18' and bbcode1='Ashok Leyland') AS FY_1,
(SELECT RT_ADJ_EPS_RS FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-19' and bbcode1='Ashok Leyland') AS FY_2,
(SELECT RT_ADJ_EPS_RS FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-20' and bbcode1='Ashok Leyland') AS FY_3,
(SELECT CAST((price/FY_1) as decimal(10,1)) FY18_X FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-18' and bbcode1='Ashok Leyland') AS FY_1,
(SELECT CAST((price/FY_2) as decimal(10,1)) FY19_X FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-19' and bbcode1='Ashok Leyland') AS FY_2,
(SELECT CAST((price/FY_3) as decimal(10,1)) FY20_X FROM svg_test.sch6_back_page WHERE PERIOD_END = 'Mar-20' and bbcode1='Ashok Leyland') AS FY_3

FROM svg_test.sch6_back_page b where b.bbcode1='Ashok Leyland';

so i want to like, if i pass a parameter from a file.

par1=Mar-18
par2=Mar-19
par3=Mar-20
bb=Eicher motors

Similarly i have 100 companies names and i have to load one by one to the table dynamically

how to do this in talend. Kindly help me in this how to acheive it step by step.

Re: How to pass multiple parameter to a MYSQL table

TRF — Fri, 23 Feb 2018 08:12:26 GMT

Hi,

SQL queries are just strings so you can have parameters in global or context variables and use them to construct the query dynamically like in this example:

"SELECT col1, col2 FROM myTable WHERE col3 = '" + (String)globalMap.get("param1") + "' AND col4 = " + context.param2

where "col3" is a string column and "col4" is a numeric one.

It's your job to transpose this example to your usecase.

Re: How to pass multiple parameter to a MYSQL table

Darmesh — Fri, 25 May 2018 10:14:51 GMT

Hi,

requirement is for every quarter i will go and change the parameter like mar-18 or sep-18 etc.

Instead of that i have to load it dynamically without entering every time for each quarter.

I have attached test.xlsx which is the parameter file which contains company name, and 8 column with name parm 1,2,... so i am passing each parameter value in sql query.

SQL Query

INSERT INTO temp_report(SELECT
distinct b.bbcode,b.COMPANY ,b.Reco,b.Price,
(SELECT round(NET_SALES) FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM1"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS Revenue_Rs_m,
(SELECT round(NET_SALES) FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM2"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS R_Year,
(SELECT round(NET_SALES) FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM3"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS R_Quarter,
(SELECT CAST(((Revenue_Rs_m-R_Year)/R_Year)*100 as decimal(10,1)) 'Revenue_Rs_m YOY%' FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM1"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Revenue_Rs_m YOY%',
(SELECT CAST(((Revenue_Rs_m-R_Quarter)/R_Quarter)*100 as decimal(10,1)) 'Revenue_Rs_m QOQ%' FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM1"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Revenue_Rs_m QOQ%',
(SELECT CAST(EBITDA_MARGIN as decimal(10,1)) FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM1"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'EBITDA_MARGIN_Dec-17',
(SELECT CAST(EBITDA_MARGIN as decimal(10,1)) FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM2"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'EBITDA_MARGIN_Dec-16',
(SELECT round(ADJ_NET_PROF) FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM1"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS Adjusted_PAT,
(SELECT round(ADJ_NET_PROF) FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM2"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS A_Year,
(SELECT round(ADJ_NET_PROF) FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM3"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS A_Quarter,
(SELECT CAST(((Adjusted_PAT-A_Year)/A_Year)*100 as decimal(10,1)) 'Adjusted_PAT YOY%' FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM1"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Adjusted_PAT YOY%',
(SELECT CAST(((Adjusted_PAT-A_Quarter)/A_Quarter)*100 as decimal(10,1)) 'Adjusted_PAT QOQ%' FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM1"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Adjusted_PAT QOQ%',
(SELECT round(NET_SALES) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM5"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS Revenue_Rs_m1,
(SELECT round(NET_SALES) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM4"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS R_Year1,
(SELECT CAST(((Revenue_Rs_m1-R_Year1)/R_Year1)*100 as decimal(10,1)) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM5"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Revenue_Rs_m YOY1%',
(SELECT round(ADJ_NET_PROF) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM5"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS Adjusted_PAT_Rs_m1,
(SELECT round(ADJ_NET_PROF) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM4"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS A_Year1,
(SELECT CAST(((Adjusted_PAT_Rs_m1-A_Year1)/A_Year1)*100 as decimal(10,1)) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM5"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Adjusted_PAT_Rs_m_YOY1%',
(SELECT round(NET_SALES) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM6"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS Revenue_Rs_m2,
(SELECT round(NET_SALES) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM5"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS R_Year2,
(SELECT CAST(((Revenue_Rs_m2-R_Year2)/R_Year2)*100 as decimal(10,1)) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM6"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Revenue_Rs_m YOY2%',
(SELECT round(ADJ_NET_PROF) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM6"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS Adjusted_PAT_Rs_m2,
(SELECT round(ADJ_NET_PROF) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM5"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS A_Year2,
(SELECT CAST(((Adjusted_PAT_Rs_m2-A_Year2)/A_Year2)*100 as decimal(10,1)) 'Adjusted_PAT_Rs_m_YOY%' FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM6"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Adjusted_PAT_Rs_m_YOY%2',
(SELECT round(NET_SALES) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM7"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS Revenue_Rs_m3,
(SELECT round(NET_SALES) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM6"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS R_Year3,
(SELECT CAST(((Revenue_Rs_m3-R_Year3)/R_Year3)*100 as decimal(10,1)) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM7"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Revenue_Rs_m YOY%3',
(SELECT round(ADJ_NET_PROF) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM7"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS Adjusted_PAT_Rs_m3,
(SELECT round(ADJ_NET_PROF) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM6"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS A_Year3,
(SELECT CAST(((Adjusted_PAT_Rs_m3-A_Year3)/A_Year3)*100 as decimal(10,1)) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM7"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Adjusted_PAT_Rs_m_YOY%3',
(SELECT CAST((RT_ADJ_EPS_RS) as decimal(10,1)) FROM sch6_template WHERE yyyymm = '" + ((String)globalMap.get("test_log.PARM5"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS FY_1,
(SELECT CAST((RT_ADJ_EPS_RS) as decimal(10,1)) FROM sch6_template WHERE yyyymm = '" + ((String)globalMap.get("test_log.PARM6"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS FY_2,
(SELECT CAST((RT_ADJ_EPS_RS) as decimal(10,1)) FROM sch6_template WHERE yyyymm = '" + ((String)globalMap.get("test_log.PARM7"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS FY_3,
(SELECT CAST((price/FY_1) as decimal(10,1)) FY18_X FROM sch6_template WHERE yyyymm = '" + ((String)globalMap.get("test_log.PARM5"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS FY18_X,
(SELECT CAST((price/FY_2) as decimal(10,1)) FY19_X FROM sch6_template WHERE yyyymm = '" + ((String)globalMap.get("test_log.PARM6"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS FY19_X,
(SELECT CAST((price/FY_3) as decimal(10,1)) FY20_X FROM sch6_template WHERE yyyymm = '" + ((String)globalMap.get("test_log.PARM7"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS FY20_X
FROM price_and_reco b where b.BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"');

Logic is if my current year is dec-17, then for quarter i need sep-17 and for year i need dec-16

the above is first requirement

similarly for next quarter is mar-18 then, i should not go to the parameter file and change the values.

so i need for quarter dec-17 and for year mar-17 automatically

test.xlsx

Re: How to pass multiple parameter to a MYSQL table

Darmesh — Fri, 25 May 2018 10:16:27 GMT

Hi,

requirement is for every quarter i will go and change the parameter like mar-18 or sep-18 etc.

Instead of that i have to load it dynamically without entering every time for each quarter.

I have attached test.xlsx which is the parameter file which contains company name, and 8 column with name parm 1,2,... so i am passing each parameter value in sql query.

SQL Query

Logic is if my current year is dec-17, then for quarter i need sep-17 and for year i need dec-16

the above is first requirement

similarly for next quarter is mar-18 then, i should not go to the parameter file and change the values.

so i need for quarter dec-17 and for year mar-17 automatically

test.xlsx