Use Parameters in DB2 SQL

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