Forum Discussion

RPietrzak's avatar
RPietrzak
Copper Contributor
Jun 17, 2019

Use Parameters in DB2 SQL

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

 

excel

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 17, 2019

    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

     

     

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jun 14, 2024

        Raymond_Pietrzak 

        Oh, so old thread. Glad it helped.

        I never worked with DB2 and don't know connector to it works with query folding or not. If the former perhaps we could do everything with M-scripts or even from UI, without native SQL scripts.

        What is more optimal - it depends.