Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5

    Unanswered: trouble inserting from a cte

    I am on Z/OS, DB2 V9.


    I have a query to populate a calendar table, to be used for common date functions. The query uses a CTE to get each day of the year and then get me additional info as well. The query itself runs fine, but when I try to put insert in front of it, it dies.

    Code:
    WITH MYCTE(BEGRANGE) AS
     (SELECT DATE(STRIP(DIGITS(YEAR(CURRENT DATE) + 1),L,'0')
             || '-01-01') BEGRANGE
         FROM SYSIBM.SYSDUMMY1
         UNION ALL
      SELECT BEGRANGE + 1 DAY
         FROM    MYCTE
      WHERE   BEGRANGE + 1 DAY  <= DATE(STRIP(DIGITS(YEAR(CURRENT DATE) + 1),L,'0') || '-12-31') )
    
    
     SELECT BEGRANGE AS G_DT
           ,JULIAN_DAY(BEGRANGE) AS G_JUL_DT
          ,DAYOFWEEK(BEGRANGE) AS DAY_OF_WEEK
           ,CASE
               WHEN MONTH(BEGRANGE) IN (1,2,3)
                  THEN 1
               WHEN MONTH(BEGRANGE) IN (4,5,6)
                 THEN 2
               WHEN MONTH(BEGRANGE) IN (7,8,9)
                 THEN 3
               WHEN MONTH(BEGRANGE) IN (11,12,10)
                 THEN 4
           END AS QTR
           ,WEEK(BEGRANGE)    AS WEEKNBR
          ,CASE
              WHEN DAYOFWEEK(BEGRANGE) IN (2,3,4,5,6)
               and hol.g_holiday_dt is null
                 THEN 'Y'
              ELSE 'N'
           END AS G_NXT_BUS_DT_IND
    
          ,CASE WHEN DAYOFWEEK(BEGRANGE) IN (1,7)
                 THEN 'W'
                when hol.g_holiday_dt is not null
                 THEN 'H'
                else null
           END AS G_NXT_DT_RSN_CD
          ,CASE WHEN DAYOFWEEK(BEGRANGE) IN (1,7)
                 THEN 'WEEKEND DATE'
                WHEN hol.g_holiday_dt is not null
                 THEN hol.g_holiday_desc
                else null
           END AS G_NXT_DT_RSN_CD_DESC
           ,'AGGRAY' 
           ,CURRENT TIMESTAMP
      FROM MYCTE 
    left outer join ncadev01.g_holiday_desc_tb hol
       on mycte.begrange = hol.g_holiday_dt
    Gives me each day of the year and the additional data that I want in the table. When I add on at the front of the above SQL:
    Code:
    INSERT INTO NCADEV01.G_CALENDAR_TB
            (G_DT
            ,G_JUL_DT
            ,G_DAY_OF_WK
            ,G_QTR_OF_YR
            ,G_WK_OF_YR
            ,G_NXT_BUS_DT_IND
            ,G_NXT_DT_RSN_CD
            ,G_NXT_DT_RSN_CD_DESC
            ,G_AUD_USER_ID
            ,G_AUD_TS)
    I get a -104. An unexpected ")" was found following "". Expected tokens may include "MICROSECONDS MICRODESCOND SECONDS SECOND MINUTES MINUTE HOURS". SQLSTATE=42601.

    Any help you can provide would be appreciated.

    Dave Nance

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... An unexpected ")" was found following "". ...
    What if put a statement delimiter(semicolon) at the end of the statement?

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Makes absolutely no difference. Still receive the same -104. I have attempted changing it up quite a bit. I am now trying to change it so that all the calculations are done within the CTE, then just do a select * from the CTE see if that is any different.
    Dave Nance

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    not sure exactly which change I made got it to work, but went back to running it in SPUFI and ran fine this last time. Here is the query I used:

    Code:
    INSERT INTO NCADEV01.G_CALENDAR_TB                                      00010017
    WITH MYCTE(BEGRANGE) AS (SELECT DATE(STRIP(DIGITS(YEAR(CURRENT DATE)    00020017
     + 1),L,'0') || '-01-01') BEGRANGE                                      00020117
    FROM SYSIBM.SYSDUMMY1                                                   00021017
    UNION ALL                                                               00021317
    SELECT BEGRANGE + 1 DAY                                                 00021417
    FROM MYCTE                                                              00021517
    WHERE BEGRANGE + 1 DAY <= DATE(STRIP(DIGITS(YEAR(CURRENT DATE)          00021617
      + 1),L,'0') || '-12-31') )                                            00021717
                                                                            00021817
                                                                            00023017
    SELECT BEGRANGE AS G_DT                                                 00023117
    ,JULIAN_DAY(BEGRANGE) AS G_JUL_DT                                       00023217
    ,DAYOFWEEK(BEGRANGE) AS DAY_OF_WEEK                                     00023317
    ,CASE                                                                   00023417
    WHEN MONTH(BEGRANGE) IN (1,2,3)                                         00023517
    THEN 1                                                                  00023617
    WHEN MONTH(BEGRANGE) IN (4,5,6)                                         00023717
    THEN 2                                                                  00023817
    WHEN MONTH(BEGRANGE) IN (7,8,9)                                         00023917
    THEN 3                                                                  00024017
    WHEN MONTH(BEGRANGE) IN (11,12,10)                                      00024117
    THEN 4                                                                  00024217
    END AS QTR                                                              00024317
    ,WEEK(BEGRANGE) AS WEEKNBR                                              00024417
    ,CASE                                                                   00024517
    WHEN DAYOFWEEK(BEGRANGE) IN (2,3,4,5,6)                                 00024617
    AND HOL.G_HOLIDAY_DT IS NULL                                            00024717
    THEN 'Y'                                                                00024817
    ELSE 'N'                                                                00024917
    END AS G_NXT_BUS_DT_IND                                                 00025017
                                                                            00025117
    ,CASE WHEN DAYOFWEEK(BEGRANGE) IN (1,7)                                 00025217
    THEN 'W'                                                                00025317
    WHEN HOL.G_HOLIDAY_DT IS NOT NULL                                       00025417
    THEN 'H'                                                                00025517
    ELSE NULL                                                               00025617
    END AS G_NXT_DT_RSN_CD                                                  00025717
    ,CASE WHEN DAYOFWEEK(BEGRANGE) IN (1,7)                                 00025817
    THEN 'WEEKEND DATE'                                                     00025917
    WHEN HOL.G_HOLIDAY_DT IS NOT NULL                                       00026017
    THEN HOL.G_HOLIDAY_DESC                                                 00026117
    ELSE NULL                                                               00026217
    END AS G_NXT_DT_RSN_CD_DESC                                             00026317
    ,'AGGRAY'                                                               00026417
    ,CURRENT TIMESTAMP                                                      00026517
    FROM MYCTE                                                              00026617
    LEFT OUTER JOIN NCADEV01.G_HOLIDAY_DESC_TB HOL                          00026717
    ON MYCTE.BEGRANGE = HOL.G_HOLIDAY_DT
    Dave Nance

Posting Permissions

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