Can't seem to evaluate a floating field

[tmssales]

Let me try to explain my problem. I run a playlist automated script that uses a scripting language to run against a mySQL DB of songs. In my DB is the table "songlist" and a particular field of that table is floating variable called "balance".

I have several sql statements that successfully manipulate and write to this field. Typically the balance ends up less than zero as a decimal but my program only displays 6 places to the right of that decimal. Then I run the following queries.

D in this example is a data set which is a mysql database

D := Query('Select balance from songlist where songtype = ''S'' Order by balance ASC',[], True);

I then set the value of balance to an in script floating variable low_balance

low_balance = D['balance']

As a debug tool I can then write that low_balance variable to the screen to verify the contents of it.

Then I attempt to make a selection from the DB using this query

D := Query('select filename from songlist where songtype = ''S'' and balance = :low_balance Order by RAND() LIMIT 1', [low_blance]);

The only time this statement works (actually selects a filename) is when the actual calculated balance is 0.

When I debug this, as an example, I see that low_balance is = .896532 (or some similar figure, it could be 1.254389) and I have at least 100 records in my songlist with the identical value in the balance field but it never EQUATES low_balance and balance as equal eventhough low_blance was derived from balance as being set to equal it.

but, it always selects (or evaluates as true) when the balance is = 0.


I know this may be confusing but I thought I would give it a shot here

 

[PHV]

You may consider to play with integral numbers, ie multiply balance by 1 000 000 and coerce to long integer.
The single and double precision floating aren't designed for exact matches.
You may also consider testing the absolute value of the difference being less than, say, 0.000005

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tmssales]

I have thought about multiplying it by an integer and then trucnating it, but I still wind up with the catch-22 issue that I need to isolate that group of 100 or so songs and then randomly select one of them. How would I incorporate an integer solution into the where clause?

 

[PHV]

I don't know nor mySQL, nor your scripting language.
Something like this ?
D := Query('Select Int(1000000*balance) as ibal from songlist where songtype = ''S'' Order by 1 ASC',[], True);
low_ibal = D['ibal']
D := Query('select filename from songlist where songtype = ''S'' and Int(1000000*balance) = :low_ibal Order by RAND() LIMIT 1', [low_ibal]);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244