Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 8 of 8 total |
Using Rand for random data - DBISam 4 |
Wed, May 21 2014 2:29 AM | Permanent Link |
Adam H. | 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. |
Wed, May 21 2014 3:55 AM | Permanent Link |
Matthew Jones | 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 |
Wed, May 21 2014 4:39 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, May 21 2014 6:30 AM | Permanent Link |
Matthew Jones | 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 |
Wed, May 21 2014 7:01 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, May 21 2014 8:51 AM | Permanent Link |
Raul Team Elevate | 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 |
Wed, May 21 2014 10:23 PM | Permanent Link |
Adam H. | 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. |
Thu, May 22 2014 3:47 AM | Permanent Link |
Matthew Jones | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |