This Question is Answered

1 "correct" answer available (4 pts) 2 "helpful" answers available (2 pts)
2 Replies Last post: Jun 17, 2009 6:18 AM by PAL029  
Click to view PAL029's profile   3 posts since
Jun 8, 2009

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.


Click to view markevans's profile   483 posts since
May 1, 2008
1. Jun 14, 2009 3:36 PM Up Image in response to: PAL029
Re: Problems using SQL with

Hi,

Hi...a couple of questions:

1.) What version of RBD/EGL are you using? And what fixpack are you using?

2.) What DB2 product is being used (DB2 on z/OS, Db2 on i5/OS, etc)?

3.) Is this happening in the DB2 precompiler during the compile/link or during "validation of the SQL statement" during the generation?

4.) Does the same problem happen if you "validate the SQL statement" in the editor?

5.) Can you send the records that is being used in the SQL statement (SQL and working storage/basic records)?


Bottom Banner