Hello

I'm looking for a solution to write the outer left join on several table of the sql in pro-cobol because the latter does not know how to manage it. Here is the error I had and the code I wrote. I work under oracle

mistacke

 from VIVERS a
   244  
Pro*COBOL: Release 12.1.0.2.0 - Production on Thu May 6 13:53:12 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Error at line 245, column 12 in file VIH400.pco
   245             LEFT OUTER JOIN VICOMPTE
   245  ...........1
   245  PCB-S-00400, Encountered the symbol "LEFT" when expecting one of the following:
       
           , START END-EXEC CONNECT GROUP HAVING INTERSECT MINUS ORDER
           WHERE WITH FOR UNION
       
Error at line 246, column 30 in file VIH400.pco
   246             on   vib_cd_rv01N = a.chv_vers_cd_rv01N  
   246  .............................1
   246  PCB-S-00400, Encountered the symbol "=" when expecting one of the following:
       
           id END-EXEC
       
   247             and   vib_no_rv01N = a.chv_vers_no_rv01N
   248  
Error at line 249, column 12 in file VIH400.pco
   249             LEFT OUTER JOIN CONTRAT1
   249  ...........1
   249  PCB-S-00400, Encountered the symbol "LEFT" when expecting one of the following:
       
           + - * / . ( AND OR AT DAY RETURNING YEAR || @
       
Error at line 250, column 27 in file VIH400.pco
   250             on sob_cd_r101 = a.vin_cd_r101  
   250  ..........................1
   250  PCB-S-00400, Encountered the symbol "=" when expecting one of the following:
       
           id END-EXEC
       
   251             and sob_no_r001 = a.vin_no_r001  
   252             and sob_no_r102 = a.VIN_NO_R102
   253  
Error at line 254, column 12 in file VIH400.pco
  

Code

SELECT …...

from VIVERS a

LEFT OUTER JOIN VICOMPTE

on vib_cd_rv01N = a.chv_vers_cd_rv01N

and vib_no_rv01N = a.chv_vers_no_rv01N

LEFT OUTER JOIN CONTRAT1

on sob_cd_r101 = a.vin_cd_r101

and sob_no_r001 = a.vin_no_r001

and sob_no_r102 = a.VIN_NO_R102

LEFT OUTER join adrcont

on CH_ADRCONT1_NO_R001 = a.vin_NO_R001

AND ADRCONT.SOX_CD_R101 = a.vin_CD_R101

AND ADRCONT.SOX_NO_R102 = a.vin_NO_R102

LEFT OUTER JOIN VIEMIS

on CHV_EMIS_CD_RV01N = a.CHV_VERS_CD_RV01N

and CHV_EMIS_NO_RV01N = a.CHV_VERS_NO_RV01N

and vip_cd_R620N = 1

and (vip_dt_R632an = a.vin_dt_R630an

and vip_dt_R632mn = a.vin_dt_R630mn

or

vip_dt_R632an = a.vin_dt_R632an

and vip_dt_R632mn = a.vin_dt_R632mn

and vip_dt_R632jn = a.vin_dt_R632jn

or

vip_dt_R630an = a.vin_dt_R630an

and vip_dt_R630mn = a.vin_dt_R630mn

and vip_dt_R630jn = a.vin_dt_R630jn

or

vip_dt_R634an = a.vin_dt_R630an

and vip_dt_R634mn = a.vin_dt_R630mn

and vip_dt_R634jn = a.vin_dt_R630jn

)

LEFT OUTER JOIN PROFAGRA

on SOI_CH_GARANT_NO_R001 = a.VIN_NO_R001

and SOI_CH_GARANT_CD_R101 = a.VIN_CD_R101

and SOI_CH_GARANT_NO_R102 = a.VIN_NO_R102

and (soi_cd_re67n_1 = 1 and soi_cd_re65_1 = 'P'

and soi_dt_re61n_1 < a.vin_dt_R630an*100+a.vin_dt_R630mn

and soi_dt_re62n_1 > a.vin_dt_R630an*100+a.vin_dt_R630mn

or soi_cd_re67n_2 = 1 and soi_cd_re65_2 = 'P'

and soi_dt_re61n_2 < a.vin_dt_R630an*100+a.vin_dt_R630mn

and soi_dt_re62n_2 > a.vin_dt_R630an*100+a.vin_dt_R630mn)

and sob_cd_r125 = 'U'

LEFT OUTER JOIN VIVERS b

on b.chv_vers_cd_rv01N = a.chv_vers_cd_rv01N

and b.chv_vers_no_rv01N = a.chv_vers_no_rv01N

and b.vin_dt_r630an = a.vin_dt_r634an

and b.vin_dt_r630mn = a.vin_dt_r634mn

and b.vin_dt_r630jn = a.vin_dt_r634jn

and b.vin_cd_r620n = 61

LEFT OUTER JOIN VIVERS c

on c.chv_vers_cd_rv01N = a.chv_vers_cd_rv01N

and c.chv_vers_no_rv01N = a.chv_vers_no_rv01N

and c.vin_dt_r630an = vip_dt_r634an

and c.vin_dt_r630mn = vip_dt_r634mn

and c.vin_dt_r630jn = vip_dt_r634jn

where a.vin_dt_r630AN = :WANNEE-MENS

and a.vin_cd_r620n = 60

and a.vin_cd_r635 = 'J'

and vip_cd_rv59 <> 'VS'

I am not an Oracle expert, but I believe the error you've received is caused by a default behavior of the Oracle Pro*COBOL precompiler. By default, it does not recognize OUTER JOIN syntax, but instead an older join syntax used before Oracle 9. Try specifying the option common_parser=yes to the Pro*COBOL precompiler. For example:

procob iname=samplej.pco common_parser=yes

Blair

It resolved a problem that I had with a customer in production when needing to add an inner join. Thanks Blair! Lifesaver,,