parameter to SQL query

[Darmesh]

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? 

[manodwhb]

@Darmesh,context parameter file should be link this.

parameter file

BBCODE=ATXL IN

parm1 =Dec-17

parm2 =Dec-16

parm3=Sep-17

[Darmesh]

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';

[Darmesh]

This is my flow.

[manodwhb]

@Darmesh,yes flow looks ok for me.

[Darmesh]

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

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;.

kindly suggest me what to do.

[Darmesh]

@TRF, @manodwhb

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

[Anonymous]

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.

[David_Beaty]

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