Returning an empty dataset

I just needed the column list of the resulting query for a field picklist,
so I surrounded the original query AABB by

SELECT * FROM
(AABB) T
WHERE FALSE

But unfortunately this fails if the original query AABB was segmented...
(multiple SQL statements like creating an index and then returning data
using a select statement)

So I decided to change all the SELECT keywords into SELECT TOP 0 and then
returning the query result.

But hey, this is strange: I get ALL records back? SELECT TOP 1 returns one
record, SELECTR TOP 2 returns two records, but SELECT TOP 0 returns all
records?

Bug or WAD?

Regards - hans

SELECT TOP 1 * FROM "EMET_EM"

takes 16 ms

SELECT * FROM "EMET_EM" WHERE FALSE

takes 33703 ms

(Table with 1.6M records, 12 columns)

Any explanation?

Regards - Hans

Hans Hasenack wrote:
> SELECT TOP 1 * FROM "EMET_EM"
>
> takes 16 ms
>
> SELECT * FROM "EMET_EM" WHERE FALSE
>
> takes 33703 ms
>
> (Table with 1.6M records, 12 columns)
>
> Any explanation?
>
> Regards - Hans
>
>
If you have an AUTOINC field, try the following:

SELECT * FROM Table WHERE RecordID = 0

Where RecordID is your AUTOINC field.

Best regards,

Shane

Unfortunately the SQL statement is entered by the end user, so I need the
fieldlist before i can determine wheter there is an autoincfield ;>

regards - Hans

there is SELECT ALL ...

there is SELECT DISTINCT ...

there is SELECT TOP n ..

so why isn't there SELECT NONE ...

Just because it isn't in the SQL 2003 defenition?

I'd even like it better if TnxQuery.Prepare would initialize FieldDefs but
hey, That is moved to the v3 version

Regards - Hans

Hans,

> SELECT TOP 1 * FROM "EMET_EM"
>
> takes 16 ms

Because the TOP clause makes the result non-live.

> SELECT * FROM "EMET_EM" WHERE FALSE
>
> takes 33703 ms
>
> (Table with 1.6M records, 12 columns)
>
> Any explanation?

I think this is only a problem with "live" queries. Request a non-live query
(set RequestLive to false) and you should get the result instantaneously.

Ole

Hans,

> so why isn't there SELECT NONE ...
>
> Just because it isn't in the SQL 2003 defenition?

The problem you've experienced has nothing to do with the SQL:2003
specification.

See my other reply.

Ole

Ole, the original problem is that I do not really want to execute a query,
but I actually only want the field list it produces. Since FieldDefs.Update
executes the entire query (and thus may take a long time), I turned to
changing the query SQL statement in tat it produces no results, which
produces my results a lot faster.


While doing this, I investigated some alternate options and came through
this path.

Anyway you were right, requesting the query with LiveDataset=False took only
0 ms

Hans