Take the following table

CREATE TABLE MGR_DEV1.ADDRESSES (
   CUSTOMER_NBR DECIMAL (9, 0)  NOT NULL
 , ADDR_KEY DECIMAL (3, 0)  NOT NULL
 , STREET_ADDR_1 VARCHAR (40)  NOT NULL
 , STREET_ADDR_2 VARCHAR (40)
 , CITY VARCHAR (25)  NOT NULL
 , STATE CHARACTER (2)
 , ZIP_CODE DECIMAL (9, 0)
 , CONSTRAINT ADDR_PK PRIMARY KEY ( CUSTOMER_NBR, ADDR_KEY)
)
;

With input being a customer_nbr I want to check for an address/zip code
match using the following criteria:

If my 'input address' (being leading numerics only) matches then AVS1 = 'A',
else AVS1 = 'N'
If the 5-digit input zip code matches the first five numerics of ZIP_CODE
then AVS2 = 'Z' else AVS2 = 'N'
If AVS1 = 'A' and AVS2 = 'Z' then AVS = 'Y'
else if AVS1 = 'A' then AVS = 'A'
else if AVS2 = 'Z' then AVS = 'Z'
else AVS = 'N'

As an example, let's say we have a customer_nbr of 9876543210 that has two
addresses:
STREET_ADDR_1 = 12345 W COLFAX AVE
STREET_ADDR_2 IS NULL
ZIP_CODE = 802150000
and
STREET_ADDR_1 = C/O FIRSTBANK
STREET_ADDR_2 = PO BOX 1111
ZIP_CODE = 802160000

If we have an input addr of 12345 and input zip code of 80215 this should
return an AVS of 'Y'
If we have an input addr of 12345 and input zip code of 80214 this should
return an AVS of 'A'
If we have an input addr of 1234 and input zip code of 80215 this should
return an AVS of 'Z'
If we have an input addr of 1234 and input zip code of 80214 this should
return an AVS of 'N'

So far I have coded this (using a VALUES clause in place of the actual
ADDRESSES table):
select
   case
     when street_addr_1 like '12345%' or street_addr_2 like '12345%'
     then 'A'
     else 'N'
    end as AVS1
 , case
     when (zip_code / 10000) = 80215
     then 'Z'
     else 'N'
   end as AVS2
from (
   select STREET_ADDR_1, STREET_ADDR_2, ZIP_CODE
    from (
      values
        (9876543210., '12345 W COLFAX AVE', NULL, 802150000.)
      , (9876543210., 'C/O FIRSTBANK', 'PO BOX 1111', 802160000.)
      , (9999999999., '12345 MAIN STREET', NULL, 543210000.)
    ) AS ADDRESSES(CUSTOMER_NBR, STREET_ADDR_1, STREET_ADDR_2, ZIP_CODE)
    where customer_nbr = 9876543210
) as T
;

This returns:
AVS1 AVS2
---- ----
A    Z  
N    N  

So far so good.  But I'm not sure where to go from here.  I think I somehow
want to get a result table of some sort where, with the above example, it
would look like:

AVS
---
Y
N

Or, say I had the first result table like this:
AVS1 AVS2
---- ----
A    Z  
N    N  
A    N
N    Z

then my second results table would be
AVS
---
Y
N
A
Z

And again I would return 'Y' as my final result.

But if I had
AVS1 AVS2
---- ----
N    N  
A    N
N    Z

giving
AVS
---
N
A
Z

then I would return 'A'.

Hope this makes some amount of sense.
Thanks for any help!

Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO  USA