Nested sort, trying again

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
.

Relevant Pages

  • Nested sort
    ... I have a table 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. ... ALTER TABLE IS3.LOCATIONS ...
    (comp.databases.theory)
  • SQL Server confused about primary keys. So am I.
    ... We are generating a script to convert a client's database to Sql ... ALTER TABLE dbo.Categories ADD ... CONSTRAINT PK_Categories PRIMARY KEY CLUSTERED ...
    (microsoft.public.sqlserver.server)
  • Re: Altering Table - Adding Primary Key With References
    ... The CREATE TABLE command work fine. ... > let the primary key of the Child table be the referecing field. ... > can't do it with the ALTER TABLE command in VFP8. ... > ALTER TABLE Morechildren ADD FOREIGN KEY cid TAG cid1 REFERENCES Parents ...
    (microsoft.public.fox.vfp.dbc)
  • Re: Altering Table - Adding Primary Key With References
    ... ALTER TABLE Morechildren ADD PRIMARY KEY cid TAG cid REFERENCES Parents TAG id ... VFP does not support PRIMARY KEY constraint with TAG and REFERENCES ...
    (microsoft.public.fox.vfp.dbc)
  • Re: Alter table help
    ... alter table table_name add constraint constraint_name primary key ... > "Tom Pennington" wrote in message ... >> Okay, I'm trying to modify a tables primary key, actually, I'm trying to ...
    (microsoft.public.sqlserver.programming)