---------- Original Message -----------
From: "sboydlns" <sboydlns@...>

> > alter table gl add concat_key computed by (company || account || division

||

> > department);
> >
> > create asc index ix_gl_concat on gl computed by (company || account ||

division

> > || department);
> >
> > select * from gl where concat_key > ('CO' || 'ACCT');
> >
>
> I thought about something like that but these are all Varchar fields,
> so concatenating them together isn't going to work. I suppose that
> they could be changed to Char. I will have to play around with it to
> see if that will do what I want.

------- End of Original Message -------

You don't have to change the original fields to CHAR; try:

alter table gl add concat_key computed by (cast(company as char(4)) || cast
(account as char(6)) || cast(division as char(8)) || cast(department as char
(4)));

create asc index ix_gl_concat on gl computed by (cast(company as char(4)) ||
cast(account as char(6)) || cast(division as char(8)) || cast(department as char
(4)));

select * from gl where concat_key > (cast('CO' as char(4)) || cast('ACCT' as
char(6)));

The other option is to delimit with a character greater than any of the
characters normally found in the name. 'A,B' > 'AZ,B' whereas 'AB' < 'AZB'. You
could keep varchars at that point, and just comma-delimit (or whatever
character is required, but I would assume you only use plain A-Z, 0-1 in those
key fields.) Note that I'm not *absolutely* sure of this solution, it just
feels right off the cuff.

Either way, you don't have to change existing datatypes, nor storing more data,
except for more index data.

If positive numeric fields are involved, use lpad(field, maxlength, '0') for
that portion, if you don't want to sort by alpha. But given that "anything >
ACCT' can't be for logical reasons (why is SCIFI > ACCT?), I'm assuming this is
more for 'physical' reasons, and as long as it always sorts the same way,
you'll reach your goal.

(My guess: export in chunks, maybe for pages of green-bar paper? Do they also
need select FIRST N on this? Could they accomplish the same thing with FIRST N
SKIP M, to get consistent output sets, without remembering "where they were" so
they can continue from that point?)

-Philip