parameter to SQL query

Six Stars

parameter to SQL query

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? 

Forteen Stars

Re: parameter to SQL query

@Darmesh,context parameter file should be link this.

 

parameter file

BBCODE=ATXL IN

parm1 =Dec-17

parm2 =Dec-16

parm3=Sep-17

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Six Stars

Re: parameter to SQL query

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

a.PNG

 

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

Six Stars

Re: parameter to SQL query

This is my flow.

 

a.PNGb.PNG

Forteen Stars

Re: parameter to SQL query

@Darmesh,yes flow looks ok for me.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Six Stars

Re: parameter to SQL query

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.

Six Stars

Re: parameter to SQL query

@TRF@manodwhb

 

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

Community Manager

Re: parameter to SQL query

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.

Nine Stars

Re: parameter to SQL query

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

Regards David
Dont forget to give Kudos when an answer is helpful or mark the answer as the solution.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog