Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Reporting Question
 Login/Join
 
<Paul Mueller>
posted
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
 
Report This Post
Darin Lee
Virtuoso
posted Hide Post
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?


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Leah
Virtuoso
posted Hide Post
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).


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
<Paul Mueller>
posted
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
 
Report This Post
<Paul Mueller>
posted
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
 
Report This Post
Alan B
Virtuoso
posted Hide Post
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;
.
.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Leah
Virtuoso
posted Hide Post
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?


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
mgrackin
Virtuoso
posted Hide Post
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


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Darin Lee
Virtuoso
posted Hide Post
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?


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
<Paul Mueller>
posted
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
 
Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders