Dear friends and neighbors,
I'm running Pervasive 9.5 (9.50.077.002) on a Microsoft Windows Server 2003 R2 Standard Edition with Service Pack 2.
I have a table that I use to define a group of categories and subcategories. This table is used to create nested menu options in a drill-down fashion. Here is a simplified version for demonstation purposes.
Table name : categories
CatNum ParentNum CatName
------ --------- -----------
1 0 Autos
2 0 Motorcycles
3 0 Trucks
4 1 Audi
5 1 Chevy
6 1 Toyota
7 2 Honda
8 2 Kawasaki
9 2 Suzuki
10 2 Yamaha
11 3 Mack
12 3 Volvo
13 6 Camry
14 6 Corolla
15 6 Prius
16 6 Yaris
I want to write a query to produce the following:
CatNum CatName NumberOfSubcategories
------ ----------- ---------------------
1 Autos 3
2 Motorcycles 4
3 Trucks 2
4 Audi 0
5 Chevy 0
6 Toyota 4
7 Honda 0
8 Kawasaki 0
9 Suzuki 0
10 Yamaha 0
11 Mack 0
12 Volvo 0
13 Camry 0
14 Corolla 0
15 Prius 0
16 Yaris 0
I want to produce the above results in one query. This will eventually be deployed on an ASP page for an intranet.
To prototype the SQL, I've tried various methods that work in Microsoft Access, but when I try them in the Pervasive Control Center, I get a SQL Execution Error (java.io.IOException: LNA session closed). Then I need to restart the Pervasive services on the server. In-line techniques all seem to fail.
The only method that succeeds is as follows:
SELECT c1.CatNum, c1.CatName,
( SELECT Count(c2.ParentNum)
FROM categories c2
WHERE c2.ParentNum = c1.CatNum ) AS NumberOfSubcategories
FROM categories c1
Is this the best way to produce the information I desire?