Hi guys,

I'm using 2.04B4 and trying to insert/select a range of records into a temporary table

The basic query goes like this
SELECT
 SUM(SaleHistory.Quantity) AS ordering_field,
 Product.Code AS ProductCode
 FROM Product
 INNER JOIN SaleHistory
    ON Product.Code = SaleHistory.Code
 GROUP BY Product.Code
 ORDER BY ordering_field DESC
 RANGE 1 TO 10

However, when inserting into a temporary table I get the following error
ElevateDB Error #700 An error was found in the statement at line 9 and column 3 (Expected end of expression but instead found ORDER)

CREATE TEMPORARY TABLE MYTEST
(ordering_field int,
ProductCode int)!

INSERT INTO MYTEST (ordering_field, ProductCode)
SELECT
 SUM(SaleHistory.Quantity),
 Product.Code
 FROM Product
 INNER JOIN SaleHistory
    ON Product.Code = SaleHistory.Code
 GROUP BY Product.Code
 ORDER BY ordering_field DESC
 RANGE 1 TO 10!

Using a derived table doesn't cause an error. However, incorrect records are inserted into the temp table
INSERT INTO MYTEST (ordering_field, ProductCode)
select ordering_field, ProductCode
FROM
(SELECT
 SUM(SaleHistory.Quantity) AS ordering_field,
 Product.Code AS ProductCode
 FROM Product
 INNER JOIN SaleHistory
    ON Product.Code = SaleHistory.Code
 GROUP BY Product.Code
 ORDER BY ordering_field DESC
 RANGE 1 TO 10) TBL234!

Any ideas?

Chris


From memory ElevateDB doesn't support inserting using ORDER BY. I found out when creating in-memory tables. If you want the records on a specific sequence you'll have to create an index afterwards.

Roy Lambert [Team Elevate]

Chris,

<< However, when inserting into a temporary table I get the following error
ElevateDB Error #700 An error was found in the statement at line 9 and
column 3 (Expected end of expression but instead found ORDER) >>

You cannot use an ORDER BY clause with an INSERT sub-query.  You'll need to
create indexes on the temporary table using CREATE INDEX in order to allow
for different orders.

<< Using a derived table doesn't cause an error. However, incorrect records
are inserted into the temp table >>

What do you mean by "incorrect records" in this context ?  Do you mean that
it appears like a bug in EDB, or that it doesn't give you the rows that you
need ?

--
Tim Young
Elevate Software
www.elevatesoft.com

<You cannot use an ORDER BY clause with an INSERT sub-query.  You'll need to
create indexes on the temporary table using CREATE INDEX in order to allow
for different orders.>

Fair enough. In this case I'm only interested in selecting the top n records into a temporary table. The ORDER BY was used so the RANGE selects the records I want. Indexing (or having a specific natural order of records in the temp table) was not my concern.

<What do you mean by "incorrect records" in this context ?  Do you mean that
it appears like a bug in EDB, or that it doesn't give you the rows that you
need ?>

Indeed. It appears to be a bug in EDB. I get a different set of records in the temporary table when doing the derived table insert when compared to using the same select on its own.

Chris,

<< Indeed. It appears to be a bug in EDB. I get a different set of records
in the temporary table when doing the derived table insert when compared to
using the same select on its own. >>

Okay, I'll check this out and see what the issue is.

--
Tim Young
Elevate Software
www.elevatesoft.com