We help IT Professionals succeed at work.

Meaning of this line of code

Camillia
Camillia asked
on
353 Views
Last Modified: 2012-05-11
I have this line of SQL 2000. I coded it last year and now not sure what it means.

sm.weight is foat.

it can have values like 3 OR 2.72099995613098 OR 2


select sm.weight,  AND (CAST(floor( sm.weight  * 1000000000000000000) AS bigint) %100000)  

from ...
where
 AND (CAST(floor( sm.weight  * 1000000000000000000) AS bigint) %100000)  <=  0

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2012

Commented:
Since float is an approximate value you are trying to determine if the value stored is approximate or not.  So instead of 2.123 you may have 2.1229999999999999.  With this arithmetic acrobatics you can confirm this is the case.

It would be far better if you just quit using float and instead used numeric, that way you would not have to resort to this perfromant killing calculation every time.

Commented:
consider sm.weight = 2.72099995613098

 (CAST(floor( sm.weight  * 1000000000000000000) AS bigint) %100000)  
~ ( sm.weight  * 1000000000000000000) = A = 2,720,999,956,130,980,000
~ floor(A) = B = 2,720,999,956,130,980,000 (floor function return the largest integer)
~ CAST(b as bigint) = C = 2,720,999,956,130,980,000
~C%100000 = C mod 100000 = 80000

Is the AND is typo error? it's should be boolean operator instead of act as function
CERTIFIED EXPERT

Author

Commented:
this is a vendor provided database. I can't change it. Have to use that float.

That "AND" is part of the where clause i had (copied it by mistake).

I changed the clause to this, would this cause an issue? I changed it because i was getting an arthimatic overlfow error;

(CAST(floor(cast( sm.weight as int)  * 1000000000000000000) AS bigint) %100000)  
CERTIFIED EXPERT
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
i'll try it
G Trurab KhanSnr. Development Manager
CERTIFIED EXPERT

Commented:
CAST(floor( @x  * 1000000000000000000) AS bigint)  multiplies the float value with 1000000000000000000
floor function returns the closese integer less than the multiplication produst
and convert it to a bigint

%100000 takes the reminder from division of above value by 100000.

Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
It use for to get specific round of value
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.