CTE using with statement

[kmonahan01]

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 )

[Scott Klement]

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.

[B.Hauser]

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.

Birgitta

[kmonahan01]

Thank you Scott and Birgitta. One more question - are CTEs usable in V7R2, or am I spinning my wheels for no reason?

[Scott Klement]

Yes, CTEs are available in 7.2

[kmonahan01]

Thanks Scott, sorry for the dumb question about V7R2. I went back to delete it but you had already answered.
However, I cannot find an example of using a CTE to UPDATE data in a file. Is this possible? It appears to be for select or insert but not update.
I may need to extract the data and then fetching in RPGLE to update each record.

[TedHolt]

You can't use a common table expression in an UPDATE statement, but you can put a SELECT inside the USING clause of MERGE.

[kmonahan01]

Thanks Ted, I'll look into that. BTW, I enjoyed your Summit presentations last month.

[TedHolt]

You're welcome. I'm glad to hear it.

[kmonahan01]

Thanks again Ted. I did some digging based on your Merge/Update suggestion and I think i have it working! After 35 years of RPG, it's fun to learn new SQL stuff!

[TedHolt]

K: If you get this working properly, maybe you could share your code with us.

[kmonahan01]

After all the help I've gotten from this group, I'm happy to. I did not use a CTE, btw. using_merge_match.docx

[TedHolt]

That looks good! You wouldn't need a CTE in your MERGE, since the USING clause can join files.

Thanks for sharing, KM. I'm glad your problem is solved.