Re: Nested sort, trying again

Bob Stearns wrote:
> 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,

What does namex, etc... mean? I am guessing namex is a (the?) name (is it globally unique?) for the location, rightx is a (the?) name for the location to the right of it, and leftx to the left. Right sofar?

No abovex, belowx, behindx, infrontofx, so lineair space?

> 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.

In the example I don't see what you mean by level. Which level are you talking about?

> If I didn't have to have changeable names,
> I could define the data in alphabetic order.

No. In a table, the rows are supposed to be unordered. You can present them in an order you would like to see, but to /define/ them in an order would be breaking the rules.

But maybe I am not understanding your question right.

> Given that I can't do that,

Indeed you can't - not because you have to have changeable names, but because (we pretend) rows are unordered.

> is there (relatively) easy
> way to present the data in a sorted manner?

Earlier, Hugo Kornelis wrote:

 > Sure!
 >
 > SELECT   NAMEX
 > FROM     IS3.LOCATIONS
 > WHERE    whatever your current where clause is
 > ORDER BY NAMEX

Is there something wrong with that?

> 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.

That's a different problem. One at a time. Let's postpone the history-problem
for now and make sure can get all
data we need for the current situation.

> Because of the arbitrary number of levels,

You speak of levels in your problem description, but there are none yet in your data.
Maybe that is the real problem:

        "how to model a hierarchy of locations?"

> 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),
Just nitpicking: try to use positives where possible. If you really want this meaning say e.g. "obsolete".

More nitpicking:

• Why character(25) for Y or N?
  • No date?

But, as I said, ISTM history is a problem we can focus on later.

> 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 - 19:15:48 CEST