int/float with select math (bad subject, i know)

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

SQL Server 2000 Forums

SQL Server Development (2000)

int/float with select math (bad subject, i know)

Author

Topic

jrich523
Starting Member

4 Posts

Posted - 2006-03-11 : 17:33:29

here is the query..

SELECT item, gross, orderid, CAST((gross/100) as float) as rate FROM dbo.INV_Current WHERE (item = 'fr')

my alias rate always comes back as an int. the field gross was originally a int and i changed it to foat to see if that would help but nothing.
if i change that 100 to 100.0 it works.. the problem is the 100 comes from another table (which is also a float) but because SQL sees no need for a .0 on a float field it chops it off in the DB.. so even tho the gross field and the rate(that 100 up there from another table) are both float it doesnt seem to care.

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-03-11 : 18:57:44

Try using (field + 0.0)

-b

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-11 : 21:24:55

Make sure you understand how data is implicitly cast during calculations.

You should read about the subject in SQL Server Books Online, and run tests for yourself, like the code below.


select
	A= gross/divisior,
	B= convert(numeric(8,2),gross/divisior),
	C= convert(numeric(8,2),gross)/convert(numeric(8,2),divisior),
	D= convert(numeric(8,2),gross)/divisior,
	E= (gross+.0)/divisior,
	F= gross/(divisior+.0)
from
	(select  gross = 66, divisior = 100) a

Results:


A           B          C             D               E              F
----------- ---------- ------------- --------------- -------------- ---------------
0           .00        .66000000000  .6600000000000  .660000000000  .6600000000000

(1 row(s) affected)

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-03-12 : 02:01:06

Instead of

CAST((gross/100) as float)

I use the syntax

CAST(gross as float) / 100.0

(CAST the "100" too if its coming from somewhere else and with a different type)

Kristen

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-03-12 : 13:37:20

Is there any harm in using +0.0 instead of cast? I usually use the former just because it's less typing and (depending on the math) often introduces one less set of parenthesis.

-b

Kristen
Test

22859 Posts

Posted - 2006-03-13 : 03:55:46

My view would be that you are relying on a "side effect".

We try to code to avoid any such effects - because the next person coming along may not be aware that a particular action has a side effect - so if we do something deliberately to get a side-effect we always comment it, to that effect.

Which would be a PITA for something like +0.0

Also, I reckon I would be looking to get a very specific datatype change - not letting SQL decide for itself and possible do multiple type changes - in effect having to escalating the result at each stage.

When I programmed in C we used to have tools (like LINT) that would alert if you did something like MyIntColumn + '0.01' + 0.00 - whereas explicitly Casting the individual components would not raise any alerts.

So in summary! I would explicitly CAST or CONVERT something that was an inappropriate type for any given manipulation.

Kristen

jrich523
Starting Member

4 Posts

Posted - 2006-03-13 : 07:48:28

I did it as CAST(gross as float)/CAST(@punit as float) and it works great.. the @punit comes from my ASP app..
i agree with kristen about the side effects so i didnt bother with the + 0.0 but i recall learning that in college, i should have know that one.
thanks for all your help guys, im sure i'll be back for more :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-13 : 08:53:11

It's not really a "side effect", it's a question of implicit versus explicit conversions. You need to be wary of implicit conversions because:

a) they may not happen the way you want/expect
b) They may be occurring when you don't want them to (i.e., you made a mistake and it still "works", returning the wrong result)
c) the next guy who inherits your code isn't always sure what your intentions are when he sees your code w/o it explicitly stated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources