Temporary table creation in HSQLDB 1.8 for LibreOffice

[mrmister]

Hi, finally I found how to create temporary tables and I'd like to discuss this here, also to talk about the possible strategies we could strive to use'em.
Okay, first of all I want to thank you to two people in the forum which amazed me for the level of knowledge they have.
One of them is FJCC, the forum's moderator, which months ago gave me an example of code using a temporary table that was a piece of art. Brilliant.
The other one is Arineckaig which made possible I understand in an easy way how to create a temporary table.

The example code I am posting is based in a code from Arineckaig

Here you have an example database you can download and you can use to do tests. This example database is in Spanish, it has many tables and we will perform a query using a temporary table example.

You can download the database here:
https://mega.nz/#!gF5lxYQQ!davoeKfKc_GQ ... FEbbHh8ZDI

Okay, we will do the following.
We will use the tbl_EMPLEADOS (tbl_EMPLOYEES) a real table, with data in it...
We will create a temporary table "on the fly" called tbl_temporal...
We will load the data in this temporary table called tbl_temporal, and the data will come from the real table tbl_EMPLEADOS
Finally, we will show the data from the temporary table and we won't show any data from the real table, just the temporary one.

The code is as follows:

Code: Select all

//
// THIS CODE LOAD THE DATA FROM A REAL TABLE TO A TEMPORARY TABLE
//
//
// THE SELECT CREATE THE TEMPORARY TABLE ON THE FLY OBSERVE HOW IT IS NAMED "tbl_TEMPORAL" (FULL STOP) "the field you want to show"
// FOR EXAMPLE: "tbl_temporal"."Nombre" WILL SHOW THE FIELD NOMBRE (NAME) FROM THE TEMPORARY TABLE "tbl_temporal"
//
// SUMMARIZING WE CREATE A TEMPORARY TABLE CALLED tbl_TEMPORAL AND WE ARE SHOWING FROM THAT TEMPORARY TABLE THE FIELDS
//
// Nombre (name in English), "Apellido1ero" (LastName),"Apellido2Seg" (Second Lastname, in Spain we use two last names father and mother)
//

SELECT 
       "tbl_TEMPORAL"."Nombre",
       "tbl_TEMPORAL"."Apellido1ero", 
       "tbl_TEMPORAL"."Apellido2seg"


// NOW AS USUALLY WE USE THE FROM (SELECT - FROM) AS IN ALL THE QUERIES DUE WE ARE LOADING DATA FROM A REAL TABLE, IT MUST BE PRESENT
// IN THE FROM, SO WE HAVE FROM "tbl_EMPLEADOS", OBSERVE WE HAVE A COMMA BECAUSE WE CONTINUE ADDING DATA TO BE SHOWN IN THE QUERY, AND
// THAT DATA IS THE DATA FROM THE TEMPORARY TABLE 
//
// SO... WE HAVE FROM "tbl_EMPLEADOS",
//
// AND NOW WE PROCEED TO LOAD THE DATA USING ANOTHER SELECT FROM WE WILL SELECT ALL THE DATA SELECT * (ASTERISK) FROM THE REAL
// TABLE "tbl_EMPLEADOS" AND WE WILL LOAD THAT DATA IN THE TEMPORARY TABLE "tbl_TEMPORAL"
//
// AT THIS TIME WE ARE CREATING THE TEMPORARY TABLE AND ALSO LOADING ALL THE DATA IN IT. WE LOAD ALL THE DATA IN THE TEMPORARY TABLE
// USING THE SELECT * FROM AND WE DEFINE THE TEMPORARY TABLE USING AN ALIAS AS "tbl_TEMPORAL"

FROM "tbl_EMPLEADOS",
                     ( SELECT *
                       FROM "tbl_EMPLEADOS") AS "tbl_TEMPORAL"

// FINALLY WE DEFINE A RELATIONSHIP BETWEEN THE REAL TABLE tbl_EMPLEADOS AND THE TEMPORARY TABLE tbl_TEMPORAL FOR THAT WE STABLISH
// THE RELATIONSHIP USING THE WHERE AND RELATING THE PRIMARY KEY FIELD OF BOTH TABLES SEE THE FIELD ID_Empleados IS PRESENT IN BOTH
// TABLES THE REAL ONE tbl_EMPLEADOS AND THE TEMPORARY TABLE tbl_TEMPORAL

WHERE "tbl_EMPLEADOS"."ID_Empleados" = "tbl_TEMPORAL"."ID_Empleados"

The code above is full of commentaries explaining everything inside, however, to see it fast, here you have the same code without commentaries.

THIS CODE LOAD DATA FROM A REAL TABLE INTO A TEMPORARY TABLE:

Code: Select all

SELECT 
       "tbl_TEMPORAL"."Nombre",
       "tbl_TEMPORAL"."Apellido1ero", 
       "tbl_TEMPORAL"."Apellido2seg"

FROM "tbl_EMPLEADOS",
                     ( SELECT *
                       FROM "tbl_EMPLEADOS") AS "tbl_TEMPORAL"

WHERE "tbl_EMPLEADOS"."ID_Empleados" = "tbl_TEMPORAL"."ID_Empleados"

So the question is... for what reason do we need temporary tables?

What is the strategy behind the use of temporary tables?

I have uploaded a demo database, if someone want to play a bit with it and retrieve other data using temporary tables, I think we could learn a bit more about the strategies behind the use of them. I find this kind of tables very interesting and I would like to see more examples and practical uses for them.

Any idea to show more examples?

Cheers

[Arineckaig]

So the question is... for what reason do we need temporary tables?

See some comments at:
viewtopic.php?f=61&t=79564&p=366343#p366343
Temporary tables (aka relations) are an integral part of the mathematical set theory that underlies relational databases and the processing of SQL statements.

[Villeroy]

Code: Select all

FROM "tbl_EMPLEADOS",
                     ( SELECT *
                       FROM "tbl_EMPLEADOS") AS "tbl_TEMPORAL"

In this particular case it does exactly the same as

Code: Select all

FROM "tbl_EMPLEADOS" AS "tbl_TEMPORAL"

because SELECT * FROM "X" simply selects everything from table X.

[mrmister]

I am uploading again the database so you can do some tests if you want.

You can download the demo database and play with it following this link:
https://mega.nz/#!QI4WxD5K!rhx7ZSi_ZwhH ... 9ohR-X5Y_Y

Hi Villeroy, if I type the code you suggested I am getting an error code, it says SQL Status S0022, Error code -28

Is this the code you suggested?

THIS CODE DON'T WORK AND RETURN A SQL STATUS S0022 - ERROR CODE -28.
The demo database name for tests is: ejemplotablastemporales.odb

Code: Select all

SELECT
       "tbl_TEMPORAL"."Nombre",
       "tbl_TEMPORAL"."Apellido1ero",
       "tbl_TEMPORAL"."Apellido2seg"

FROM "tbl_EMPLEADOS" AS "tbl_TEMPORAL"

WHERE "tbl_EMPLEADOS"."ID_Empleados" = "tbl_TEMPORAL"."ID_Empleados"

As Arineckaig said:

Working with any relational database makes frequent use of temporary tables, because these are in effect created every time a SELECT query statement is executed: in this respect a sub-query does not differ from any other query.

On the other hand if the content of a temporary table, produced by the output of a query or sub-query, is to be used as input for further processing it may frequently necessary to give that temporary (and not yet named) table an "alias" name so as to distinguish the values in its columns/attributes from similar named columns/attributes in other tables.

Okay, but do we have a common or practical use or cases in which we would need to use temporary tables?
For example to reach a calculation or some date we couldn't reach without the temporary table?
Any ideas or examples about this?
Some examples with code would be highly appreciated to see these temporary tables in queries working in action and having that way a practical idea about for what they could be used for.

I'd like to understand practical uses in which we would need to use temporary tables in a query design in SQL.

Cheers

PS If you want to play with the uploaded database and doing some queries with temporary tables, it would be great so we can see the code and talk about several cases to understand better why this kind of temporary tables in queries could be used.

[Villeroy]

SELECT
"tbl_TEMPORAL"."Nombre",
"tbl_TEMPORAL"."Apellido1ero",
"tbl_TEMPORAL"."Apellido2seg"

FROM "tbl_EMPLEADOS", "tbl_EMPLEADOS") AS "tbl_TEMPORAL"

WHERE "tbl_EMPLEADOS"."ID_Empleados" = "tbl_TEMPORAL"."ID_Empleados"

or equivalent

Code: Select all

SELECT
           "tbl_TEMPORAL"."Nombre",
           "tbl_TEMPORAL"."Apellido1ero",
           "tbl_TEMPORAL"."Apellido2seg"

    FROM "tbl_EMPLEADOS" JOIN "tbl_EMPLEADOS" AS "tbl_TEMPORAL" 
    ON  "tbl_EMPLEADOS"."ID_Empleados" = "tbl_TEMPORAL"."ID_Empleados"

It selects records from "tbl_EMPLEADOS" table where ID_Empleatos matches itself (which is not useful).

[eremmel]

Is OP looking for table aliases with derived tables and has gone the route by asking too technical questions i.s.o. asking about the question behind the need for temporary tables? I guess most volunteers never used them .

I've just seen the other post of the OP.

A table can have multiple roles in the same query. For each role it has to be mentioned separately. Assume a table called people. It contains among others four fields ID, 'fatherID' and 'motherID' and 'personName'. The fields fatherID and motherID are foreign keys to the same table people (key ID), but other records of cause.

So when we want to print all persons with their parents we need the the same table in three roles. Each role we identify with an alias (you called it a temporary table, but that is something totally differently). We might make the following query (I leave out all those double quote for readability).

Code: Select all

SELECT p.ID, p.personName, m.personName, f.personName
FROM people AS p           -- The person role 
LEFT JOIN people AS m    -- The mother role 
   ON p.motherID = m.ID
LEFT JOIN people AS f    -- The father role
  ON p.fatherID = f.ID

You can leave out the key-word AS. The LEFT JOIN makes that persons with unknown father/mother are also shown.
I hope this example makes things clear.

Erik