Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
Temp table with ordering |
Sat, Mar 9 2019 6:32 AM | Permanent Link |
Mike | 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 |
Sat, Mar 9 2019 9:16 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sat, Mar 9 2019 2:23 PM | Permanent Link |
Mike | Hi Roy,
Thank for the suggestions. I will try both. Mike |
Thu, Mar 14 2019 12:05 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |