Re: Multiple file entries for the same file

CREATE TABLE ROB/GCONV (GROUPID CHAR (10 ) NOT NULL WITH DEFAULT, 
CONTRACT SMALLINT NOT NULL WITH DEFAULT, EXPDATE DATE NOT NULL WITH
DEFAULT, CONSTRAINT GCONVKEY PRIMARY KEY (GROUPID, CONTRACT, 
EXPDATE)) 

insert...

select * from gconv 
order by groupid, contract, expdate
....+....1....+....2....+....3..
GROUPID     CONTRACT  EXPDATE 
A                 1   2003-12-01
A                 2   2003-12-01
A                 2   2004-12-01
B                 3   2003-12-01
********  End of data  ******** 

select * 
from gconv a 
where a.groupid concat char(a.contract) not in ( 
  select b.groupid concat char(b.contract) from gconv b
    where b.expdate>date('12/31/2003') ) and 
  a.expdate<date('12/31/2003') 
order by a.groupid, a.contract, a.expdate 

....+....1....+....2....+....3..
GROUPID     CONTRACT  EXPDATE 
A                 1   2003-12-01
B                 3   2003-12-01
********  End of data  ******** 

Is this the result you are looking for?

Rob Berendt
-- 
"All creatures will make merry... under pain of death."
-Ming the Merciless (Flash Gordon)




CarollaT@xxxxxxxxxxxxxx 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
12/19/2003 10:59 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
rpg400-l@xxxxxxxxxxxx
cc

Fax to

Subject
Multiple file entries for the same file






Here's the sitch:

 

I have a file that contains contract definitions (GROUP), and a second 
file
that contains renewal instances of these contracts (GCONV), with renewal
dates.  Any contract would have only one GROUP record, and can have 
multiple
GCONV records, one for each date range of the contract. 

 

My task is to find contract instances that need renewing for the beginning
of next year, and create new GCONV records for these. 

 

What I am doing is searching through the GCONV file for any expiring
contracts as of a prompted date.  For each record I find, I would _like_ 
to
then search the file to see if this particular contract has already been
renewed. 

 

With no logical files built over the GCONV file, I am trying to open the
same file, with two F-specs, as follows:

 

     FGCONVRD   IF   E             DISK    EXTFILE(FILEGCONV) BLOCK(*YES)

     F                                     USROPN

     FGCONVCK   IF   E           K DISK    EXTFILE(FILEGCONV) USROPN
PREFIX(CK)

     F                                     RENAME(GRMREC:GRMREC2)

 

I had to rename the record format, or the compiler ignored the spec. Also,
to get this to compile, I had to OVRDBF each file to point to a separate
copy of the GCONV file.  The problem is that when the program runs, as 
soon
as I OPEN the second file, I get an RNX1215 - OPEN was issued to opened 
file
GCONVRD. 

 

My question -- is this even possible?  Is there a way to have the RPG
program open the same physical file twice?  We have over 50 data libraries
with copies of the same files in each, and it would be non-desirable to
create any LF's in each library. 

 

Right now, I am solving this by writing a separate program that will run 
in
place of this small routing. 

_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.