really a basic question 1

[kevinluebb]

Yes, I know this is pretty basic but it's Monday and I must have destroyed that braincell over the weekend.

I have a 5 char zipcode in table1

table2 has a 10 char zipcode (5-4)

this would work???
table1.zipcode like table2.zipcode

 

[rac2]

No, but this will -

table1.zipcode = SUBSTRING(table2.zipcode,1,5)

 

[kevinluebb]

I think you're right on....but it's really kinda weird cause I'm not getting anything (and I've done a couple of manual checks of the data so I should get a couple hits).

INSERT INTO KMLDB..HIMARK
SELECT PROVIDERID,PRIMARYADDRESSLINE,CITY,STATECODE,ZIPCODE,
COUNTY,PHONENUMBER,SUITENUMBER,0,0,0,0,'X','X',0,0,'X','X',0,0,'X','X'
FROM KMLDB..HIGHMARK LEFT JOIN SITE ON ZIP LIKE SUBSTRING(ZIPCODE,1,5)

really a quick, easy, basic hunk o code.

 

[kevinluebb]

no problem now.....just another "monday morning brain burp" I guess. Sure this will work! It just takes a long time to run!

 

[desi2002]

i am not sure but i think it will work if u use rtrim, ltrim functions.

 

[tlbroadbent]

When you use the substring, use = condition rather than Like. Your query should run faster.

INSERT INTO KMLDB..HIMARK
SELECT
PROVIDERID, PRIMARYADDRESSLINE,
CITY, STATECODE, ZIPCODE,
COUNTY, PHONENUMBER, SUITENUMBER,
0,0,0,0,'X','X',0,
0,'X','X',0,0,'X','X'
FROM KMLDB..HIGHMARK
LEFT JOIN SITE ON ZIP = SUBSTRING(ZIPCODE,1,5)

I may be able to make additional recommendations if I knew which columns are coming from each table in the JOIN. If you want to qualify the columns names with the table names and repost the query, I'll let you know if I see any additional improvements you could make.
Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[kevinluebb]

Thanks to all. The Monday fog has cleared and with your help this task should be completed within the hour.
Thanks again