On 13 Nov 2001, at 12:52, Ivan Prenosil wrote:

> If you append character higher than any other that can occur in zones
> (e.g. '~'), merge codes and zones, and order the result, you get
> something like that:
>
> select c || ' ' from tcode
> union all
> select z || '~' from tzone
> order by 1
>
> X1X 1X1
> X1X 1X1~
> X1X 1X~
> X1X 1~
> X1X 2E4
> X1X~
> X1~
> X3F 2Z7
> X~
>
> See the pattern ?
> Now try following command; is it similar to what you want ?
>
> select c,
> (select min(z||'~') from tzone where z||'~' > tcode.c)
> from tcode
>
> C
> ========== ===========
> X1X 1X1 X1X 1X1~
> X1X 2E4 X1X~
> X3F 2Z7 X~
>
>

Hmmmm.... that looks very hopeful, I will try naming the inner
select, and turn the whole into a linking view, then use the view to
link the tables together. Or is there an easier way of obtaining
additional fields from tzone? Fortunately I have control of the
application, and the data generation, so I can do something like
this quite easily.

Paul











Paul Schmidt
Tricat Technologies
paul@...
www.tricattechnologies.com