Meaning of this line of code

Posted on 2011-04-25
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 ...
 AND (CAST(floor( sm.weight  * 1000000000000000000) AS bigint) %100000)  <=  0

Open in new window

Question by:Camillia
    LVL 75

    Expert Comment

    by:Anthony Perkins
    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.
    LVL 11

    Expert Comment

    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
    LVL 7

    Author Comment

    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)  
    LVL 75

    Accepted Solution

    I would be more inclined to try something like this:
    WHERE sm.weight  = CAST(sm.weight AS numeric (38,10))  -- change scale and precision appropriately.

    LVL 7

    Author Comment

    i'll try it
    LVL 8

    Expert Comment

    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.

    LVL 21

    Expert Comment

    by:Alpesh Patel
    It use for to get specific round of value

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now