OVRDBF and SQL

["Fleming, Greg \(ED\)" <GFLEMING@xxxxxxxxxxxxxxxxxxxx>]

I'm writing an SQLRPG program to insert data into one of three identical
files, depending on which brand the data is from.
 
When searching the archives, I see that OVRDBF has been suggested as an
option for dealing with multi-member files, and thought it might work in
my situation as well. 
 
I generally like to use QCmdExc to do my OvrDBF's when dealing with RPG,
then open the file.  But in SQL, I don't even declare the file on the F
specs, so open/close doesn't seem to apply.  
 
Will this work, or do I have to do my Override in a CL or calling RPG
program.  Or, should I declare the file and open it before I run the SQL
? 
 
Here's what I've got.  It works when the override is not necessary, but
when I pass in one of the "alternate" files, I get no data in any of
them.
 
H/copy GnSrc4,HSPEC
     HDftActGrp(*No)
      *
     FISCDET    IF   E           K DISK
      *
     D MAIN            PR                  EXTPGM('MK320PR')
     D  Comp                          3
     D  FileName                     10
     D MAIN            PI
     D  P#Comp                        3
     D  P#File                       10
      *
     D QCmdExc         PR                  EXTPGM('QCMDEXC')
     D  Command                     400
     D  Length                       15  5
      *
     D W#Start         S              7  0
     D W#Comp          S              3  0
     D W#Replace       S              1
     D WKCmd           S            400
     D WKCmdLen        S             15  5 INZ(400)
      *
      *
      /Free
       // Get Start Date
       W#Comp = %Int(P#Comp);
       Chain (W#Comp:'COGN':'PHSW') ISCDET;
       If %Found(ISCDET);
          W#Start = %Int(%Trim(CDDATA));
       EndIf;
 
       // Get Replace/Append Flag (Replace = 1, Append = 0)
       Chain (W#Comp:'COGA':'PHSW') ISCDET;
       If %Found(ISCDET);
          W#Replace = %Trim(CDDATA);
       EndIf;

       //Clear File
       WKCmd = 'CLRPFM FILE(' + %Trim(P#File) + ')';
       QCmdExc(WKCmd:WKCmdLen);

       //Override File
       If P#File <> 'MKPHSW';
          WKCmd = 'OVRDBF File(MKPHSW) ToFile(P#File)';
          QCmdExc(WKCmd:WKCmdLen);
       EndIf;
      /End-Free
      *
      *
     C/Exec SQL
     C+ Set Option Commit = *None
     C/End-Exec
     C
     C                   If        W#Replace = '1'
     C/Exec SQL
     C+ Insert into MKPHSW
     C+ select CYCOMP, CYVJCF,
     C+ Case When CYIYDY = 0
     C+    Then '0001-01-01'
     C+ Else
     C+    substr(char(CYIYDY + 19000000),1,4) || '-' ||
     C+    substr(char(CYIYDY + 19000000),5,2) || '-' ||
     C+    substr(char(CYIYDY + 19000000),7,2)
     C+ End,
     C+ CYJYNB, CYFQNQ, EYQDCD
     C+ from CSSFND a INNER JOIN mssrc b
     C+ on a.CYCOMP = b.EYCOMP and a.CYVJCF = b.EYSJCD
     C+ where CYcomp = :W#Comp and CYIYDY >= :W#Start
     C/End-Exec
     C                   Else
     C/Exec SQL
     C+ Insert Into MKPHSW
     C+  select ZCCOMP, ZCSRCD, ZCMLDT, ZCCUST, ZCINDV, EYQDCD
     C+  from MKPH2W a INNER JOIN MSSRC b
     C+  on a.ZCCOMP = b.EYCOMP and a.ZCSRCD = b.EYSJCD
     C+  where ZCCOMP = :W#Comp
     C/End-Exec
     C                   EndIf
     C
      /Free
       If P#File <> 'MKPHSW';
          WKCmd = 'DltOvr FILE(MKPHSW)';
          QCmdExc(WKCmd:WKCmdLen);
       EndIf;
       *InLR = *On;
      /End-Free 
 
 
Thanks.

Greg Fleming

Programmer/Analyst

Everglades Direct, Inc.