dynamic clause pecularities in FROM syntax

["Banks, Richard" <Richard.Banks@CBA.COM.AU>]

FYI in case you end up beating your forehead on your monitor whilst writing
SQRs....

-----

I've found a workaround (below) but does anyone care to explain this one to
me;

I have been trying to use a dynamic FROM clause so I can use inline views
with DB2 and SQR for PeopleSoft 8.16 (NT)

The following (simplified) syntax fails;

let $selected_dept_div = 'PS_DEPT_TBL DEPT_DIV'

begin-select
DEPT_DIV.DEPTID,

  show 'FOUND ROW ' &dept_div.deptid

FROM PS_EX_SHEET_HDR HDR,
PS_EX_SHEET_LINE LINE,
[$selected_dept_div],
PS_PERSONAL_DATA PD,
PS_EX_AA_EE_ORG_VW EE_ORG
WHERE PD.EMPLID = HDR.EMPLID
AND HDR.SHEET_ID = LINE.SHEET_ID
AND PD.EMPLID = EE_ORG.EMPLID
AND DEPT_DIV.DEPTID = EE_ORG.DEPTID
end-select

-----

However if I move the string to the first parameter in the FROM table list,
it works;

begin-select
DEPT_DIV.DEPTID,

  show 'FOUND ROW ' &dept_div.deptid

FROM [$selected_dept_div],
PS_EX_SHEET_HDR HDR,
PS_EX_SHEET_LINE LINE,
PS_PERSONAL_DATA PD,
PS_EX_AA_EE_ORG_VW EE_ORG
WHERE PD.EMPLID = HDR.EMPLID
AND HDR.SHEET_ID = LINE.SHEET_ID
AND PD.EMPLID = EE_ORG.EMPLID
AND DEPT_DIV.DEPTID = EE_ORG.DEPTID
end-select

It appears to me that SQR parses/passes the select statement incorrectly to
DB2 when you have the string variable anywhere but in the first position.

Anyone else had similar experiences?

-----

The error is actually
(SQR 5528) ODBC SQL dbsql: SQLPrepare error -104 in cursor 1:
   [IBM][CLI Driver][DB2/SUN] SQL0104N  An unexpected token
"END-OF-STATEMENT" was found following "PS_PERSONAL_DATA PD,".  Expected
tokens may include:  "<table_ref>".  SQLSTATE=42601

SQL: SELECT EE_ORG.DEPTID, HDR.EMPLID, HDR.SHEET_ID, HDR.SHEET_NAME,
     HDR.SUBMISSION_DATE, PD.NAME  FROM PS_EX_SHEET_HDR HDR,
PS_EX_SHEET_LINE
     LINE, PS_PERSONAL_DATA PD,

Error on line 111:
   (SQR 3716) Error in SQL statement.


**************   IMPORTANT MESSAGE  **************
This e-mail message is intended only for the addressee(s) and contains 
information which may be confidential. If you are not the intended recipient 
please advise the sender by return email, do not use or disclose the contents, 
and delete the message and any attachments from your system. Unless 
specifically indicated, this email does not constitute formal advice or 
commitment by the sender or the Commonwealth Bank of Australia (ABN 48 123 123 
124) or its subsidiaries.
**************************************************