June 27, 2007 at 4:46 pm
We were having issues with using a function in a subquery (couldn't use the group by).
So we are trying to insert the data into a table, but get an error message saying
'A domain error occurred'
No SQL Server msg or error number, just that.
Here's the query
create table MPAResults_Temp
(
countOfLeads bigint,
Make varchar(50),
Zip varchar(10),
City varchar(50),
State varchar(10)
)
sp_help MPAResults_temp
drop table MPAResults_Temp
*/
truncate table MPAResults_Temp
go
insert into MPAResults_Temp (countOfLeads, make, zip, city, state)
select leadID, Make, MPATemp.Zip, MPATemp.City, MPATemp.State
from MPATemp (nolock)
inner join leads_sent on dbo.CalculateDistanceByZip(MPATemp.Zip ,leads_sent.zip) < 25
and datediff(month, leads_Sent.DateIN, getDate()) = 0
and Leads_Sent.Credited = 0
insert into MPAResults_Temp
select leadID , Make, MPATemp.Zip, MPATemp.City, MPATemp.State
from MPATemp (nolock)
inner join leads_rejected on dbo.CalculateDistanceByZip(MPATemp.Zip ,leads_rejected.zip) < 25
and datediff(month, leads_rejected.DateIN, getDate()) = 0
and leads_rejected.Nodealer = 1
select count(leadID) as numLeads, Make, Zip, City, State
from MPAResults_Temp
group by Make, Zip, City, State
June 27, 2007 at 6:37 pm
Please post how CalculateDistanceByZip is coded.
June 27, 2007 at 6:51 pm
create table MPATemp(
City varchar(50),
state varchar(50),
zip varchar(10)
)
insert into MPATemp values ('Los Angeles','CA','90025')
insert into MPATemp values ('New York','NY','10021')
insert into MPATemp values ('Chicago','IL','60657')
insert into MPATemp values ('Philadelphia','PA','19104')
insert into MPATemp values ('Washington','DC','20009')
insert into MPATemp values ('Detroit','MI','48219')
insert into MPATemp values ('Houston','TX','77004')
insert into MPATemp values ('Atlanta','GA','30318')
insert into MPATemp values ('Dallas','TX','75206')
insert into MPATemp values ('Boston','MA','2116')
insert into MPATemp values ('Riverside','CA','94507')
insert into MPATemp values ('Phoenix','AZ','85023')
insert into MPATemp values ('Minneapolis','MN','55407')
insert into MPATemp values ('Orange County','CA','92614')
insert into MPATemp values ('San Diego','CA','92103')
insert into MPATemp values ('Nassau','NY','12123')
insert into MPATemp values ('St. Louis','MO','63116')
insert into MPATemp values ('Baltimore','MD','21215')
insert into MPATemp values ('Seattle','WA','98103')
insert into MPATemp values ('Tampa','FL','33604')
insert into MPATemp values ('Oakland','CA','94606')
insert into MPATemp values ('Pittsburgh','PA','15219')
insert into MPATemp values ('Miami','FL','33135')
insert into MPATemp values ('Cleveland','OH','44102')
insert into MPATemp values ('Denver','CO','80203')
insert into MPATemp values ('Newark','NJ','7105')
insert into MPATemp values ('San Juan','PR','907')
insert into MPATemp values ('Portland','OR','97202')
insert into MPATemp values ('Kansas City','MO','64111')
insert into MPATemp values ('San Francisco','CA','94109')
insert into MPATemp values ('Fort Worth','TX','76010')
insert into MPATemp values ('San Jose','CA','95112')
insert into MPATemp values ('Cincinnati','OH','45202')
insert into MPATemp values ('Orlando','FL','32803')
insert into MPATemp values ('Sacramento','CA','95814')
insert into MPATemp values ('Fort Lauderdale','FL','33308')
insert into MPATemp values ('Indianapolis','IN','46227')
insert into MPATemp values ('San Antonio','TX','78207')
insert into MPATemp values ('Norfolk','VA','23505')
insert into MPATemp values ('Las Vegas','NV','89108')
insert into MPATemp values ('Columbus','OH','43201')
insert into MPATemp values ('Milwaukee','WI','53202')
insert into MPATemp values ('Charlotte','NC','28226')
insert into MPATemp values ('Bergen-Passaic','NJ','7055')
insert into MPATemp values ('New Orleans','LA','70115')
insert into MPATemp values ('Salt Lake City','UT','84103')
insert into MPATemp values ('Greensboro','NC','27407')
insert into MPATemp values ('Austin','TX','78745')
insert into MPATemp values ('Nashville','TN','37211')
insert into MPATemp values ('Providence','RI','2906')
insert into MPATemp values ('Raleigh','NC','27609')
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [DBO].[CalculateDistanceByZip] (@zip1 varchar(5), @zip2 varchar(5))
RETURNS FLOAT AS
BEGIN
DECLARE @Lat1 FLOAT
DECLARE @Lon1 FLOAT
DECLARE @Lat2 FLOAT
DECLARE @Lon2 FLOAT
select @Lat1 = Latitude, @Lon1 = Longitude
from ZipCodes where ZipCode = @zip1
select @Lat2 = Latitude, @Lon2 = Longitude
from ZipCodes where ZipCode = @zip2
RETURN (dbo.CalculateDistance(@Lat1, @Lon1, @Lat2, @Lon2 ))
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
June 27, 2007 at 7:09 pm
You're going to hate this, but it's likely even deeper, so we'll need the code for CalculateDistance. It's usually caused by functions receiving values they can't handle, such as passing a number to ACOS that isn't between -1 and +1.
June 27, 2007 at 7:11 pm
and CalculateDistance?
I suspect your trigonometric functions in the above is causing the domain error.
You have to make sure that parameters are within the ranges allowed in functions.
June 28, 2007 at 7:13 am
Try these two:
print acos(2)
and
select acos(2)
The first returns a message "a domain error occurred" without raising an error. I suppose it's a warning.
The second returns the same message and a recordset with no rows. Again no error is raised.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply