Problem:
Here is an example of the error message from Oracle PROCOB when a JOIN is used:
-----
Error at line 566, column 38 in file lb010dallas.cbl
FROM RECEIVABLE LEFT JOIN LEGALDTL
.....................................1
PCB-S-00400, Encountered the symbol "JOIN" when expecting one of the following:
, START END-EXEC CONNECT GROUP HAVING INTERSECT MINUS ORDER
WHERE WITH FOR UNION
-----
Running the same syntax from Oracle SQL Developer works.
Resolution:
The precompilers in Oracle are outdated with INNER and OUTER JOIN's. They only precompile the old syntax, so the code will have to be modified.
Here's an example of how to change the syntax:
The syntax for JOIN's in Oracle is a little different when compiling with current versions of Oracle preprocessor. So change the JOIN's as follows:
Take this DB2 statement:
EXEC SQL
DECLARE DR_CSR CURSOR FOR
SELECT DISTINCT
C.CONFIG_ITEM_ID,
C.DR_NBR,
C.DR_TYPE_CD,
C.ASSY_DISC_CD,
C.DISPOSITION_CD,
C.INITIATED_DT,
C.IGNORE_USE_DT_FLG,
C.SUPPRESS_PRINT_FLG,
D.ACI AS ASSY-CONFIG-ID
FROM DATAMGT.AS_BUILT_ITEM_DR AS C LEFT JOIN (SELECT
A.CONFIG_ITEM_ID,
A.DR_NBR,
B.CONFIG_ITEM_ID AS ACI
FROM DATAMGT.AS_BUILT_ITEM_DR AS A,
DATAMGT.AS_BUILT_ITEM_DR AS B
WHERE A.DR_NBR = B.DR_NBR
AND A.CONFIG_ITEM_ID =:CONFIG-ITEM-HOLD
AND A.ASSY_DISC_CD = 'D'
AND B.ASSY_DISC_CD = 'A')
AS D ON C.DR_NBR = D.DR_NBR
WHERE C.CONFIG_ITEM_ID =:CONFIG-ITEM-HOLD
ORDER BY 1
END-EXEC.
And convert it to this Oracle statement.
EXEC SQL
DECLARE DR_CSR CURSOR FOR
SELECT DISTINCT
C.CONFIG_ITEM_ID,
C.DR_NBR,
C.DR_TYPE_CD,
C.ASSY_DISC_CD,
C.DISPOSITION_CD,
C.INITIATED_DT,
C.IGNORE_USE_DT_FLG,
C.SUPPRESS_PRINT_FLG,
D.ACI ASSY_CONFIG_ID
FROM DATAMGT.AS_BUILT_ITEM_DR C,
( SELECT
A.CONFIG_ITEM_ID,
A.DR_NBR,
B.CONFIG_ITEM_ID ACI
FROM DATAMGT.AS_BUILT_ITEM_DR A,
DATAMGT.AS_BUILT_ITEM_DR B
WHERE A.DR_NBR = B.DR_NBR
AND A.CONFIG_ITEM_ID =:CONFIG-ITEM-HOLD
AND A.ASSY_DISC_CD = 'D'
AND B.ASSY_DISC_CD = 'A') D
WHERE C.DR_NBR = D.DR_NBR( )
AND C.CONFIG_ITEM_ID =:CONFIG-ITEM-HOLD
ORDER BY 1
END-EXEC.
