By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,287 Members | 1,287 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,287 IT Pros & Developers. It's quick & easy.

query for VISA AVS (address verification service)

P: n/a
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
Feb 23 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Moments later, I think I'm getting closer:

with
T as
(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 InnerT)
select
case
when AVS1 = 'A' and AVS2 = 'Z' then 'Y'
when AVS1 = 'A' then 'A'
when AVS2 = 'Z' then 'Z'
else 'N'
end as AVS
from T
;

This returns:
AVS
---
Y
N

Now I just need to do some sort of column function to return just the 'Y'.
I'll probably get there after I go to lunch. Just posting it helps me
think.

Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
>>Frank Swarbrick<Fr*************@efirstbank.com02/23/07 1:37 PM >>>
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
Feb 23 '07 #2

P: n/a
I have two issues.
1) "when street_addr_1 like '12345%' or street_addr_2 like '12345%'"
If your input was 1234, then
"when street_addr_1 like '1234%' or street_addr_2 like '1234%'"
satisfy street_addr_1:'12345 W COLFAX AVE'.
2) I thougt that if there are multiple address for a customer,
you want first AVS in the sequence of 'Y' -'A' -'Z' -'N'.

Here is a trial example considering these two points.
-------------------- Commands Entered ------------------------------
WITH
TestData(CUSTOMER_NBR, STREET_ADDR_1, STREET_ADDR_2, ZIP_CODE) AS (
values
(9876543210., '12345 W COLFAX AVE', NULL, 802150000.)
, (9876543210., 'C/O FIRSTBANK', 'PO BOX 1111', 802160000.)
, (9999999999., '12345 MAIN STREET', NULL, 543210000.)
)
select TRANSLATE(MIN(TRANSLATE(
case
when AVS1 = 'A' and AVS2 = 'Z' then 'Y'
when AVS1 = 'A' then 'A'
when AVS2 = 'Z' then 'Z'
else 'N'
end
, '1234', 'YAZN')), 'YAZN', '1234') as AVS
from (select
case
when SUBSTR(street_addr_1,1
,LENGTH(street_addr_1)
-LENGTH(LTRIM(TRANSLATE(street_addr_1,''
,'0123456789')))) =
'12345'
or SUBSTR(street_addr_2,1
,LENGTH(street_addr_2)
-LENGTH(LTRIM(TRANSLATE(street_addr_2,''
,'0123456789')))) =
'12345' then
'A'
else 'N'
end as AVS1
, case
when (zip_code / 10000) = 80215
then 'Z'
else 'N'
end as AVS2
from TestData
where customer_nbr = 9876543210
) AS S;
--------------------------------------------------------------------

AVS
---
Y

1 record(s) selected.

Feb 24 '07 #3

P: n/a
If AVS1 and AVS2 was not necessary in final results(only used to keep
interim results), here is another example.
-------------------- Commands Entered ------------------------------
WITH
TestData(CUSTOMER_NBR, STREET_ADDR_1, STREET_ADDR_2, ZIP_CODE) AS (
values
(9876543210., '12345 W COLFAX AVE', NULL, 802150000.)
, (9876543210., 'C/O FIRSTBANK', 'PO BOX 1111', 802160000.)
, (9999999999., '12345 MAIN STREET', NULL, 543210000.)
)
select SUBSTR('YAZN', MIN(
case
when SUBSTR(street_addr_1,1
,LENGTH(street_addr_1)
-LENGTH(LTRIM(TRANSLATE(street_addr_1,''
,'0123456789')))) =
'12345'
or SUBSTR(street_addr_2,1
,LENGTH(street_addr_2)
-LENGTH(LTRIM(TRANSLATE(street_addr_2,''
,'0123456789')))) = '12345'
then 1
else 3
end
+
case
when (zip_code / 10000) = 80215 then
0
else 1
end)
,1) as AVS
from TestData
where customer_nbr = 9876543210
;
--------------------------------------------------------------------

AVS
---
Y

1 record(s) selected.

Feb 24 '07 #4

P: n/a
Tonkuma! This is just short of perfect. Thank you very much! Very clever,
but I never would have thought of it. What made it occur to you to do the
SUBSTR thing?

All I had to do is change the zip code check to when "TRUNC(zip_code /
10000,0) = zip".

Very cool stuff.
Thanks again,
Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
>>Tonkuma<to*****@jp.ibm.com02/24/07 4:34 AM >>>
If AVS1 and AVS2 was not necessary in final results(only used to keep
interim results), here is another example.
-------------------- Commands Entered ------------------------------
WITH
TestData(CUSTOMER_NBR, STREET_ADDR_1, STREET_ADDR_2, ZIP_CODE) AS (
values
(9876543210., '12345 W COLFAX AVE', NULL, 802150000.)
, (9876543210., 'C/O FIRSTBANK', 'PO BOX 1111', 802160000.)
, (9999999999., '12345 MAIN STREET', NULL, 543210000.)
)
select SUBSTR('YAZN', MIN(
case
when SUBSTR(street_addr_1,1
,LENGTH(street_addr_1)
-LENGTH(LTRIM(TRANSLATE(street_addr_1,''
,'0123456789')))) =
'12345'
or SUBSTR(street_addr_2,1
,LENGTH(street_addr_2)
-LENGTH(LTRIM(TRANSLATE(street_addr_2,''
,'0123456789')))) = '12345'
then 1
else 3
end
+
case
when (zip_code / 10000) = 80215 then
0
else 1
end)
,1) as AVS
from TestData
where customer_nbr = 9876543210
;
--------------------------------------------------------------------

AVS
---
Y

1 record(s) selected.

Feb 26 '07 #5

P: n/a
>All I had to do is change the zip code check to when "TRUNC(zip_code / 10000,0) = zip". <<
Lower 5 digits of all sample zip_codes were zero.
Then if zip_code is 802150000, result of zip_code / 10000 is "80215.".
When comparing this result with INTEGER 80215, INTEGER 80215 will be
converted to DECIMAL and both values are equal.
If it is not guaranteed that lower 5 digits of zip_codes are zero,
it must be necessary to use TRANCATE as you showed.

Feb 27 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.