Subject Re: [ib-support] Key of varying length problem
Author Ivan Prenosil
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
X3F 2Z7

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

========== ===========
X1X 1X1 X1X 1X1~
X1X 2E4 X1X~
X3F 2Z7 X~


> I have two tables, postal_code, and postal_zone, since I am in
> Canada a postal code looks like this: X1X 1X1
> The postal_zone contains a key, that is part of a postal code, it
> might be X1X 1X1, X1X 1X, X1X 1, X1X, X1 or X
> say I have the following zones:
> 1 X1X 1X1
> 2 X1X 1X
> 3 X1X 1
> 4 X1X
> 5 X1
> 6 X
> I have the following postal codes
> X1X 1X1 must match 1 but not 2,3,4,5 or 6
> X1X 2E4 must match 4 but not 1,2,3,5 or 6
> X3F 2Z7 must match 6 but not 1,2,3,4 or 5
> I am at a loss on how to do this, I can't use another key, because
> a new zone could be added at any time, and I don't want to have to
> scan the Postal code file and relink it. I will if I have to, but would
> rather not.