Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2016
    Posts
    5

    Unanswered: How can I do a WHILE loop similar to the following in DB2 10

    Hello,

    There is this script to run a report on a monthly basis that those before me have been manually modifying every month. The report returns the results for the past 14 months. it is currently over 12k lines of SQL. This is because they have the same script repeated 14 times.

    I am wondering if there is a way to run this in a loop and incrementing the month, thereby making the SQL only 900 lines of SQL. We are running in DB2 10 in multiple environments.

    I was thinking something like:

    set i = 0
    while i < 15 do
    i + 1
    rpt_date = dateadd (rpt_date, month, - i)
    --the original select statment that returns a count
    count * as count + i


    Or something similar?
    Any Ideas??
    Thanks for any and all suggestions.

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    559
    Provided Answers: 2
    SET V_COUNTER = 0;
    WHILE (V_COUNTER < 30) DO
    SET V_COUNTER = V_COUNTER + 1;
    -- your other statements
    END WHILE;

    That's DB2 LUW. More info: https://www.ibm.com/support/knowledg.../r0005655.html
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Aug 2016
    Posts
    5

    Results of the suggestion

    Quote Originally Posted by aflorin27 View Post
    SET V_COUNTER = 0;
    WHILE (V_COUNTER < 30) DO
    SET V_COUNTER = V_COUNTER + 1;
    -- your other statements
    END WHILE;

    That's DB2 LUW. More info: https://www.ibm.com/support/knowledg.../r0005655.html
    I've tried this and get the following error regarding the WHILE statement:

    SQL0104N An unexpected token "while" was found following "SET V_COUNTER = 0 ". Expected tokens may include: "IN". SQLSTATE=42601

    Here is the SQL for one of the 10 reports that I need to do this with:

    SET V_COUNTER = 0
    while (V_COUNTER < 15) DO
    SET V_COUNTER = V_COUNTER + 1

    SELECT
    TA.PGM_NME_PARM,
    COUNT(*) AS RESULT + V_COUNTER
    FROM
    (SELECT
    W100DP1.NCCS_DISP_VAL_FC(T6.PGM_NME_PARM) AS PGM_NME_PARM,
    T1.UPI,
    COUNT(*) AS COUNT_1
    FROM
    W100DP1.NCCS_APPLC_TBL T1,
    W100DP1.NCCS_APPLC_ELIG_TBL T2,
    W100DP1.NCCS_APPLC_ELIG_RSLT_TBL T3,
    W100DP1.NCCS_PGM_LIST_TBL T6,
    W100DP1.ENTPR_PARM T7
    WHERE T2.APPLC_ELIG_ID = T3.APPLC_ELIG_ID
    AND T1.APPLC_SEQ_ID = T2.APPLC_SEQ_ID
    AND T3.GRP_SET_STAT IN ('12', '14')
    AND ((T3.ELIG_STRT_DT <= W100DP1.FIRST_DAY('2015-03-01') AND T3.ELIG_END_DT >= W100DP1.LAST_DAY('2015-03-01')) OR
    (T3.ELIG_STRT_DT <= W100DP1.FIRST_DAY('2015-03-01') AND T3.ELIG_END_DT >= W100DP1.FIRST_DAY('2015-03-01')) OR
    (T3.ELIG_STRT_DT <= W100DP1.LAST_DAY('2015-03-01') AND T3.ELIG_END_DT >= W100DP1.LAST_DAY('2015-03-01')) OR
    (T3.ELIG_STRT_DT >= W100DP1.FIRST_DAY('2015-03-01') AND T3.ELIG_END_DT <= W100DP1.LAST_DAY('2015-03-01')))

    AND T3.PGM_LIST_ID = T6.PGM_LIST_ID
    AND T6.PGM_NME_PARM = T7.PARM_ID
    AND T7.PARM_TYP = 'PROGRAM_NAME'

    GROUP BY
    T6.PGM_NME_PARM,
    T1.UPI
    ORDER BY
    T6.PGM_NME_PARM,
    T1.UPI) TA

    GROUP BY TA.PGM_NME_PARM
    ORDER BY TA.PGM_NME_PARM
    for fetch only
    with UR
    END WHILE;

    Thank you for your rapid response,

    Ken

  4. #4
    Join Date
    Jan 2003
    Posts
    4,286
    Provided Answers: 5
    First you need to put semicolons after each statement in the stored procedure. Second, what are you doing with the select statement? issuing a select statement outside a cursor or SET statement does nothing.

    Andy

  5. #5
    Join Date
    Aug 2016
    Posts
    5

    Not a Stored procedure

    Quote Originally Posted by ARWinner View Post
    First you need to put semicolons after each statement in the stored procedure. Second, what are you doing with the select statement? issuing a select statement outside a cursor or SET statement does nothing.

    Andy
    To begin this is not a stored procedure. The powers that be do not want stored procedures. This is the way the SQL currently runs. This is only one of ten statements for the report in question. then they are repeated 14 times with the dates changed to retrieve the past 14 months.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,286
    Provided Answers: 5
    You will need a different statement terminator other then semicolon (e.g. @). The statement inside the WHILE will be terminated with a semicolon.

    Code:
    SET V_COUNTER = 0@
    while (V_COUNTER < 15) DO
    SET V_COUNTER = V_COUNTER + 1;
    
    SELECT
    TA.PGM_NME_PARM,
    COUNT(*) AS RESULT + V_COUNTER
    FROM
    (SELECT
    W100DP1.NCCS_DISP_VAL_FC(T6.PGM_NME_PARM) AS PGM_NME_PARM,
    T1.UPI,
    COUNT(*) AS COUNT_1
    FROM
    W100DP1.NCCS_APPLC_TBL T1,
    W100DP1.NCCS_APPLC_ELIG_TBL T2,
    W100DP1.NCCS_APPLC_ELIG_RSLT_TBL T3,
    W100DP1.NCCS_PGM_LIST_TBL T6,
    W100DP1.ENTPR_PARM T7
    WHERE T2.APPLC_ELIG_ID = T3.APPLC_ELIG_ID
    AND T1.APPLC_SEQ_ID = T2.APPLC_SEQ_ID
    AND T3.GRP_SET_STAT IN ('12', '14')
    AND ((T3.ELIG_STRT_DT <= W100DP1.FIRST_DAY('2015-03-01') AND T3.ELIG_END_DT >= W100DP1.LAST_DAY('2015-03-01')) OR
    (T3.ELIG_STRT_DT <= W100DP1.FIRST_DAY('2015-03-01') AND T3.ELIG_END_DT >= W100DP1.FIRST_DAY('2015-03-01')) OR
    (T3.ELIG_STRT_DT <= W100DP1.LAST_DAY('2015-03-01') AND T3.ELIG_END_DT >= W100DP1.LAST_DAY('2015-03-01')) OR
    (T3.ELIG_STRT_DT >= W100DP1.FIRST_DAY('2015-03-01') AND T3.ELIG_END_DT <= W100DP1.LAST_DAY('2015-03-01')))
    
    AND T3.PGM_LIST_ID = T6.PGM_LIST_ID
    AND T6.PGM_NME_PARM = T7.PARM_ID
    AND T7.PARM_TYP = 'PROGRAM_NAME'
    
    GROUP BY
    T6.PGM_NME_PARM,
    T1.UPI
    ORDER BY
    T6.PGM_NME_PARM,
    T1.UPI) TA
    
    GROUP BY TA.PGM_NME_PARM
    ORDER BY TA.PGM_NME_PARM
    for fetch only
    with UR;
    END WHILE@

  7. #7
    Join Date
    Aug 2016
    Posts
    5

    Being told Can't work unless in Stored procedure

    I have been talking with some of my co-workers. One of them is saying that I can't do a while statement except within a stored procedure or within the where clause. If this is true is there some other type of loop I might try, that can work?

  8. #8
    Join Date
    Apr 2012
    Posts
    1,006
    Provided Answers: 16
    Quote Originally Posted by Kennethc View Post
    I have been talking with some of my co-workers. One of them is saying that I can't do a while statement except within a stored procedure or within the where clause. If this is true is there some other type of loop I might try, that can work?
    Post which operating-system runs your DB2-servers -is it Z/OS, i-series, AIX/Linux/Solaris/HPUX/Windows ?
    Post which operating-system runs your client(s) - because it's trivial to script dynamic SQL via any number of scripting languages.

    I suggest you refer to the DB2 Knowledge Center for your platform and version to see if it supports inline compound statements in dynamic SQL which does allow FOR, WHILE, IF etc.

  9. #9
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    66
    Provided Answers: 6
    Hello,

    test.sql file contents:
    Code:
    --#SET TERMINATOR @
    SET SERVEROUTPUT ON@
    
    BEGIN
      DECLARE V_COUNTER INT DEFAULT 0;
      WHILE (V_COUNTER < 5) DO
        CALL DBMS_OUTPUT.PUT_LINE('---Iteration number: '||V_COUNTER||'---');
        FOR C1 AS 
          SELECT TABSCHEMA, TABNAME
          FROM (
          SELECT TABSCHEMA, SUBSTR(TABNAME, 1, 20) TABNAME, RAND() RN_
          FROM SYSCAT.TABLES
          )
          ORDER BY RN_
          FETCH FIRST 3 ROWS ONLY
        DO
          CALL DBMS_OUTPUT.PUT_LINE(C1.TABSCHEMA||'.'||C1.TABNAME);
        END FOR;
        SET V_COUNTER=V_COUNTER+1;
      END WHILE;
    END@
    Usage:

    db2 connect to mydb
    db2 -f test.sql
    Regards,
    Mark.

  10. #10
    Join Date
    Aug 2016
    Posts
    5

    It can't be done the way I was trying

    Hello Everyone,

    I have found that what I was trying to do can't be done in a standard SQL Script. It can only be done in procedures.

    Thanks for all you suggestions.

    Ken

  11. #11
    Join Date
    Apr 2012
    Posts
    1,006
    Provided Answers: 16
    Shame you gave no facts about client and server environment details as requested, other than "DB2 V10" and "multiple environments" . "Cannot be done" is meaningless without those facts.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •