(Twilight zone) math functions in SQL and ASP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

(Twilight zone) math functions in SQL and ASP

Author

Topic

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2012-11-03 : 03:24:37

Hi.

So here's the situation. I have a database with GPS coordinates. They are stored as nchar data (a string).

example:
table1 (latitude, longitude)
44.23,-78.44
46.443,-78.443

I also have a second table with GPS coordinates stored as varchar(10) data - which is also a string.

table2 (latitude, longitude)
43.33,-78.44
45.46,-78.223

I'm not sure how I ended up with two different types of the same data, but I did. I call a script that calculates the distance between your home city (table1) to a destination city (table 2). It does this with a mathematical formula between the two sets of GPS coordinates.

Example:
SELECT latitude,longitude FROM table1

v1 = objrs("latitude")
v2 = objrs("longitude")

SELECT latitude, longitude FROM table 2
v3 = objrs("latitude")
v4 = objrs("longitude")

(Result: v1 = 44.23, v2= -78.44, v3 =43.33, v4= -78.44)

I could then add, subtract, multiply, etc.

Tonight I changed table2 from varchar(10) to decimal(10,8) format. All non-numeric characters were removed, all whitespaces removed.

After this change, my search results were all null. I tested the code and found that whenever I tried to perform a math equation between v1,v2 and v3,v4 it would result in a null value.

My first question is how is it that I could do math functions between NCHAR and VARCHAR strings which aren't actual numerical values but I can't do a math function between an NCHAR string and a DECIMAL value?

Now here's the weird Twilight Zone results...

The problem is in the decimal table (table2) not the nchar table.

For example:
strSQL = "SELECT * FROM table2"

v1 = (objrs("latitude"))
v2 = (objrs("longitude"))
h = v1 * 3

results in a "type mismatch" error.

I'm at a loss to understand how I can multiply an nchar value but I can't multiple a decimal value.

To solve the issue I do this:
SELECT * FROM table2"

w1 = cdbl(objrs("latitude"))
w2 = cdbl(objrs("longitude"))

h = w1 * 3
response.write h

and it works!

I am very confused tonight. Why am I converting decimal records for them to work ... as decimal values, when it should be the nchar being converted into decimals, no?

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2012-11-03 : 05:28:58

Just to add...
When doing a loop to go through each of my 3000+ recordsets, I had a condition:

if latitude = 0 or longitude = 0 then <don't display Google Map condition>

This condition was ALWAYS met even if the latitude/longitude was not zero (eg. 45.333 and -79.333). Only when I put in the CDBL did it work.

WTF is the point of using the decimal in SQL if your ASP server thinks these aren't even numbers and you have to convert them all the time?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-03 : 08:31:20

This has to do with Asp than SQL Server. SQL Server will not let you multiply character data type with another character data type or anything else; i.e., it will not do implicit conversion to numerical types for multiply operator.

I suspect the language that you are using does an implicit conversion to numeric types in one case and does not do so in some other cases.

SQL experts recommend that you always store the data using the appropriate types - in this case numeric data types such as float, decimal etc.

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2012-11-03 : 17:42:57

Okay but why can I read in two recordsets from a table of decimal values and not be able to multiple them by an actual number? They are after all decimal.

Confused.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources