× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



You could always indirectly reference the file name in the RPG program
and then pass a parameter to open the correct file.
Using the keywords EXTFILE and/or EXTMBR on the FSPEC, and use program
described variables for the arguments. Add the USROPN keyword as well...
EXTFILE allows and indirectly file name, EXTMBR allows an indirect
member name.

Then set the variable somewhere in the program before opening the file.
Any file of the same format can then be accessed this way, as well as
membered files.

JPW



-----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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.