Jun 8, 2009 9:01 AM
Problems using SQL with
Hi everyone,
I'M migrating VAGEN to EGL and am having problems with the SQL WITH command.
I get a "SQLException: ILLEGAL SYMBOL ?" when validating/generating my code to Cobol.
This is the code as migrated by EGL
#sql{
WITH MAXWEEK AS
(SELECT
CASE WHEN DAYOFWEEK(T1.MAXDATE) = 7
THEN WEEK(T1.MAXDATE) + 1
ELSE WEEK(T1.MAXDATE)
END WEEKNO
FROM
(SELECT STRIP(CHAR(YEAR(:GDM054W-MAIN.LOOP-CUR-DATE)))
CONCAT CHAR('-12-31') AS MAXDATE
FROM SYSIBM.SYSDUMMY1) T1),
ACTWEEK AS
(SELECT
CASE WHEN DAYOFWEEK(:GDM054W-MAIN.LOOP-CUR-DATE) = 7
THEN WEEK(:GDM054W-MAIN.LOOP-CUR-DATE) + 1
ELSE WEEK(:GDM054W-MAIN.LOOP-CUR-DATE)
END WEEKNO
FROM SYSIBM.SYSDUMMY1 T1)
SELECT
CASE
WHEN DAYOFWEEK(T1.DATE_DB2) = 7
THEN DATE(T1.DATE_DB2)
ELSE DATE(T1.DATE_DB2) - DAYOFWEEK(T1.DATE_DB2) DAYS
END WEEK_START_DATE
,CASE
WHEN T2.WEEKNO = T3.WEEKNO
THEN STRIP(CHAR(YEAR(:GDM054W-MAIN.LOOP-CUR-DATE)))
CONCAT CHAR('-12-31')
ELSE
CASE
WHEN DAYOFWEEK(T1.DATE_DB2) <= 6
THEN DATE(T1.DATE_DB2) + (6 - DAYOFWEEK(T1.DATE_DB2)) DAYS
ELSE DATE(T1.DATE_DB2) + 6 DAYS
END
END WEEK_END_DATE
INTO
:GDM054W-CALENDRIER.PROD_WEEK_BEG,
:GDM054W-CALENDRIER.PROD_WEEK_END
FROM
GDM.CALENDRIER T1,
MAXWEEK T2,
ACTWEEK T3
WHERE T1.DATE_DB2 = :GDM054W-MAIN.LOOP-CUR-DATE
}
for GDM-CALENDRIER ;
Thanks in advance for any help.