Hello,

I have a report request that I am having a terrible time trying to get the correct data back from.


Has anyone ever had to pull records from a table that had something like a sequence number where it gets incremented each time a change is made and then had to save that data, subtract 1 from that sequence number and then go back and get ALL the records that matched this lower sequence number?

I have tried a bunch of different things but have come up empty.

I am reporting from ann oracle database if that helps at all.

Paul

Sounds (on the surface) pretty straightforward. Using a hold file you could define a field that is SEQUENCE - 1, hold the data, then join the hold file using that field back to all SEQUENCE in the original file. You could also do a DEFIN-based join, but I think it would be a little more inefficient because you would have to do a lot more JOINing (additional data retrieval and I/O cost) than the other way. Is this what you're looking for. If not can you give a specific example and/or sample code?

If I understand, you are pulling information based on some selection criteria, which gives the most current sequence number and want to go back and get all of those records that match the criteria with a sequence number less than the highest sequence number.

We have student data that have multiple term records so an example could be

TABLE FILE RTPGTBL_UNO_PROD
WHERE STU_ID EQ '123456789'
PRINT COLLEGE MAJOR_1
COMPUTE SEQX / I2 = IF STU_ID EQ LAST STU_ID THEN 0 ELSE 1;
BY HIGHEST TERM_CYT
WHERE TOTAL SEQX EQ 0
END

In this case TERM_CYT would equate to your sequence.

This assumes of course you are limiting information on other criteria then would be handled in 'one pass'. If not then you would have to hold the data and then join back and pull all records whose sequence was less than the sequence you did pull (hope that makes sense).

Darin,

Thanks alot for your message. I thought the exact same thing. I did just what you said but when I join back to the records it seems to only join to the first record (so my output data is all the from the same record).

When I try to do a join to all I get errors saing duplicates in Join From field.

So even though it sounds straight forward, it really isn't unless I am missing something crucial to make it work properly.

Thanks again.

Paul

Leah,

Thanks for the reply. I do have to make more than one pass and yes I did hold teh data, but when I try to join to all the records with the lower sequence number, i only get the first record repeated each time for the records going in. When I do a join to all I get an error about duplicates in join from field.

Not sure what I may be doing wrong.

Thanks.

Paul

Paul

There should be 2 approaches available to you.

First, where you JOIN ..TO ALL.. , the HOLD file that you initially create should have only one value in it, created by code something like this:

SET HOLDLIST=PRINTONLY
TABLE FILE CAR
PRINT SEATS NOPRINT
COMPUTE NEWSEATS/I4=SEATS-1; NOPRINT
BY TOTAL HIGHEST 1 NEWSEATS
WHERE.....
ON TABLE HOLD
END
JOIN NEWSEATS IN HOLD TO ALL field IN FILE
TABLE FILE filename
.
.

or use the value created and -READ into an &variable.

SET HOLDLIST=PRINTONLY
TABLE FILE CAR
PRINT SEATS NOPRINT
COMPUTE NEWSEATS/I4=SEATS-1; NOPRINT
BY TOTAL HIGHEST 1 NEWSEATS
WHERE.....
ON TABLE SAVE
END
-IF &LINES EQ 0 GOTO NORECORD;
-READ SAVE &SEATS.A4
TABLE FILE filename
.
.
WHERE field EQ &SEATS;
.
.

quote:

do a join to all I get an error about duplicates in join from field.

Not sure what I may be doing wrong.

First are you joining hold file back to table in database? Second is the key in the hold file such that you are in the same order as the database file and the hold file has only one record for data desired. Third, the sequence number shouldn't be part of the join or you'll never get the rest of the records.

Is you code short or long that you could post it?

Here is an example which is done with one pass of the data. The key here is to do a Multi-Verb request and then use the WHERE TOTAL test to eliminate the last sequence which sounds like what you are trying to do.

The first VERB should be a SUM using the MAX. prefix operator on your sequence field. This will give you a column which contains the last sequence number. You can then use this to compare to each individual record using the WHERE TOTAL and column notation.

C01 is the column notation for the MAX sequence number. C03 is the column containing the individual sequence number for each record.

DEFINE FILE VIDEOTRK
TRANSSEQ/I3=
IF (CUSTID NE LAST CUSTID)
THEN 1 ELSE
IF (PRODCODE NE LAST PRODCODE)
THEN LAST TRANSSEQ + 1 ELSE 1;
END
TABLE FILE VIDEOTRK
SUM MAX.TRANSSEQ NOPRINT
BY CUSTID
PRINT LASTNAME TRANSSEQ TRANSDATE PRODCODE TRANSTOT
BY CUSTID
WHERE LASTNAME IN ('RATHER','STANDLER','DIZON','NON-MEMBER')
WHERE TOTAL C03 NE C01
END

Looks like something might be missing - hard to tell without seeing code. Maybe a JOIN CLEAR * or pulling data from hold file instead of original data (they would probably have same field names - use tablename-dot-fieldname like HOLD.SEQUENCE and ORIG.SEQUENCE) We do this with policy numbers without any problem - pull the most recent renewal for an active policy 1234567, renewal 002. Subtract one from the renewal to get the previous one (1234567-001) then join back to data and get all info where policy number is 1234567-001. And it works for all policies at once - not just one at a time.
Maybe we could see your code?

First of all I want to say thanks to everyone who has replied to this.

As I was writing a long message to the board here and including data and code samples, I got it to work.

So I do apprecaite everyones input and I am sure that this process helped me to get it right.

Thanks so much.

That was only part one of the data pull, hopefully I will be able to take care of the rest myself, if not, stay tuned.

Have a great weekend all.

Paul Mueller