sponsored links



No announcement yet.

CTE using with statement

  • Filter
  • Time
  • Show
Clear All
new posts

  • CTE using with statement

    Hi SQL novice here. I am trying to create a CTE in an embedded SQLRPGLE program. A couple of questions. Prior to the 'with' statement, do i need an EXEC SQL statement? Compiler doesn't seem to like that. Second question/issue: The compiler gives me the following error. Any insight into why?

    73 with x as
    ======> a
    *RNF5347 30 a 000700 An assignment operator is expected with the EVAL operation.
    74 (select fmtdate(ahdcr,'YYMD','MDYY/') CREATE_DATE, ahshmt
    75 SHIPMENT , ahstat STATUS
    76 from mylib/ahasnf00 )

  • #2
    Yes, you always need "exec sql" before an SQL statement. Otherwise, RPG will think its an RPG statement rather than an SQL one. That is why you're getting the RNF5437.

    If EXEC SQL is giving you an error make sure you've change the source type to SQLRPGLE and are compiling with the CRTSQLRPGI command.


    • #3
      Just as an aside: COMMON TABLE EXPRESSIONS are part of an SELECT-Statement.
      So you have to embedd the complete SELECT-Statement (incl. CTEs and Final SELECT).
      ... and as Scott said: In embedded SQL each SQL-Statement starts with "EXEC SQL " followed by the SQL Statement itself.