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

Roy,

Why not just use MOD?

if ID MOD(11) = 0 then Keep else Delete

Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

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

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

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

Roy Lambert wrote:

>select  _ID mod 3 from companies

Sorry about the syntax mix-up.  

Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

No need for an excuse.
I keep using DIV .. then having to change it to TRUNC

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