NAN - SQL qry to change?

[Barry G. Sumpter]

An excellent verbose answer.

Can we please add this to the wiki?

thanks

-----Original Message-----
From: valentina-bounces at lists.macserve.net
[mailto:valentina-bounces at lists.macserve.net] On Behalf Of Ruslan Zasukhin
Sent: Thursday, 1 November 2007 1:04 AM
To: valentina at lists.macserve.net
Subject: Re: NAN - SQL qry to change?

On 30/10/07 11:21 PM, "Barry G. Sumpter" <barry at noodlebox.com.au> wrote:

> Yeah, try doing that to 1 to 3 million records.
> 
> Can't select a NAN:
> SELECT fldDouble 
> FROM T where fldDouble = 'Nan' or '-nan' or '1.#uNaN'

Hi Barry,

1) Yes you cannot get SELECT only NANs

2) I did recommend do next:

    try query as

    SELECT fldDouble
    FROM T
    ORDER BY fldDouble.

    * This query must select all records of table. Right?
    * ORDER BY will sort all records.
        there is hope that NAN values will go as single group
        to the TOP or BOTTOM of result table.

    is this true? 
    If yes then you get easy enough way locate all such records.

    if no 

3) Then you need just

    SELECT fldDouble
    FROM T

And iterate all your 3 million records with check in RB code.

You think this will be long in time???
    No.

    3 millions * 8 bytes = 24MB

    this is < 1 sec to read from disk.
    and some time for CPU.

I.e. I'd expect time in few seconds maximum


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]


_______________________________________________
Valentina mailing list
Valentina at lists.macserve.net
http://lists.macserve.net/mailman/listinfo/valentina