Use Parameters in DB2 SQL

[RPietrzak]

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

[#2]

@RPietrzak 

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