Statement like
CREATE LOCAL TEMPORARY TABLE FOO AS (SOME_QUERY) TRANSACTIONAL NOT PERSISTENT;
throws a syntax error and the error is identified next to NOT word.
Dropping TRANSACTIONAL away works fine.
CREATE LOCAL TEMPORARY TABLE FOO AS (SOME_QUERY) NOT PERSISTENT;
Dropping NOT PERSISTENT seems to work fine, too.
CREATE LOCAL TEMPORARY TABLE FOO AS (SOME_QUERY) TRANSACTIONAL;
But not their combination.
Is TEMP table a GLOBAL or LOCAL TEMPORARY?
Would it make sense also to have a table that is local to the transaction?
Something like
CREATE TRANSIENT TABLE FOO AS (SOME_QUERY);
or maybe
CREATE TRANSIENT TEMPORARY TABLE FOO AS (SOME_QUERY);
to be more aligned with the current syntax.
This kind of table would always be TRANSACTIONAL and NOT PERSISTENT.
Use case:
SELECT
SELECT COUNT(*) FROM TEMP WHERE SOME_FURTHER_RESTRICTION
FROM (SOME_QUERY) AS TEMP
Is not possible because the TEMP table variable created in FROM clause is not
seen by the inner query of the SELECT clause;
This could be written
CREATE TRANSIENT TABLE TEMP AS (SOME_QUERY);
SELECT COUNT(*) FROM TEMP WHERE SOME_FURTHER_RESTRICTION;
Another way to achieve this would be to allow user-defined variables that have
a table variable like this
SET @TEMP = SOME_QUERY;
SELECT COUNT(*) FROM @TEMP WHERE SOME_FURTHER_RESTRICTION;
- Rami
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
