Access/SQL query returning "invalid floating point operation"

I suspect that the MinSchedThisWeek column is not numeric. Can you post here the table structure of the tables involved

Hi Chaau,

It is.

See attached.

Thanks,
Ben
untitled.png

So, the error comes from one of these:
TempPassThroughQry
TempPassThrough1Qry
tblZipCodesDistanceQry

Post here the syntax of these queries

Why don't you run all of the subqueries one by one?
That should quickly determine the source of the error which probably in an invalid math operation like Sqrt(-2) or a custom function of yours receiving unexpected parameters.

/gustav

Your query can be easily replaced with this one:

SELECT A.* 
FROM TmpEmpAvailSearch A
	INNER JOIN TempPassThroughQry T ON A.ID = T.EmployeeID 
	INNER JOIN TempPassThrough1Qry T1 ON A.ID = T1.EmployeesID 
	INNER JOIN tblZipCodesDistanceQry Z ON A.Zip = Z.ZipTo
WHERE ISNULL(A.MinSchedThisWeek,0) < 2280 
ORDER BY A.TmpEmpAvailSearch.LastName, A.TmpEmpAvailSearch.FirstName, A.TmpEmpAvailSearch.WeekDayOrder;

Select allOpen in new window

Hi Experts,

Following Gustav's advise, I was able to track down the issue to the following portion.

Select ZipTo from tblZipCodesDistanceQry

Select allOpen in new window

This turns out to be really a SQL issue.
See attached.

Perhaps this requires some explanation.
We have a table of (USA) zip codes containing Latitude and Longitude
See attached #2.
Then we have the following view that returns all zip codes within a distance range from a given zip code.

SELECT     z2.ZipCode AS ZipFrom, z1.ZipCode AS ZipTo, ACOS(SIN(z1.Latitude * 0.017453293) * SIN(z2.Latitude * 0.017453293) + COS(z1.Latitude * 0.017453293) 
                      * COS(z2.Latitude * 0.017453293) * COS(z2.Longitude * 0.017453293 - z1.Longitude * 0.017453293)) * 3956 AS Distance
FROM         dbo.tblZipCodes AS z1 CROSS JOIN
                      dbo.tblZipCodes AS z2
WHERE     (z2.ZipCode = '[b]11211[/b]') AND (ACOS(SIN(z1.Latitude * 0.017453293) * SIN(z2.Latitude * 0.017453293) + COS(z1.Latitude * 0.017453293) 
                      * COS(z2.Latitude * 0.017453293) * COS(z2.Longitude * 0.017453293 - z1.Longitude * 0.017453293)) * 3956 [b]<= 5[/b])

Select allOpen in new window

The two values (11211 and <=5) are just the example when the error occurs.

Let me know if you need me to upload the entire table.

Thanks,
Ben
Untitled.png
Untitled1.png

@Vitor,

I first tested your version and got attached.

However at this point that we know where the error comes from, I dont think fixing this particular syntax will help the problem.

Thanks,
Ben
untitled.png

I think you need to review the query for distance. First of all, you have duplicate Zip Codes. The CROSS JOIN view will give you the Cartesian Product when you ZipFrom is one that is duplicated. I think you need to use  WHERE z2.ZipCode = '11211' and z2.Primary = 1

Also check that the longitude/latitude values are correct for the whole table. If there are nulls or values outside the range fix them.

Hi,

I think you need to use  WHERE z2.ZipCode = '11211' and z2.Primary = 1

The view is in use for quite a while, will have to check if I dont lose anything by doing that.

If there are nulls or values outside the range fix them.

I added the following to the criteria and didnt help.

WHERE     (z2.ZipCode = '11211') AND (ACOS(SIN(z1.Latitude * 0.017453293) * SIN(z2.Latitude * 0.017453293) + COS(z1.Latitude * 0.017453293) 
                      * COS(z2.Latitude * 0.017453293) * COS(z2.Longitude * 0.017453293 - z1.Longitude * 0.017453293)) * 3956 <= 5) AND (NOT (z1.Latitude IS NULL)) AND 
                      (NOT (z1.Longitude IS NULL)) AND (NOT (z2.Latitude IS NULL)) AND (NOT (z2.Longitude IS NULL))

Select allOpen in new window

What would you consider outside the range?

Thanks,
Ben

I think it is these values:

    Latitude : max/min +90 to -90

    Longitude : max/min +180 to -180

BTW, if you use a modern version of SQL Server you can use the geography:

geography::Point(z2.Latitude, z2.Longitude, 4326).STDistance(geography::Point(z1.Latitude, z1.Longitude, 4326)) * 0.000621371 AS GeoDistance

Select allOpen in new window

The spatial methods of the geography data type have built-in checks for errors and will give an error straight away if your coordinates are incorrect

See attached the values in range of min/max.

I modified to exclude 0's but still getting this error.

What else should I do?

Thanks,
Ben
Untitled.png

if you use a modern version of SQL Server

Currently we have 2005/8 in place, dont think that will work.

Thanks,
Ben

SQL Server works not like a programming language. When you add " AND (NOT (z1.Latitude IS NULL)) AND (NOT (z1.Longitude IS NULL)) AND (NOT (z2.Latitude IS NULL)) AND (NOT (z2.Longitude IS NULL))" SQL Server will continue to execute the ACOS(COS...) stuff if it thinks that it must. I guess you need to find the offending ZipCode and see if you can fix it somehow.

SQL Server will continue to execute the ACOS(COS...) stuff if it thinks that it must.

And how about if I change instead of select from tabzipcodes, I use from (select * from tblzipcodes where zip is not null), will that help?

I guess you need to find the offending ZipCode

This is the main question, how can I get to that..?

Thanks,
Ben

To find it create a cursor and run it for each record separately.
have you tried geography yet?

Wow this crashed my SQL instance!!!

never had this yet.

See attached.

Should I choose debug or close program?

Any chance of recovering my unsaved sql docs..?

Thanks,
Ben
Untitled.png

Sorry about that. BTW, it is the SSMS that was crashed, not the SQL Server instance.
If you had the auto-recover option set (it is on by default) your work will be recovered

Ok, now do I have anything by choosing debug over close program?

Thanks,
Ben

I think you need to close

If you had the auto-recover option set (it is on by default) your work will be recovered

Where do I find that in my version?

See attached.

Thanks,
Ben
Untitled.png

Hi Experts,

Just updating, I tried changing for

SELECT     z2.ZipCode AS ZipFrom, z1.ZipCode AS ZipTo, ACOS(SIN(z1.Latitude * 0.017453293) * SIN(z2.Latitude * 0.017453293) + COS(z1.Latitude * 0.017453293) 
                      * COS(z2.Latitude * 0.017453293) * COS(z2.Longitude * 0.017453293 - z1.Longitude * 0.017453293)) * 3956 AS Distance
FROM         (SELECT     ID, ZipCode, [Primary], City, State, CountyNumber, CountyName, Borough, Latitude, Longitude
                       FROM          dbo.tblZipCodes
                       WHERE      (Latitude IS NOT NULL) AND (Longitude IS NOT NULL)) AS z1 CROSS JOIN
                          (SELECT     ID, ZipCode, [Primary], City, State, CountyNumber, CountyName, Borough, Latitude, Longitude
                            FROM          dbo.tblZipCodes AS tblZipCodes_1
                            WHERE      (Latitude IS NOT NULL) AND (Longitude IS NOT NULL)) AS z2
WHERE     (z2.ZipCode = '11211') AND (ACOS(SIN(z1.Latitude * 0.017453293) * SIN(z2.Latitude * 0.017453293) + COS(z1.Latitude * 0.017453293) 
                      * COS(z2.Latitude * 0.017453293) * COS(z2.Longitude * 0.017453293 - z1.Longitude * 0.017453293)) * 3956 <= 5)

Select allOpen in new window

and still error occured.

See attached.

Any idea what else can cause it besides of null?

Thanks,
Ben
Untitled.png

You're working the queries in a design view in Access and SSMS. Should the errors also happens when you run it as a simple query text?

@Vitor,

In that case I get to see the results, however a different error message pops up.

Could not figure out from the results set where is the prob.

See attached.

Thanks,
Ben
Untitled.png

I love that error message.

/gustav

You can't have alias in WHERE clause. Remove that "AS z2".

WHERE      (Latitude IS NOT NULL) AND (Longitude IS NOT NULL)) AS z2

@Gustav,

What are you so in love with that..:)

@Vitor,

that alias belongs to the inner select, and by removing that I get the following error.

"Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'm'.
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'WHERE'."

Thanks,
Ben

I love the wording - and the fact that it tells nothing.

/gustav

I see, but what about the floating point message, this should be some indication.
In fact, this is the message we get in our front end app.

Thanks,
Ben

It's the message you already have from the ODBC driver.

There is only one thing to do: Remove small parts from the calculation until no fail. Then debug the offending expression/calculation.

/gustav

that alias belongs to the inner select, and by removing that I get the following error.

Sorry. You're right. I need new lens or at least wake up :O

@chaau,

To find it create a cursor and run it for each record separately.

Can you post some code example to perform this?

Thanks,
Ben

Ben, did you try to isolate the commands to find where's the issue?

@Vitor,

Yes, I tried that too and I cant get it.

Tried for example the following.

SELECT     z2.ZipCode AS ZipFrom, z1.ZipCode AS ZipTo, SIN(z1.Latitude * 0.017453293),SIN(z2.Latitude * 0.017453293),COS(z1.Latitude * 0.017453293),COS(z2.Latitude * 0.017453293),COS(z2.Longitude * 0.017453293 - z1.Longitude * 0.017453293),COS(z2.Latitude * 0.017453293) * COS(z2.Longitude * 0.017453293 - z1.Longitude * 0.017453293) * 3956 --ACOS(SIN(z1.Latitude * 0.017453293) * SIN(z2.Latitude * 0.017453293) + COS(z1.Latitude * 0.017453293) 
                      --* COS(z2.Latitude * 0.017453293) * COS(z2.Longitude * 0.017453293 - z1.Longitude * 0.017453293)) * 3956 AS Distance
FROM         dbo.tblZipCodes AS z1 CROSS JOIN
                      dbo.tblZipCodes AS z2
WHERE     (z2.ZipCode = '11211')

Select allOpen in new window

See attached.

Thanks,
Ben
Untitled.png

The idea of isolating the code is to try to get only the part that is originating error so we can work on it.

@Vitor,

I got that, ans as I mentioned was trying to get to the peace of code that is causing the problem, but could not accomplish it.

Thanks,
Ben

So the error only occurs when the query is complete. Running only parts of it doesn't raise errors.

Hi,

Not Exactly, its starts running and in middle you get the error.

See attachment.

Thanks,
Ben
Untitled.png

That means somehow in a row you have a "bad" value. i.e. a value that you can use in the calculations.
You need to find that row and that value so you can get rid of it.

@Vitor,

You need to find that row and that value so you can get rid of it.

Correct, can you give me an example how to run this in a loop record by record?

Thanks,
Ben

Hi Ben,

maybe it could be a value which is out of the range of the float datatype. All of the functions like SIN, COS etc. uses float for input and output. So the longitude and latitude values in your table should also have this datatype. I've rewritten your SQL statement to use a CTE instead which makes it easier to read and handle (could not test it):

WITH qryZip AS
(
	SELECT ID ZipCode
			,CAST(Longitude  AS float) * (PI() / 180) AS LongitudePi180
			,SIN(CAST(Latitude  AS float) * (PI() / 180)) AS SINLatitudePi180,
			,COS(CAST(Latitude  AS float) * (PI() / 180)) AS COSLatitudePi180
	FROM dbo.tblZipCodes
	WHERE (Latitude IS NOT NULL)
	AND   (Longitude IS NOT NULL)
) 
SELECT	Z1.ZipCode AS ZipFrom,
		Z2.ZipCode AS ZipTo,
		ACOS(Z1.SINLatitudePi180 * Z2.SINLatitudePi180 +
			 Z1.COSLatitudePi180 * Z2.COSLatitudePi180 *
			 COS(Z2.LongitudePi180 - Z1.LongitudePi180)) * 3956 AS Distance
			 
FROM 		qryZip AS Z1
CROSS JOIN	qryZip AS Z2
WHERE	(Z2.ZipCode = '11211')
AND		(ACOS(Z1.SINLatitudePi180 * Z2.SINLatitudePi180 +
			 Z1.COSLatitudePi180 * Z2.COSLatitudePi180 *
			 COS(Z2.LongitudePi180 - Z1.LongitudePi180)) * 3956) <= 5

Select allOpen in new window

A CTE is especially useful if you need to use the same query more than once which is the case here. I would also recommend to save the values of this calculation:

ACOS(Z1.SINLatitudePi180 * Z2.SINLatitudePi180 + 
 Z1.COSLatitudePi180 * Z2.COSLatitudePi180 *
 COS(Z2.LongitudePi180 - Z1.LongitudePi180))

Select allOpen in new window

as a persistent calculated column in the original table so it will calculated whenever a record is changed and saved so your final query don't need to calculate that for each row when the query runs, should make the result a lot faster.

You should also always reduce the number of columns to the really needed ones which makes your query faster especially in case of a cross join.

If you have a problem in your data that will of course not solve your error. For example, ACOS only accepts values from -1.0 to +1.0, so if the result of the calculation in the brackets doesn't fit it would throw an error.

Another thing is that if you add a constant value in a calculation the execution plan shows that SQL Server casts the value to a datatype it thinks it's the correct one which is not always be correct. Here you don't need that as you can use the PI function to get the value you want (and it is more exact).

So the first thing to check is if Latitude and Longitude are all in a possible range of values as chaau told you above. That's a simple query so you do not need to check each record one by one. If you have found and changed the wrong values, you should add a check constraint to these columns to make sure they'll never get out of their ranges again.

Cheers,

Christian

thanks for jumping in, Christian

just a comment ... since  multiplying by pi and then dividing by 180 converts degrees ° to Radians, perhaps SINLatitudePi180 (etc) could be better named SinRadian

Hi crystal,

I'm not an expert in geographic calculations...:-)
I googled for the constant value and saw that it is PI/180 so that's why I named it so - as in all the names there is no hint if it is radians or degrees I think that's not so important but just for better documentation of the value - why not? I'm a friend of long, self-documenting names.

Cheers,

Christian

Welcome Bit,

(I know once you pitch in, sooner or later we will have an answer..-:)

Just tested your code and its not returning all records compared to original SQL, in fact its just returning 10% of the results expected, in some cases even less.

I am attaching the full table so you can test it & perhaps analyze where the error is coming from.

I would also recommend to save the values of this calculation..

Do you mean, I should save the results of the self joined table in another table which will be 60k *60k?

PS. Running the original SQL with the data attached will give you the error with zipcode '12458' (instead of 11211 OP).

Thanks,
Ben
tblZipCodes.zip

Hi Ben,

the data shows at least that there should be no issue with the values as there is no value which exceeds the longitude/langitude limits.

It would be great if you would also script the CREATE TABLE statement and post it here to be sure to use the same datatypes as you.

Saving the results: I meant to save the result of the constant calculation into this table, not the record result of the query. If you calculate a sinus or cosinus value with a constant multiplicator etc. the result will always be the same, a city doesn't change it's longitude/latitude values and if, it would need to calculate the result you need only once so it is the most performant way to save these constant results into the table as calculated persisted column. The values which would change here are the distances so you only need to add the last calculations to the query, not the sinus/cosinus etc. calculations.

Cheers,

Christian

Hi Bit,

Below is the create table script.

CREATE TABLE [dbo].[tblZipCodes](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ZipCode] [varchar](5) NULL,
	[Primary] [bit] NULL,
	[City] [varchar](28) NULL,
	[State] [varchar](2) NULL,
	[CountyNumber] [varchar](3) NULL,
	[CountyName] [varchar](25) NULL,
	[Borough] [varchar](50) NULL,
	[Latitude] [float] NULL,
	[Longitude] [float] NULL,
 CONSTRAINT [PK_tblZipCodes] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Select allOpen in new window

I saw you wrote

save the values of this calculation

ACOS(Z1.SINLatitudePi180 * Z2.SINLatitudePi180 ...
and this is taking Z1 and Z2 into the calculation therefore I asked...however what you're saying make sense, in other words save whatever possible and thus minimizing the unnecessary calculations later.

Thanks,
Ben

Hi Ben,

yes, you're right, was my fault, of course I meant the constant values only, the ACOS value contains results from both queries and cannot be used.

So the CREATE TABLE statement would look like this:

CREATE TABLE [dbo].[tblZipCodes](
	[ID] [int] NOT NULL,
	[ZipCode] [varchar](5) NULL,
	[Primary] [bit] NULL,
	[City] [varchar](28) NULL,
	[State] [varchar](2) NULL,
	[CountyNumber] [varchar](3) NULL,
	[CountyName] [varchar](25) NULL,
	[Borough] [varchar](50) NULL,
	[Latitude] [float] NULL,
	[Longitude] [float] NULL,
	[SINLatitudePi180]  AS (SIN(CONVERT([float],[Latitude],(0))*(PI()/(180)))) PERSISTED,
	[COSLatitudePi180]  AS (COS(CONVERT([float],[Latitude],(0))*(PI()/(180)))) PERSISTED,
	[LongitudePi180]  AS (CONVERT([float],[Longitude],(0))*(PI()/(180))) PERSISTED,
 CONSTRAINT [PK_tblZipCodes] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Select allOpen in new window

This adds 3 persisted calculated columns to the table.

The error why the query above did not return the same number of records took me a little bit to find it out...:-)
It was a really stupid error... I simply forgot a comma between "ID" and "ZipCode" and so "ZipCode" was the alias for the ID, so it calculates the distance to the ID...:-) (and the ID column is not even needed...).

But as you provided the table and data I could successfully rewrite that (using the calculated columns) so now it is this (tested):

;WITH qryZip AS
(
	SELECT ZipCode
		,LongitudePi180
		,SINLatitudePi180
		,COSLatitudePi180
	FROM dbo.tblZipCodes
	WHERE (ISNULL( Latitude,0) <> 0)
	AND	 (ISNULL(Longitude,0) <> 0)
) 
SELECT	ZF.ZipCode AS ZipFrom,
		ZT.ZipCode AS ZipTo,
		ACOS(ZF.SINLatitudePi180 * ZT.SINLatitudePi180 +
			ZF.COSLatitudePi180 * ZT.COSLatitudePi180 *
			COS(ZT.LongitudePi180 - ZF.LongitudePi180)) * 3956 AS Distance			 
FROM 		qryZip AS ZF
CROSS JOIN	qryZip AS ZT
WHERE	(ZT.ZipCode = '11211')
AND		(ACOS(ZF.SINLatitudePi180 * ZT.SINLatitudePi180 +
			 ZF.COSLatitudePi180 * ZT.COSLatitudePi180 *
			 COS(ZT.LongitudePi180 - ZF.LongitudePi180)) * 3956) <= 5

Select allOpen in new window

I also sometimes got the domain error where I guess that's the ACOS function which throws it depending on how the data is assembled so this is a variant which tests if the value is in the right borders:

;WITH	   qryZip AS
(
	SELECT  ZipCode,
		   LongitudePi180,
		   SINLatitudePi180,
		   COSLatitudePi180
	FROM	    dbo.tblZipCodes
	WHERE	   (ISNULL( Latitude,0) <> 0)
		    AND (ISNULL(Longitude,0) <> 0)
)
, qryZip2 AS
(
	SELECT ZF.ZipCode AS ZipFrom, ZT.ZipCode AS ZipTo,
		  ZT.SINLatitudePi180 * ZF.SINLatitudePi180
		+ ZT.COSLatitudePi180 * ZF.COSLatitudePi180
		* COS(ZF.LongitudePi180 - ZT.LongitudePi180) AS D1
	FROM	  qryZip AS ZT
	CROSS JOIN qryZip AS ZF
	WHERE ZT.SINLatitudePi180 * ZF.SINLatitudePi180
		+ ZT.COSLatitudePi180 * ZF.COSLatitudePi180
		* COS(ZF.LongitudePi180 - ZT.LongitudePi180) BETWEEN -1 AND 1
)
SELECT Z.ZipFrom, Z.ZipTo, ACOS(Z.D1) *3956 AS Distance
FROM qryZip2 AS Z
WHERE  (Z.ZipFrom = '11211') 
AND
((ACOS(Z.D1) * 3956) <=5)
ORDER BY (ACOS(Z.D1))

Select allOpen in new window

These queries worked for me in all cases (using SQL Server 2005).

Cheers,

Christian

Hi Bit,

Looks like we are getting closer..just needed some minor touch ups.

1- Did you tested with 12458? (as stated above, the example giving the error is this, instead of 11211 OP).
See attached the error I'm still getting with both versions.

2- What is the syntax to add those 3 columns to existing table?

3- Wondering why there is a need for 2 computed columns for Latitude and only 1 for Longitude?-:)

Thanks,
Ben
Untitled.png

Hi Bitsqueezer!!

You got it!!

The query works perfectly.

Thanks a million
Ben

You're welcome..:-)
Glad if I could help you.