Hey,

I want to add some new zip codes to a table. Some zip codes exist, but they are not
comprehensive. I am adding a zip code in the select statement below from the zipcode_unique
table. Currently, I have six static values in my SQL:

CUSTOMER_SERVICE_REP, UNDERWRITING_REP, LOSS_CONTROL_COUNSELOR,
MARKETING_REP, AUDIT_COMPANY_ID, AUDITOR_ID

insert into zip_code(ZIP, CUSTOMER_SERVICE_REP, UNDERWRITING_REP, LOSS_CONTROL_COUNSELOR,
MARKETING_REP, STATE, COUNTY_CODE, AUDIT_COMPANY_ID, AUDITOR_ID, CREATE_USER)
select zcu.ZIPCODE, 1, 1, 1, 1, zcu.STATE, zcu.COUNTYFIPS, 6, 1, 'AUBREYT'
from zipcode_unique zcu
left join zip_code z on
(zcu.ZIPCODE = z.ZIP)
group by zcu.ZIPCODE, zcu.STATE, zcu.COUNTYFIPS, z.ZIP
having ((z.ZIP) is NULL);

I want to replace the 1,1,1,1 and 6,1 in the select statement with existing values from
my zipcode table by going to the z table, and finding a state and countyfips code that
match the zip code that is being added, and return them to this statement.

Should I do create a procedure to obtain all 6 values ? If so, how would I insert the
values into this statement ?

If I setup a function for each of the six values, it seems like a lot of processing, as
I'll have to go back to the table six times for each zip code added. I'd like to find a
simpler way.

Thanks, Aubrey

Oracle LazyDBA home page