| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-21-11, 11:31
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
|
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
|
|
12-21-11, 13:52
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
|
... An unexpected ")" was found following "". ...
|
What if put a statement delimiter(semicolon) at the end of the statement?
|
|
12-22-11, 10:52
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
|
|
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
|
|
12-22-11, 18:09
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
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
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
trouble inserting from a cte
