Hi,

The following statement doesn't work because of using ORDER.

CREATE TEMPORARY TABLE "TEMP_MIKE" AS
SELECT * FROM Orders ORDER BY OrderID DESC RANGE 1 TO 5000
WITH DATA

Does anyone have a workaround for this?

Thanks in advance.

Mike

Mike


I can think of a couple of ways. One I know works and the other might but I've never tried it.

The way I know works is to split it into two parts

1. table creation ie use the WITH NO DATA clause - forget order or range
2. insert into the new temporary table using a select statement.

The one "problem" with this is that part 2 requires a column list so its a bit more long winded

The way that might work but I've never tried it is to create a VIEW and use that.

I'm guessing that OrderID isn't an autoinc, because if it is the ORDER BY would probably be superfluous.

There may be other ways but it depends on what you're trying to achieve..


Roy Lambert

Hi Roy,

Thank for the suggestions.

I will try both.

Mike

Mike,

<< The following statement doesn't work because of using ORDER. >>

For the record, the reasoning behind this is that I don't want to deal with a lot of support inquiries from customers asking why their rows aren't in the order that they specified in the source query.  When rows are copied in a bulk manner in such situations, the underlying ordering in the target table may not match the ORDER BY clause specified in the source query.

However, there isn't a simple workaround, just do as Roy says and create the temporary table first, followed by this INSERT:

INSERT INTO "TEMP_MIKE" SELECT * FROM Orders ORDER BY OrderID DESC RANGE 1 TO 5000

Tim Young
Elevate Software
www.elevatesoft.com