SQL Statement Problem

[Guest.Visitor]

Brian, I would put the program in debut and look at the sol statement being executed. You may need to start a service job on the job servicing the program before you start the debugging session. HTH

[Guest.Visitor]

Brian,

I'd really like to see the offending statement.

Bill

Keyword SELECT not expected. Valid tokens: FOR WITH ORDER UNION OPTIMIZE. Prepared statement SQLSTM not found.

[Guest.Visitor]

Thanks Alan and Bill. The problem has been resolved. The code did have a problem they said they had ran it through DEBUG but had not...... Brian

[Guest.Visitor]

HI All I am getting the following message in SQL on one of my systems but it apparently runs on another. Both systems are at V4R4 and are 720's. 4....+....5....+....6....+....7....+....8....+.... 9....+....0....+....1.... WITH ORDER UNION OPTIMIZE. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement bu the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again. Job 858723/QUSER/QZDASOINIT started on 06/07/01 at 16:18:06 in subsystem QSERVER in QSYS. Job entered system on 06/07/01 at 16:18:06. Printer device SYSPRT150 not found. Servicing user profile PCS. Servicing user profile PCS from client xxx.xxx.xxx Keyword SELECT not expected. Valid tokens: FOR WITH ORDER UNION OPTIMIZE. Prepared statement SQLSTM not found. Cursor SHPCSR not open. Cursor SHPCSR not open. Keyword SELECT not expected. Valid tokens: FOR WITH ORDER UNION OPTIMIZE. Prepared statement SQLSTM not found. Cursor SHPCSR not open. Cursor SHPCSR not open. Keyword SELECT not expected. Valid tokens: FOR WITH ORDER UNION OPTIMIZE. Thanks Brian Babienau

[Guest.Visitor]

the following information was received on this problems anyone have any thoughts what could be different on 2 identical systems V4R4 same cum and ptf's ???? SQL Statement Problem The program used on SYSTEMB was not different from SYSTEMA. We even went as far as saving and restoring the copy of the production object we keep on SYSTEMA onto SYSTEMB. This object worked on SYSTEMA but didn't on SYSTEMB. We "fixed" the issue by slightly changing the SQL statement being prepared; we added an ORDER BY clause to the SELECT statement on the PREPARE STATEMENT command. the change to the variable name turns out to be irrelevant ... the change was really about which of two existing RPG variables containing SQL was actually being prepared for execution; the original variable had the SQL statement without the ORDER BY clause; the new variable had the ORDER BY clause. The issue is still that there is something different between SYSTEMA and SYSTEMB that changes how dynamic embedded SQL is interpreted. At compile time, the dynamic embedded SQL is turned into a call to QSQROUTE. I would suggest that as a good place to start looking. Thanks again Brian Babineau