Question

Embedded SQL in a Cobol program using a sub-select in the select clause

COBOL, Embedded SQL, SQLPlus, SQL

The following works fine in SQLPLUS:

SELECT SOVLCUR_PIDM
INTO :STDN-PIDM
FROM SOVLCUR
WHERE SOVLCUR_PIDM = :HOLD-STU-PIDM
AND SOVLCUR_LEVL_CODE = 'UG'
AND SOVLCUR_LMOD_CODE =
SB_CURRICULUM_STR.F_LEARNER
AND SOVLCUR_CURRENT_IND = 'Y'
SSRMEET_ROOM_CODE
FROM SSBSECT, SSRMEET, SGRSPRT
WHERE SGRSPRT_TERM_CODE = '200810'
AND (SGRSPRT_SPST_CODE IS NULL
OR SGRSPRT_SPST_CODE = ' ')
AND SSBSECT_CRN = SSRMEET_CRN
AND SSBSECT_TERM_CODE = '200810'
AND SSRMEET_TERM_CODE = '200810'
AND EXISTS
(SELECT 'X'
FROM SFRSTCR, STVRSTS
WHERE SFRSTCR_PIDM = SGRSPRT_PIDM
AND SFRSTCR_TERM_CODE = '200810'
AND SFRSTCR_CRN = SSBSECT_CRN
AND SFRSTCR_RSTS_CODE = STVRSTS_CODE
AND STVRSTS_INCL_SECT_ENRL = 'Y')

However, when I declare a cursor using this in a Cobol program I get the following error message:


Pro*COBOL: Release 10.2.0.1.0 - Production on Mon May 5 13:41:42 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

System default option values taken from: /mnt00/app/oracle/product/10.2.0/precomp/admin/pcbcfg.cfg

Error at line 236, column 30 in file srr006.pco
(SELECT SIRASGN_PIDM
.............................1
PCB-S-00400, Encountered the symbol "SIRASGN_PIDM" when expecting one of the fol
lowing:

+ - * / . ( ) AT DAY YEAR || @
The symbol ")" was substituted for "SIRASGN_PIDM" to continue.

Error at line 240, column 58 in file srr006.pco
AND SIRASGN_TERM_CODE = '200810'),
.........................................................1
PCB-S-00400, Encountered the symbol ")" when expecting one of the following:

+ - * / START AND OR END-EXEC AT CONNECT DAY GROUP HAVING
INTERSECT MINUS ORDER WITH YEAR FOR || UNION

PCB-I-0556: Unrecoverable error. Fix previous errors and re-precompile
Error at line 297, column 23 in file srr006.pco
FETCH ATH_CURSOR INTO :HOLD-STU-PIDM, :HOLD-SUBJ,
......................1
PCB-S-00225, Undeclared SQL identifier "ATH_CURSOR"
Error at line 748, column 28 in file srr006.pco
EXEC SQL CLOSE ATH_CURSOR END-EXEC.
...........................1
PCB-S-00225, Undeclared SQL identifier "ATH_CURSOR"
make: 1254-004 The error code from the last command is 1.

I am using the sub-select to avoid using an outer join because of performance issues with 10g.


Thanks

Hi,

The field names in your example don't match up with your error messages, can you post the code that gives the error messages as well.

Regards,

Martin Gilbert.