RE: [Info-ingres] ORDER BY not allowed when creating temporary tables...

#1

I suspect it's an oversight that the order by is allowed in that context.

You can do what you appear to want in 2 stages

Declare global temporary table session.temp_table AS
(SELECT timestamp FROM someTable where 1 = 0)
on commit preserve rows with norecovery;

Insert into session.temp_table (timestamp) as SELECT FIRST 5 timestamp FROM
someTable ORDER BY timestamp DESC

NB syntax not checked

--
Nick
-----We Solve your Computer Problems---
UNIX, C, Panther, Ingres, Interbase, Firebird - Available Shortly

Quote:

-----Original Message-----
From: info-ingres-admin (AT) cariboulake (DOT) com
[mailto:info-ingres-admin (AT) cariboulake (DOT) com] On Behalf Of
morgan brickley
Sent: 03 March 2006 12:56
To: info-ingres (AT) cariboulake (DOT) com
Subject: [Info-ingres] ORDER BY not allowed when creating
temporary tables...

Hi there,

Can anyone answer this riddle:

You can create a temporary table using a select as follows:

Declare global temporary table session.temp_table AS
(SELECT FIRST 5 timestamp FROM someTable)
on commit preserve rows with norecovery;

But you can't do this because it has an order by clause:

Declare global temporary table session.temp_table AS
(SELECT FIRST 5 timestamp FROM someTable ORDER BY timestamp
DESC) on commit preserve rows with norecovery;

The Q is why allow the 'FIRST' modifer if you can't choose
what type of
sorting should be done. i.e. the first temporary table is
useless as you can't guarantee the elements that will get
copyied into the temporary
table. Is there some syntax I'm missing here ?

Regards to the ingres community,

Morgan.
_______________________________________________
Info-ingres mailing list
Info-ingres (AT) cariboulake (DOT) com
http://mailman.cariboulake.com/mailm...py/info-ingres

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

RE: [Info-ingres] ORDER BY not allowed when creating temporary tables...

comp.databases.ingres comp.databases.ingres

RE: [Info-ingres] ORDER BY not allowed when creating temporary tables... - 03-03-2006 , 07:09 AM