Hi Tim,


I am rewriting an application which supports a variety of DB backends via
plugins (SQLServer, MySQL, Interbase/Firebird, NexusDB, DBISAM and
AbsoluteDB). I am using remote procedure calls and pure SQL to drive the
application, and have run into some essential SQL which does not execute in
DBISAM 4.24, but does in each of the other engines.


I am implementing a heirarchical table of categories using the Nested Set
Model (see
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html for more
information), and need to retrieve a dataset exposing records in an
Adjacency Model format (i.e. RecordID/ParentID) to allow the data to be
displayed in a data-aware treeview. The following SQL should do that:


SELECT B.Id AS PId, P.*
FROM Category AS P
LEFT OUTER JOIN
Category AS B
ON B.TreeLeft
= (SELECT MAX(TreeLeft)
FROM Category AS S
WHERE P.TreeLeft > S.TreeLeft
AND P.TreeLeft < S.TreeRight) WHERE B.Id IN ( SELECT O1.Id
FROM Category AS O1, Category AS O2
WHERE O1.TreeLeft BETWEEN O2.TreeLeft AND O2.TreeRight
AND O2.Id = 4
) AND NOT B.Id IS NULL;


Following is the DDL to create the tables, indexes and populate them with
sample data:

CREATE TABLE Category (
 Id INTEGER NOT NULL,
 RecordGUID CHAR(38),
 TreeLeft INTEGER,
 TreeRight INTEGER,
 Name CHAR(40),
 Description CHAR(250),
 RecordType SMALLINT,
 PWord CHAR(32),
 ImageIndex SMALLINT,
 Path CHAR(250),
 DefaultEditStyle SMALLINT,
 UNIQUE PRIMARY KEY (Id)
);

CREATE INDEX IDX_CATEGORY_TREELEFT ON Category (TreeLeft) COMPRESS FULL;
CREATE INDEX IDX_CATEGORY_TREERIGHT ON Category (TreeRight) COMPRESS FULL;

INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(1,'{2BA288A5-6EDC-48fa-9704-66403C44B626}',1,76,'Root',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(2,'{D12B2CCC-A130-4665-A93E-1FC58513B228}',2,17,'Category
1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(3,'{A787AA21-0774-44ee-A4E3-86A452E242BC}',18,53,'Category
2',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(4,'{70E7730A-072A-4E74-9E14-F88BB72E93E6}',54,75,'Category
3',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(5,'{CF361CC6-CA20-4936-83D6-7C8132689B8A}',3,14,'Category 1 Sub-Category
1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(6,'{DE62FDD7-4781-47E6-8112-812AFC429317}',15,16,'Category 1 Sub-Category
2',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(7,'{1D42B530-5117-48FE-9163-F29F0835D2BE}',19,24,'Category 2 Sub-Category
1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(8,'{4C43A58D-87C5-42C4-8F43-91C5A1D52374}',25,26,'Category 2 Sub-Category
2',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(9,'{18530F90-2B0C-438B-B914-F2B14ABF0C56}',27,34,'Category 2 Sub-Category
3',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(10,'{1B1BDC2F-86A0-4ADB-B428-4A45652BA2A1}',35,50,'Category 2 Sub-Category
4',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(11,'{148293B1-10CB-4E99-8193-7DB00296EEA1}',51,52,'Category 2 Sub-Category
5',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(12,'{31513CA3-BAFC-4AD2-AF62-0F5846D6FE4E}',55,56,'Category 3 Sub-Category
1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(13,'{9291C86E-34F1-452C-B2BD-3B1C4C748AC9}',57,66,'Category 3 Sub-Category
2',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(14,'{666E889A-18B4-4C37-A6D2-A3993B909559}',67,74,'Category 3 Sub-Category
3',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(15,'{15D48846-7F7A-4508-890B-246BC6289A4C}',4,9,'Category 1 Sub-Category 1
Level 1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(16,'{A3F3112C-5FD3-4BA3-A7F9-10F7C8011148}',10,13,'Category 1 Sub-Category
1 Level 2',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(17,'{1281C30F-43FC-47C0-B9B5-418770B7E5F0}',20,23,'Category 2 Sub-Category
1 Level 1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(18,'{E71B0FFA-3C41-409A-919C-B89CE7BE5475}',28,33,'Category 2 Sub-Category
3 Level 1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(19,'{18F28612-BA8B-4944-B61D-11C0AA777296}',36,43,'Category 2 Sub-Category
4 Level 1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(20,'{328A96FB-1126-427C-9DB4-5F95E8ABE2ED}',44,49,'Category 2 Sub-Category
4 Level 2',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(21,'{68E4FFEA-79E2-4C02-8294-6247368E4C87}',58,65,'Category 3 Sub-Category
2 Level 1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(22,'{DEAD7400-9247-4CEC-8737-41954A665D21}',68,73,'Category 3 Sub-Category
3 Level 1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(23,'{41FDC1E7-3BD5-43FC-8376-FC06E3ACD27E}',5,6,'Category 1 Sub-Category 1
Level 1 Leaf 1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(24,'{2DD2A379-50F2-4499-BF32-C4B3F96DF95A}',7,8,'Category 1 Sub-Category 1
Level 1 Leaf 2',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(25,'{2FAD8E3E-D673-4900-AA14-0A2A37E656DE}',11,12,'Category 1 Sub-Category
1 Level 2 Leaf 1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(26,'{A2242A4A-9092-48BA-B8D3-2815038BD9B8}',21,22,'Category 2 Sub-Category
1 Level 1 Leaf 1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(27,'{3C8A7A18-F70C-4985-95E3-2F9A777F0A7C}',29,30,'Category 2 Sub-Category
3 Level 1 Leaf 1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(28,'{FE5969A8-5A1F-4B42-AE00-EF308B31991F}',31,32,'Category 2 Sub-Category
3 Level 1 Leaf 2',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(29,'{861CC5D0-FBAC-4A2D-888C-976EEF6A9F60}',37,38,'Category 2 Sub-Category
4 Level 1 Leaf 1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(30,'{87844F4D-8B0C-424D-B9BF-6D4756A5135C}',39,40,'Category 2 Sub-Category
4 Level 1 Leaf 2',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(31,'{CAC2F31B-F780-46BA-BE4E-89763C097DCC}',41,42,'Category 2 Sub-Category
4 Level 1 Leaf 3',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(32,'{5E4B81A1-C19A-4AB2-8FAC-574A1E89FD08}',45,46,'Category 2 Sub-Category
4 Level 2 Leaf 1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(33,'{1199A5A8-61C2-4FE9-8217-5E8623DA89A4}',47,48,'Category 2 Sub-Category
4 Level 2 Leaf 2',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(34,'{07ED9E31-F78C-4415-AFB0-FB63FDE62231}',59,60,'Category 3 Sub-Category
2 Level 1 Leaf 1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(35,'{DCA3C002-2107-44B4-8B34-E57C4E179E0D}',61,62,'Category 3 Sub-Category
2 Level 1 Leaf 2',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(36,'{94065E69-ED67-485C-94DF-57FD8CBA80F4}',63,64,'Category 3 Sub-Category
2 Level 1 Leaf 3',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(37,'{C4BBF01B-3911-4D3E-9DF9-6973C6B39A9F}',69,70,'Category 3 Sub-Category
3 Level 1 Leaf 1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name,
"Description", RecordType, PWord, ImageIndex, Path, DefaultEditStyle) VALUES
(38,'{25CF5B6F-33EC-4480-835D-490BDEFED6B9}',71,72,'Category 3 Sub-Category
3 Level 1 Leaf 2',NULL,NULL,NULL,NULL,NULL,NULL);

I am hoping that the above SQL will work in V5!!
--
Best regards

Steve

Steve,

<< I am hoping that the above SQL will work in V5!! >>

Yes, it will work in ElevateDB.  It treats single-column/single-row queries
just like normal values.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Thanks! Phew, that's good news

--
Best regards

Steve

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:E2003D82-210C-4EE5-A470-C404F03E8498@news.elevatesoft.com...
> Steve,
>
> << I am hoping that the above SQL will work in V5!! >>
>
> Yes, it will work in ElevateDB.  It treats single-column/single-row
> queries just like normal values.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>