RE: OVRDBF and SQL

["Jim Wiant" <Jim.Wiant@xxxxxxxxxxxxxxxx>]

And now that I read the whole message thoroughly, my answer doesn't work
- I was thinking only of the RPG issues not the SQL issues... sorry
about that.

It's still a good technique though ;-)

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Fleming, Greg (ED)
Sent: Wednesday, 5 October 2005 10:03
To: midrange-L@xxxxxxxxxxxx
Subject: OVRDBF and SQL


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.

 

 
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

######################################################################
This message was scanned for compliance with Foodstuffs email policies
######################################################################
This message has been sent from Foodstuffs (Auckland) Limited ("Foodstuffs").

The information contained in this message and or attachments 
is intended only for the person or entity to which it is 
addressed and may contain confidential and/or privileged 
material. Any review, retransmission, dissemination or other 
use of, or taking of any action in reliance upon, this 
information by persons or entities other than the intended 
recipient is prohibited. If you received this in error, 
please contact the sender and delete the material from any 
system and destroy any copies.

The views and opinions expressed in this message may be those
of the individual and not necessarily those of Foodstuffs, 
and are not given or endorsed by it.

Please note that this communication does not designate an 
information system for the purposes of the Electronic 
Transactions Act 2002.