Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
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....
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....
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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.
Here's a query that will give you a category index (defined as the page number that the first item from the category appears on).
Assumptions:
1) Catalog is sorted by category and item number.
2) There are always exactly 13 items per page.
This query should produce the index you are looking for:
The inner query produces a list with a row number for each item when sorted by category and item number:
1,CAT1
2,CAT1
3,CAT1
4,CAT1
5,CAT2
6,CAT2,
7,CAT3
8,CAT4
The outer query takes that list groups it by category, selects only the smallest item number in each category, and then calculates the page number for that first item in each category.
If your list of items doesn't start on page one of the catalog, you can adjust it by adding the first page of the actual item list. So for example, if the item list starts on page 5 instead of page 1:
int(((min(rowid))+1)/13)+5
int(((min(rowid))+1)/13)+5
One last note: in order to use the row_number() OLAP function, you'll need to be running V5R4 of the IBM i operating system or later.
Assumptions:
1) Catalog is sorted by category and item number.
2) There are always exactly 13 items per page.
This query should produce the index you are looking for:
select
min(rowid),
category,
int(((min(rowid))+1)/13)+1 as pageid
from (
select
row_number() over(order by category,item) as rowid,
category
from catalog) a
group by category
The inner query produces a list with a row number for each item when sorted by category and item number:
1,CAT1
2,CAT1
3,CAT1
4,CAT1
5,CAT2
6,CAT2,
7,CAT3
8,CAT4
The outer query takes that list groups it by category, selects only the smallest item number in each category, and then calculates the page number for that first item in each category.
If your list of items doesn't start on page one of the catalog, you can adjust it by adding the first page of the actual item list. So for example, if the item list starts on page 5 instead of page 1:
int(((min(rowid))+1)/13)+5
int(((min(rowid))+1)/13)+5
One last note: in order to use the row_number() OLAP function, you'll need to be running V5R4 of the IBM i operating system or later.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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.
My bad. Should be:
int(((min(rowid)) - 1)/13)+1 as pageid
not
int(((min(rowid)) + 1)/13)+1 as pageid
int(((min(rowid)) - 1)/13)+1 as pageid
not
int(((min(rowid)) + 1)/13)+1 as pageid
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
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2
From novice to tech pro — start learning today.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
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".