Hi,

I was just wondering if someone could point out to me where I'm going
wrong with this SQL:

Select field1, field2
from mytable
where rand(10) < 5

(To get a random set of records - about 50%)

This returns no records at all.

However choosing

select field1, rand(10)
from mytable

gives me a resultset with the 2nd field full of random numbers as expected.

Just wondering if anyone can see what I'm missing?

Cheers

Adam.

Adam H. wrote:
>
> Select field1, field2
> from mytable
> where rand(10) < 5
>
> Just wondering if anyone can see what I'm missing?

I guess the SQL parser in DBISAM isn't sophisticated enough to handle
that. Part of my moving to ElevateDB for new stuff is to get the better
SQL capabilities. It's easier than one might think to migrate!

(Sorry, didn't help much did I?)


--

Matthew Jones

Adam

>I was just wondering if someone could point out to me where I'm going
>wrong with this SQL:
>
>Select field1, field2
>from mytable
>where rand(10) < 5
>
>(To get a random set of records - about 50%)
>
>This returns no records at all.
>
>However choosing
>
>select field1, rand(10)
>from mytable
>
>gives me a resultset with the 2nd field full of random numbers as expected.
>
>Just wondering if anyone can see what I'm missing?

I'm guessing here but I'd say that in the second case rand HAS to be fired for every row, in the first case it only has to be fired once and by luck you're getting a value above 4 each time.

I just did a quick test and replaced 10 with 10000 and got a good result.

The only approach I can think of right now is to create a memory table, use the primary key of mytable and rand(10) - (call it half)  to create the data and then join that with mytable where half < 5

Roy Lambert

Roy Lambert wrote:

> The only approach I can think of right now is to create a memory
> table, use the primary key of mytable and rand(10) - (call it half)
> to create the data and then join that with mytable where half < 5

How about a combination?

Select field1, field2, Rand(10) as TheRandom
from mytable
where TheRandom < 5

Just a guess but if that gets calculated each row, then the Where now
has a named field to work with...

--

Matthew Jones

Matthew


That was my first thought and its been so long since I actively used DBISAM I tried it in DBSys but it doesn't work

However, its good to see you getting into the ElevateDB mindset

Roy Lambert

On 5/21/2014 4:39 AM, Roy Lambert wrote:
> The only approach I can think of right now is to create a memory table, use the primary key of mytable and rand(10) - (call it half)  to create the data and then join that with mytable where half < 5
>
See here for an example of this :

http://www.elevatesoft.com/forums?action=view&category=dbisam&id=dbisam_sql&page=1&msg=12865#12865


Raul

Thanks all for your replies.

Memory table is probably my best bet. In this example I was just using a
select but in the actual application it was more like:

update mytable
set field = 'value= where rand(10) < 5

.... which didn't work either. Memory table it will be.

As for going across to EDB in most of our new applications we're
designing using EDB already.

Unfortunately this is one of our applications which is significantly
large, under constant developemnt and utilise DBISam specific statements
that aren't supported in EDB that I doubt I will ever manage to it
across to EDB. We'd have to shut shop for literally months to merge over
(thousands of SQL statements, many complicated with into memory tables,
etc), and considering changes and updates are released every few days I
can't see it happening.

So... memory tables (which I secretly love anyway) to the rescue.
Just wanted to make sure it wasn't something silly I was doing wrong.

Cheers

Adam.

Adam H. wrote:

> (thousands of SQL statements, many complicated with into memory
> tables, etc)

FWIW, I created myself a TSQLBuilder object. I got fed up with manually
creating SQL and getting it slightly wrong. It was a fantastic relief
when I did. Of course it then got slightly complicated with the various
extras like TOP, but when it came to the EDB switch it meant that I
actually had to change the UPDATE SQL format in only one place, which
was really nice. I do understand the issue though - I too have projects
that are only going to get converted in a major update, if at all.

--

Matthew Jones