Lists: Multiple Zip Codes

You could certainly do it with a manual edit. Would an example like this work for you?

select a.customer_no
from dbo.t_customer c
join dbo.t_address a
on c.primary_address_no = a.address_no
where left(a.postal_code,5) in ('63116', '63108')
and c.inactive = 1

That's pretty cool.  Thanks, I've never done Manual Edit before.  Great help!

Happy to help!

Best regards,

Matt

Or you could try a.postal_code like '631%'

and that would get you all postal codes that start with 631

Anyone figure out how to do this in T-Keyword with a long pull down list?

I haven't fully tested it yet, but I created a t_keyword with the following which seems to work.

Detail Tbl
vs_address
Detail Col
substring(!.postal_code,1,5)
Ref Tbl
lv_zip
Ref Idcol
postal_code
Ref Desccol
postal_code

lv_zip view is simply
Select distinct substring(postal_code,1,5) AS postal_code
From t_address

I came up with much the same.

Created new Search Object in Tessitrua.

T_Keyword

Description: Postal_Code USA Zip (5 Digits)
Data Type: String
Edit Mask: None
Detail TBL: VS_address
Detail Col: left(!.postal_code,5)
Ref Tbl: TR_CITYSTATE
Ref Idcol: zip5
Ref Desccol: zip5+ ' ' + city+', '+state
Ref Where: country = 1
Categrory: Address Information
Use For List: List Only
Multiple Value: [Not Checked]
Control Group: [Blank]
Custom Id: [Blank]
Custom Limit: [Blank]
Custom Required: [Not Checked]
Custom Default Value: [Blank]
Inc In Basic Search: [Not Checked]
Keyword: Description: US Zip Codes the first 5 characters of the postal code on an address where country = USA

We are also thinking of adding a first 3 char of US Postal Codes varient.

I came up with much the same.

Created new Search Object in Tessitrua.

T_Keyword

Description: Postal_Code USA Zip (5 Digits)
Data Type: String
Edit Mask: None
Detail TBL: VS_address
Detail Col: left(!.postal_code,5)
Ref Tbl: TR_CITYSTATE
Ref Idcol: zip5
Ref Desccol: zip5+ ' ' + city+', '+state
Ref Where: country = 1
Categrory: Address Information
Use For List: List Only
Multiple Value: [Not Checked]
Control Group: [Blank]
Custom Id: [Blank]
Custom Limit: [Blank]
Custom Required: [Not Checked]
Custom Default Value: [Blank]
Inc In Basic Search: [Not Checked]
Keyword: Description: US Zip Codes the first 5 characters of the postal code on an address where country = USA

We are also thinking of adding a first 3 char of US Postal Codes varient.

Looks like your version is a little more elegant. Shall try it out, thanks Tom!

Actually I forgot that I didn't use tr_citystate in our system because it was lacking a lot of zips for some reason and I was short on time. So if you use it, I would recommend doing a little comparison between it and what you have in t_address first.

Tom -- Thank you!  Your instructions just saved me a lot of time.  Margaret

Tom,  Would you provide the T_Keyword script like you did here when you set up the first 3 char of US Postal Codes varient?  We don't have an IT staff so if you provide the detail, we all can 'copy'.  Thanks!