Avatar of Jeff
Jeff
Flag for United States of America asked on

Catalog Index

Tools: DB2 on iSeries and VBA in Bartender barcode software

I am creating catalogs for customers using data gathered via DB2 SQL and Bartender for printing. These items are grouped by category and I need an index of the categories to put in front of these catalogs.

My Data file:

ITEM         Description              Cost          Category
012345,    My first item,           9.99,         Cat1
012356,    My second item,     8.99,         Cat1
123456,    My third item,         9.99,         Cat2
112345,    My fourth item,           9.99,         Cat3
222356,    My fifth item,     8.99,         Cat3
333456,    My sixth item,         9.99,         Cat3
and so on...

There are 13 records per page.

So I'm thinking something along the lines of:

PageCount
RecCount
ItmCat
CatName

Set PageCount = 1
Set CatName = ItmCat
RecCount increments by 1 for every record
PageCount increments by 1 everytime RecCount = 14.
Reset RecCount after hitting 14
IF ItmCat <> CatName
Write PageCount  and ItmCat to file every time ItmCat changes
Loop back to Set

So that my end result is a file to print my index:

Pg 1  Cat1
Pg 2 Cat 2
Pg 6 Cat8
and so on....
DB2DatabasesIBM System iVB ScriptVBA

Avatar of undefined
Last Comment
Gary Patterson, CISSP

8/22/2022 - Mon
Member_2_276102

But what is the problem? What should any answer contain?

The concept seems useful enough, but a better description of how it will be used could be helpful.

What is a "catalog"? That is, you say it's for printing. Is that the end of it? Will there be any other use? Also, this is tagged with both DB2 SQL and VB tags, but it's not clear if anything but VB will be part of any solution. And what "VB" is involved? Is this .NET?

Be aware that 'PageCount' may need adjustment since the printed index will also take one or more "pages".
Jeff

ASKER
Catalog and Index as in a book/PDF. The Index and Catalog, while both created as 8 1/2 X 11 will be created separately as they are in different formats so no need to account for the number of pages contained in the index. The index contains the category and page number of the category to make looking up an item easier. I am already creating the catalog. I'm seeking to create the index.
ASKER CERTIFIED SOLUTION
Gary Patterson, CISSP

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jeff

ASKER
Unfortunately, I awarded this a little too soon. It worked great for my first catalog, which was small, but when I applied this code to a larger catalog it failed about half way through the index.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Gary Patterson, CISSP

What was the error message?
Jeff

ASKER
No error messages. The page numbers started missing by a page or 2 after 18 pages in.
Gary Patterson, CISSP

My bad.  Should be:

int(((min(rowid)) - 1)/13)+1 as pageid  

not

int(((min(rowid)) + 1)/13)+1 as pageid
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jeff

ASKER
Still shifting it of by a page or so.
Gary Patterson, CISSP

I tested it on a 50 page file and it was perfect.  Are you sure you're getting exactly 13 records per page in the document?  If it varies occasionally then this formula won't work.

Run this and see where the problem starts.  You should see the page number increment by one every 13 rows.

select
   rowid,
   category,                                        
   int(((rowid)+1)/13)+1  as pageid
from (                        
   select
      row_number() over(order by category,item) as rowid,
      category
   from catalog) a