Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik GA: Multivariate Time Series in Qlik Predict: Get Details
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Darmesh
Contributor III
Contributor III
‎2018-02-23 02:58 AM

How to pass multiple parameter to a MYSQL table

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.

Labels (2)
Labels
264 Views
0 Likes
3 Replies
TRF
Champion II
Champion II
‎2018-02-23 03:12 AM

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.

264 Views
0 Likes
Darmesh
Contributor III
Contributor III
‎2018-05-25 06:14 AM
Author

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
264 Views
0 Likes
Darmesh
Contributor III
Contributor III
‎2018-05-25 06:16 AM
Author

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
264 Views
0 Likes