Jun 17 2019 09:14 AM
I have an Excel Workbook where I am writing an ODBC query to select DB2 data. This query needs to vary the Year and PayPeriod so I would like to have parameters in my DB2 SQL. How do I do this. I want to have the parameters directly to the SQL so that I am not querying DB2 and returning thousands of row.
My SQL is
SELECT G.PYRL_NO, G.YR, G.PAY_PE, G.PTN_NO, G.BNS_CD,
G.REG_HR, G.PLY_HR, G.SUN_WRK_PREM_HR, G.HOL_WRK_PREM_HR,
I.SIXPP_ICV_RT
FROM OFSPROD.AG_GROSS_DTL G
JOIN OFSPROD.AG_POSITION_RATE P
ON G.PTN_NO = P.PTN_NO
JOIN OFSPROD.AG_ICV_RATE I
ON G.PTN_NO = I.PTN_NO
WHERE 1 = 1
AND G.YR = '2019' AND G.PAY_PE = '10'
AND G.BNS_CD IN ('P','N')
AND G.ICV_EARN = 0
AND G.REG_HR <> 0
AND P.BNS_CD = '2'
AND I.YR = '2019' AND I.PAY_PE = '09'
AND I.SIXPP_ICV_RT <> 0
UNION
SELECT G.PYRL_NO, G.YR, G.PAY_PE, G.ALT_PTN_NO, G.BNS_CD,
G.REG_HR, G.PLY_HR, G.SUN_WRK_PREM_HR, G.HOL_WRK_PREM_HR,
I.SIXPP_ICV_RT
FROM OFSPROD.AG_GROSS_DTL G
JOIN OFSPROD.AG_POSITION_RATE P
ON G.ALT_PTN_NO = P.PTN_NO
JOIN OFSPROD.AG_ICV_RATE I
ON G.ALT_PTN_NO = I.PTN_NO
WHERE 1 = 1
AND G.YR = '2019' AND G.PAY_PE = '10'
AND G.BNS_CD IN ('-','W','X','Y','Z')
AND G.ICV_EARN = 0
AND G.REG_HR <> 0
AND P.BNS_CD = '2'
AND I.YR = '2019' AND I.PAY_PE = '09'
AND I.SIXPP_ICV_RT <> 0
Jun 17 2019 03:40 PM
Your query looks like
let Source = Odbc.Query("dsn=DB2E", "SELECT G.PYRL_NO, G.YR, G.PAY_PE, G.PTN_NO, G.BNS_CD, G.REG_HR, G.PLY_HR, G.SUN_WRK_PREM_HR, G.HOL_WRK_PREM_HR, I.SIXPP_ICV_RT FROM OFSPROD.AG_GROSS_DTL G JOIN OFSPROD.AG_POSITION_RATE P ON G.PTN_NO = P.PTN_NO JOIN OFSPROD.AG_ICV_RATE I ON G.PTN_NO = I.PTN_NO WHERE 1 = 1 AND G.YR = '2019' AND G.PAY_PE = '10' AND G.BNS_CD IN ('P','N') AND G.ICV_EARN = 0 AND G.REG_HR <> 0 AND P.BNS_CD = '2' AND I.YR = '2019' AND I.PAY_PE = '09' AND I.SIXPP_ICV_RT <> 0 UNION SELECT G.PYRL_NO, G.YR, G.PAY_PE, G.ALT_PTN_NO, G.BNS_CD, G.REG_HR, G.PLY_HR, G.SUN_WRK_PREM_HR, G.HOL_WRK_PREM_HR, I.SIXPP_ICV_RT FROM OFSPROD.AG_GROSS_DTL G JOIN OFSPROD.AG_POSITION_RATE P ON G.ALT_PTN_NO = P.PTN_NO JOIN OFSPROD.AG_ICV_RATE I ON G.ALT_PTN_NO = I.PTN_NO WHERE 1 = 1 AND G.YR = '2019' AND G.PAY_PE = '10' AND G.BNS_CD IN ('-','W','X','Y','Z') AND G.ICV_EARN = 0 AND G.REG_HR <> 0 AND P.BNS_CD = '2' AND I.YR = '2019' AND I.PAY_PE = '09' AND I.SIXPP_ICV_RT <> 0 "), <next step>
Let assume you'd like to transfer current year as parameter. When define somehow parameter and in advanced editor modify your code as
let pCurrentYear = Number.ToText(Date.Year(DateTime.LocalNow())), Source = Odbc.Query("dsn=DB2E", "SELECT G.PYRL_NO, G.YR, G.PAY_PE, G.PTN_NO, G.BNS_CD, G.REG_HR, G.PLY_HR, G.SUN_WRK_PREM_HR, G.HOL_WRK_PREM_HR, I.SIXPP_ICV_RT FROM OFSPROD.AG_GROSS_DTL G JOIN OFSPROD.AG_POSITION_RATE P ON G.PTN_NO = P.PTN_NO JOIN OFSPROD.AG_ICV_RATE I ON G.PTN_NO = I.PTN_NO WHERE 1 = 1 AND G.YR = ' " & pCurrentYear & " ' AND G.PAY_PE = '10' AND G.BNS_CD IN ('P','N') AND G.ICV_EARN = 0 AND G.REG_HR <> 0 AND P.BNS_CD = '2' AND I.YR = ' " & pCurrentYear & " ' AND I.PAY_PE = '09' AND I.SIXPP_ICV_RT <> 0 UNION SELECT G.PYRL_NO, G.YR, G.PAY_PE, G.ALT_PTN_NO, G.BNS_CD, G.REG_HR, G.PLY_HR, G.SUN_WRK_PREM_HR, G.HOL_WRK_PREM_HR, I.SIXPP_ICV_RT FROM OFSPROD.AG_GROSS_DTL G JOIN OFSPROD.AG_POSITION_RATE P ON G.ALT_PTN_NO = P.PTN_NO JOIN OFSPROD.AG_ICV_RATE I ON G.ALT_PTN_NO = I.PTN_NO WHERE 1 = 1 AND G.YR = ' " & pCurrentYear & " ' AND G.PAY_PE = '10' AND G.BNS_CD IN ('-','W','X','Y','Z') AND G.ICV_EARN = 0 AND G.REG_HR <> 0 AND P.BNS_CD = '2' AND I.YR = ' " & pCurrentYear & " ' AND I.PAY_PE = '09' AND I.SIXPP_ICV_RT <> 0 "), <next step>
Other words, concatenate sql query string with your parameters