Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
Floor / Numeric comparisons |
Tue, Oct 1 2013 7:28 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | I wanted to reduce the amount of data in one of the tabkes I'm preparing for the demo of my query generator. I decided a sufficiently "random" method would be to divide the id column by 11 and wherever it was divisible exactly by 11 keep that. _ID is an autoinc (or more formally "_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,)
My first attempt was select floor(_ID / 11) , (_ID/11), * from companies where floor(_ID / 11) = (_ID/11) The first two columns are there to see what's going on because I was astounded that the entire table was selected. To give an idea the first row is 90909, 90909.1818181818 to me that says NOT EQUAL but there they are bold as brass! At this point I thought it looks as though when comparing a float to an integer the float is truncated. My second attempt was select floor(_ID / 11) , (_ID/11), * from companies where (floor(_ID / 11) - (_ID/11)) = 0 That seems to work. This select floor(_ID / 11) , (_ID/11), * from companies where ceil(_ID / 11) = (_ID/11) also seems to work. So it looks like a problem with FLOOR or my understanding of it. Roy Lambert |
Wed, Oct 2 2013 8:27 AM | Permanent Link |
Michael Riley ZilchWorks | Roy,
Why not just use MOD? if ID MOD(11) = 0 then Keep else Delete Michael Riley GySgt USMC (Ret) www.zilchworks.com |
Wed, Oct 2 2013 9:47 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Michael
>Why not just use MOD? > >if ID MOD(11) = 0 then Keep else Delete As Herman the German used to say "very interesting but ungeliveabubble" Or to put it another way if I'd been using Delphi I would have, but I was using SQL I even tried just to see and got ElevateDB Error #700 An error was found in the statement at line 1 and column 8 (ElevateDB Error #401 The function MOD does not exist in the schema Default) Roy Lambert |
Wed, Oct 2 2013 10:32 AM | Permanent Link |
Malcolm Taylor | Roy Lambert wrote:
> Michael > > > Why not just use MOD? > > > > if ID MOD(11) = 0 then Keep else Delete > > As Herman the German used to say "very interesting but > ungeliveabubble" > > Or to put it another way if I'd been using Delphi I would have, but I > was using SQL > > I even tried just to see and got > > ElevateDB Error #700 An error was found in the statement at line 1 > and column 8 (ElevateDB Error #401 The function MOD does not exist in > the schema Default) > That's strange, I use MOD in EDB SQL. The EDB SQL Reference has it under Arithmetc Operators. Or have I lost the place! Malcolm |
Wed, Oct 2 2013 11:37 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Malcolm
>That's strange, I use MOD in EDB SQL. The EDB SQL Reference has it >under Arithmetc Operators. >Or have I lost the place! You and Michael have got me worried now. Michael you could see from what he posted he was thinking navigational methods. My version of EDBManager's help has "Numeric Functions" in the contents, but "Arithmetic Operators" in the index. There the OLH does reference MOD. But I was treating it as a function and its an operator. So if I use the correct syntax eg select _ID mod 3 from companies and not select mod(_id / 3) from companies it does indeed work. My excuse is my brain is organised into classes of things that do stuff to other things (eg things that do stuff to numbers) rather than into stick the parameters in brackets or bung one either side. Also I know MOD but couldn't remember the bloody name and for a one off I thought FLOOR would work Roy |
Wed, Oct 2 2013 1:25 PM | Permanent Link |
Michael Riley ZilchWorks | Roy Lambert wrote:
>select _ID mod 3 from companies Sorry about the syntax mix-up. Michael Riley GySgt USMC (Ret) www.zilchworks.com |
Wed, Oct 2 2013 5:44 PM | Permanent Link |
Malcolm Taylor | No need for an excuse.
I keep using DIV .. then having to change it to TRUNC |
Tue, Oct 8 2013 5:28 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< At this point I thought it looks as though when comparing a float to an integer the float is truncated. >> Yeah, that's exactly what is going on. The automatic conversion in the bowels of the value comparison code, and I completely missed it. I'll have a fix for this in the next build (2.14 B2). Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |