how to pass parameter dynamically without manual entry

[Darmesh]

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

[TRF]

Your question is not so clear but you should have a look at Talend Date routines chapter from documentation.

However, from my understanding of what you want, this piece of code may help you:

Date lastDec = TalendDate.ADD_TO_DATE(TalendDate.setDate(TalendDate.getCurrentDate(), 12, "MM"), "YYYY", -1);
System.out.println("lastDec: "+TalendDate.TO_CHAR(lastDec, "yyyyMM"));

Date lastQ3 = TalendDate.ADD_TO_DATE(TalendDate.setDate(lastDec, 12, "MM"), "MONTH", -3);
System.out.println("lastQ3: "+TalendDate.TO_CHAR(lastQ3, "yyyyMM"));

Date prevYear = TalendDate.ADD_TO_DATE(lastDec, "YYYY", -1);
System.out.println("prevYear: "+TalendDate.TO_CHAR(prevYear, "yyyyMM"));

Date nextQ1 = TalendDate.ADD_TO_DATE(lastDec, "MONTH", +3);
System.out.println("nextQ1: "+TalendDate.TO_CHAR(nextQ1, "yyyyMM"));

Just copy/past to a tJava component and run the job.

Here is the result:

Démarrage du job test a 11:33 26/05/2018.
[statistics] connecting to socket on port 3393
[statistics] connected
lastDec: 201712
lastQ3: 201709
prevYear: 201612
nextQ1: 201803
[statistics] disconnected
Job test terminé à 11:33 26/05/2018. [Code sortie=0]

[Darmesh]

Hi,

can you tell me how to pass the output values in sql query.

Explain me in steps. 

[TRF]

You just have to replace Java locale variable from the example by global variable that you can reuse into your SQL statement as it is in your 1rst query.

Use a tJavaRow to place the necessary code for each needed variable.

For example:

globalMap.put("lastDec", 
    TalendDate.TO_CHAR(TalendDate.ADD_TO_DATE(
        TalendDate.setDate(TalendDate.getCurrentDate(), 12, "MM"), "YYYY", -1), "yyyyMM"));

globalMap.put("lastQ3", 
    TalendDate.TO_CHAR(TalendDate.ADD_TO_DATE(
        TalendDate.setDate(lastDec, 12, "MM"), "MONTH", -3), "yyyyMM");

You may also put the code directly into your SQL statement but as it is long, it seems not a good idea.

[Darmesh]

Hi TRF,

Can you explain me steps with components. I am new to talend.

[Darmesh]

Hi TRF,

still am not able solve this.

whether the component which i have used is correct. if not let me know the steps.

[TRF · Accepted solution]

2 things you have to change:

1. tJavaRow should not br the 1rst component of your subjob as it expect an input flow. If you don't have an input flow, just replace tJavaRow by tJava
2. the variables you set in your component are "lastDec" and "lastQ3" but in your tMySqlRow you use "test_log.lastDec" and "last_log.lastQ3" and I'm afraid they null, so you have to change the variables name

[Darmesh]

Thanks TRF,

that's works fine.

one doubt. when i'm passing the below code in tjava, i am getting the output.

Date lastDec = TalendDate.getCurrentDate();
System.out.println("lastDec: "+TalendDate.TO_CHAR(lastDec, "MMM-yy"));

O/P

Jun-18

the same code am passing thru globalvar, am getting null in tjava.

correct me on the code.

[TRF]

I think there is a space char before "lastDec" in the globalMap.put opteration.

Can you check it?

[Darmesh]

Checked...there is no space .