Nested sort, trying again
From: Bob Stearns <rstearns1241_at_charter.net>
Date: Thu, 29 Sep 2005 11:31:00 -0400
Message-ID: <UOT_e.7500$wg7.6034_at_fe06.lga>
I have a table (see below for ddl) which implements a set of locations for an organization, from the whole organization to shelves in a particular room. Users never see the primary key, only the NAMEX, usually as a drop down selection list, sometimes a single selection other times a multiselection. Users, being users, want the ability to change names and to see the names in sorted order at each level. If I didn't have to have changeable names, I could define the data in alphabetic order. Given that I can't do that, is there (relatively) easy way to present the data in a sorted manner? This is the same as seeing you directory tree in Windows explorer with the name chosen as the sort key. The root directory is sorted in filename order, then every directory under in it also in name order, down an arbitrary number of levels. In my examples, users attach people, functions, things etc. to locations at any level, can add additional locations at any level (dividing up a room, adding shelving, buying a new building, etc.). Because history is important, locations can only become inactive, never deleted. Because of the arbitrary number of levels, I can not join any other table to achieve the sort I want.
)
GO
ALTER TABLE IS3.LOCATIONS
GO
ALTER TABLE IS3.LOCATIONS
GO Received on Thu Sep 29 2005 - 17:31:00 CEST
Date: Thu, 29 Sep 2005 11:31:00 -0400
Message-ID: <UOT_e.7500$wg7.6034_at_fe06.lga>
I have a table (see below for ddl) which implements a set of locations for an organization, from the whole organization to shelves in a particular room. Users never see the primary key, only the NAMEX, usually as a drop down selection list, sometimes a single selection other times a multiselection. Users, being users, want the ability to change names and to see the names in sorted order at each level. If I didn't have to have changeable names, I could define the data in alphabetic order. Given that I can't do that, is there (relatively) easy way to present the data in a sorted manner? This is the same as seeing you directory tree in Windows explorer with the name chosen as the sort key. The root directory is sorted in filename order, then every directory under in it also in name order, down an arbitrary number of levels. In my examples, users attach people, functions, things etc. to locations at any level, can add additional locations at any level (dividing up a room, adding shelving, buying a new building, etc.). Because history is important, locations can only become inactive, never deleted. Because of the arbitrary number of levels, I can not join any other table to achieve the sort I want.
CREATE TABLE IS3.LOCATIONS (
LOC_ID INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 1, INCREMENT BY 1, CACHE 20) NOT NULL,
NAMEX VARCHAR(30) NOT NULL,
LEFTX INTEGER NOT NULL,
RIGHTX INTEGER NOT NULL,
ENTITY_ID INTEGER NOT NULL,
INACTIVE CHARACTER(25),
PRIMARY KEY(LOC_ID)
)
GO
ALTER TABLE IS3.LOCATIONS
ADD CONSTRAINT INACTIVE
CHECK (INACTIVE in ('Y','N'))
GO
ALTER TABLE IS3.LOCATIONS
ADD CONSTRAINT SQL040717191150980
FOREIGN KEY(ENTITY_ID)
REFERENCES IS3.ENTITIES_PUB(ENTITY_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
GO Received on Thu Sep 29 2005 - 17:31:00 CEST