[Last Call] Learn how to a build a cloud-first strategyRegister Now


Meaning of this line of code

Posted on 2011-04-25
Medium Priority
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
ID: 35464267
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

ID: 35464289
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

Author Comment

ID: 35464387
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)  
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 75

Accepted Solution

Anthony Perkins earned 2000 total points
ID: 35464401
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.


Author Comment

ID: 35464413
i'll try it

Expert Comment

ID: 35464850
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
ID: 35465006
It use for to get specific round of value

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

829 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