Stored Procedure problem 1

[kyledunn]

Stored procedure code listed below. This trimmed down version of a stored procedure queries for a city from a zip code database and inserts the original data if no zip code is found or inserts the city from the zip code database if a zip code is found. Is there a way to insert the return data from the first query and not have to perform the second query to supply the city when the zip code is found?

CREATE PROCEDURE transform_as400_data AS
INSERT INTO DevelopmentWarehouse.dbo.Contract
(PurchaserCity)
SELECT
PurchaserCity = case (SELECT city FROM ZipCode.dbo.ziplist5 where zip = PurchaserZip)
WHEN Null then PurchaserCity
ELSE (SELECT City FROM ZipCode.dbo.ziplist5 where zip = PurchaserZip)
END
FROM DevelopmentStaging.dbo.Contract
GO

 

[Cagliostro]

how about quotas?
CREATE PROCEDURE transform_as400_data AS
INSERT INTO DevelopmentWarehouse.dbo.Contract
(PurchaserCity)
SELECT
PurchaserCity = case (SELECT city FROM ZipCode.dbo.ziplist5 where zip = PurchaserZip)
WHEN Null then 'PurchaserCity'
ELSE (SELECT City FROM ZipCode.dbo.ziplist5 where zip = PurchaserZip)
END
FROM DevelopmentStaging.dbo.Contract
GO Ion Filipski

filipski@excite.com

 

[kyledunn]

That is not the problem I am trying to solve. The routine below works fine. I am just trying to make it more efficient. The procedure INSERTS INTO the table DevelopmentWarehouse.dbo.Contract in column PurchaserCity the value returned from the SELECT statement. The SELECT statement assigns to the column the value returned from the CASE expression. The CASE expression first retrieves the city from the zipcode file if it has been fed a valid zipcode. This is the value I wish to assign in the ELSE clause of the CASE statement without having to do a requery. The goal is to reduce the queries needed to assign the city from the zipcode file to the PurchaserCity column from 2 queries to 1. Thank you for trying.

CREATE PROCEDURE transform_as400_data AS
INSERT INTO DevelopmentWarehouse.dbo.Contract
(PurchaserCity)
SELECT
PurchaserCity = case (SELECT city FROM ZipCode.dbo.ziplist5 where zip = PurchaserZip)
WHEN Null then PurchaserCity
ELSE (SELECT City FROM ZipCode.dbo.ziplist5 where zip = PurchaserZip)
END
FROM DevelopmentStaging.dbo.Contract
GO

 

[Cagliostro]

I don't understand the last line of your code. In my opinion is easier to:

CREATE PROCEDURE transform_as400_data AS
if PurchaseZip in(select zip from ZipCode.dbo.ziplist5)
begin
INSERT INTO DevelopmentWarehouse.dbo.Contract
(PurchaserCity)
SELECT city FROM ZipCode.dbo.ziplist5
where zip = PurchaserZip
end else
begin
INSERT INTO DevelopmentWarehouse.dbo.Contract
SELECT City FROM ZipCode.dbo.ziplist5
where zip = PurchaserZip
end
GO Ion Filipski

filipski@excite.com

 

[kyledunn]

The last line allows me to apply unique data cleansing rules to various kinds of data during an INSERT INTO when moving data from one database to another. I'll post at the end of this comment a little larger sample (the complete script is much larger) to offer a better demonstration of what I am doing. I am cleansing the data as I move it from a staging area into a data warehouse and there are more than 50 variables in the actual INSERT INTO statement. The code I submitted before was only a small portion of the INSERT INTO command just for discussion. My objective is to batch read and insert while cleansing the various fields according to unique business rules that were appropriate to the data. This was the only approach I could think of so far to apply 30 different business rules to one batch process. I agree with you that your approach is easier but only considering that there is a limited amount of fields. I'm not sure how I would apply the approach to a large number of data fields. I am very interested in any efficient approach I could use to move data from the staging area to the data warehouse. I'm not married to any particular approach. My current version is with activeX scripting. With the amount of data and the various cleansing rules it takes a whopping 30 hours to complete. I am looking for a performance boost with stored procedures. I want to make the process as fast as possible. Thank you for your comments.

 

[kyledunn]

Sorry, I didn't post the additional sample. Here it is:

CREATE PROCEDURE transform_as400 AS
INSERT INTO DevelopmentWarehouse.dbo.Contract
(
PurchaserCity,
PurchaserState,
PurchaserZip,
PurchaserCounty,
PurchaserSex,
PurchaserBirthdate,
PurchaserAgeRange,
)
SELECT
PurchCity = case (SELECT zip FROM ZipCode.dbo.ziplist5 where zip = PurchZip and [Pref?] = 'P')
WHEN Null then PurchCity
ELSE (SELECT City FROM ZipCode.dbo.ziplist5 where zip = PurchZip and [Pref?] = 'P')
END,
PurchState = case (SELECT State FROM State.dbo.State where State = PurchState)
WHEN Null then 'U'
ELSE PurchState
END,
PurchZip = case (SELECT zip FROM ZipCode.dbo.ziplist5 where zip = PurchZip and [Pref?] = 'P')
WHEN Null then 'Unknown'
ELSE PurchZip
END,
PurchaserCounty = case (SELECT zip FROM ZipCode.dbo.ziplist5 where zip = PurchZip and [Pref?] = 'P')
WHEN Null then 'Unknown'
ELSE (SELECT county FROM ZipCode.dbo.ziplist5 where zip = PurchZip and [Pref?] = 'P')
END,
PurchSex = case PurchSex
WHEN 'M' then 'M'
WHEN 'F' then 'F'
ELSE 'U'
END,
PurchBirthdate = case LEN(PurchBirthdate)
WHEN 8 then
case isdate(Substring(PurchBirthdate,1,4)+'-'+Substring(PurchBirthdate,5,2)+'-'+Substring(PurchBirthdate,7,2))
WHEN 1 then Convert(datetime,Substring(PurchBirthdate,1,4)+'-'+Substring(PurchBirthdate,5,2)+'-'+Substring(PurchBirthdate,7,2))
ELSE Null
END
ELSE Null
END,
PurchaserAgeRange = case LEN(PurchBirthdate)
WHEN 8 then
case isdate(Substring(PurchBirthdate,1,4)+'-'+Substring(PurchBirthdate,5,2)+'-'+Substring(PurchBirthdate,7,2))
WHEN 1 then (DateDiff(Year,Convert(datetime,Substring(PurchBirthdate,1,4)+'-'+Substring(PurchBirthdate,5,2)+'-'+Substring(PurchBirthdate,7,2)),getdate())/5)+1
ELSE 1
END
ELSE 1
END
FROM DevelopmentStaging.dbo.cntrct
GO

 

[tlbroadbent]

It seems to me that you could greatly improve the efficiency of the procedure by using a LEFT JOIN to the zip and state tables instead of multiple sub-queries. I recommend the following code.

CREATE PROCEDURE transform_as400 AS
INSERT INTO DevelopmentWarehouse.dbo.Contract
(
PurchaserCity,
PurchaserState,
PurchaserZip,
PurchaserCounty,
PurchaserSex,
PurchaserBirthdate,
PurchaserAgeRange,
)
SELECT
PurchCity = case z.zip
WHEN Null then PurchCity
ELSE z.City
END,
PurchState = case s.State
WHEN Null then 'U'
ELSE PurchState
END,
PurchZip = case z.zip
WHEN Null then 'Unknown'
ELSE PurchZip
END,
PurchaserCounty = case z.zip
WHEN Null then 'Unknown'
ELSE z.County
END,
PurchSex = case PurchSex
WHEN 'M' then 'M'
WHEN 'F' then 'F'
ELSE 'U'
END,
PurchBirthdate = case LEN(PurchBirthdate)
WHEN 8 then
case isdate(Substring(PurchBirthdate,1,4)+'-'+Substring(PurchBirthdate,5,2)+'-'+Substring(PurchBirthdate,7,2))
WHEN 1 then Convert(datetime,Substring(PurchBirthdate,1,4)+'-'+Substring(PurchBirthdate,5,2)+'-'+Substring(PurchBirthdate,7,2))
ELSE Null
END
ELSE Null
END,
PurchaserAgeRange = case LEN(PurchBirthdate)
WHEN 8 then
case isdate(Substring(PurchBirthdate,1,4)+'-'+Substring(PurchBirthdate,5,2)+'-'+Substring(PurchBirthdate,7,2))
WHEN 1 then (DateDiff(Year,Convert(datetime,Substring(PurchBirthdate,1,4)+'-'+Substring(PurchBirthdate,5,2)+'-'+Substring(PurchBirthdate,7,2)),getdate())/5)+1
ELSE 1
END
ELSE 1
END
FROM DevelopmentStaging.dbo.cntrct As c
Left Join ZipCode.dbo.ziplist5 As z
On c.PurchZip = z.Zip
Left Join State.dbo.State As s
On c.PurchState=s.State
where z.[Pref?] = 'P'
GO Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[kyledunn]

Terry,

I appreciate your input. I am struggling getting it to work. Here is a description of my confusion. I went to the query analyzer to test and view the results of a left join. While connected to the DevelopmentStaging database I executed the following query:

select * from cntrct as c
left join ZipCode.dbo.ziplist5 as z
on c.PurchZip = z.Zip

My sample database has 30 records and it returned all 30 records as expected but I also expected the zipcodes that were in the cntrct table that were not in the ziplist5 table to appear as NULL in the joined table. They still appear as the original value from the cntrct table.

I queried the ziplist5 table to assure that the zipcodes I expected to be NULL were in fact not in the ziplist5 table.

The next concern I had is with

where z.[Pref?] = 'P'

because I don't want to exclude any records from the join I thought I would alter the code to be

select * from cntrct as c
left join ZipCode.dbo.ziplist5 as z
on c.PurchZip = z.Zip and z.[Pref?] = 'P'

If I am understanding correctly then the result set would return all records in the cntrct table and set to NULL any zipcodes that don't have a corresponding zip code in the ziplist5 table and don't match the zipcode preference setting in the ziplist5 table. Is this correct? Can you tell me why the zipcodes that don't match are not being set to NULL? Thanks for your help.

Kyle

 

[tlbroadbent]

You are right to be concerned about the WHERE clause. Change it to read as follows.

where z.[Pref?] = 'P' Or z.[Pref?] IS NULL Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[kyledunn]

Terry,

I now realize that I was looking at the Purchaser Zip column instead of the joined Zip column from the ziplist5 table which indeed did have NULL for all not matching values and adding the "and z.[Pref?] = 'P'" to the on phrase did exclude those additional records where more than one Preference Flag existed for the same zip code. This works great, a very good solution you offered. Thanks!