Ask Your Question
0

Asking for help with Temporary Tables y LibreOffice Base

asked 2015-10-07 23:58:52 +0100

mrmister gravatar image

Hi, I've seen several examples of code in which you can create a temporary table on the fly, that is directly in SQL, then load data in that table, and show the data from the temporary table.

LibreOffice Base is using exactly HSQLDB 1.8 HyperSQL.

I've been trying to get more info about this, but sadly most of the pages I found talk about creating temporary tables in Microsoft SQL Server.

I would appreciate if you could help me with this, and post me some examples about how to create a temporary table.

For example, let's imagine I have a very simple database. EMPLOYEES.ODB

That database have a table tbl_EMPLOYEES with 3 fields.

ID_Employee (the primary key)

EmployeeName (text field to introduce the employee's name)

EmployeeLastname (text field to introduce the employee's last name)

I have that database with 3 or 4 employees in it. Okay, now let's imagine I want to create the temporary table tbl_temp_WORKERS with the following fields

ID_Worker

WorkerName

WorkerLastname

And let's imagine I want to load all the data from the tbl_EMPLOYEES to tbl_temp_WORKERS and show the registries from the temporary table tbl_temp_WORKERS

How may I do that?

I would appreciate your help with this.

Cheers

edit retag flag offensive close merge delete

Comments

It is not really clear to me what you want to do, but it sounds as if you are looking for what is called a "View". Do you know https://wiki.documentfoundation.org/i... ?

Regina gravatar imageRegina ( 2015-10-08 16:41:24 +0100 )edit

4 Answers

Sort by » oldest newest most voted
0

answered 2015-10-10 18:12:51 +0100

peterwt gravatar image

@mrmister
1) The database that you gave a link to at https://mega.nz/#!gF5lxYQQ!davoeKfKc_... is not the database you are using in the Query you described - it only contains the Table tbl_names_and_ages.

2) The Query you describe is a Query with a sub query. The sub query selects all the Fields in all the Records in the real Table as an alias tbl_TEMPORAL. It then selects all the Fields in all the Records in the temporary Table tbl_TEMPORAL. This does not achieve anything as you get the same result if you just do a Select Query on the real Table. You are just creating a temporary copy of the real table and querying that in the same way as querying the real Table. You can also leave out the line - WHERE "tbl_EMPLEADOS"."ID_Empleados" = "tbl_TEMPORAL"."ID_Empleados" as the Table link is not used.

3) You said that you have seen examples of SQL to create a temporary Table. You need to use direct SQL as a Query can not contain CREATE TABLE only SELECT is allowed. You will probably have noticed that in most versions of SQL to create a temporary table the table name is prefixed with #. The SQL being:-

CREATE TABLE "#TableName" (field definitions)

The version of SQL used in Base does not use this convention. To create a temporary Table you need to use this SQL:-

CREATE TEMPORARY (or TEMP) TABLE "TableName" (field definitions)

Using direct SQL you can not save the statements. @pierre-yves samyn showed how you can use a Macro (LO Basic) to run SQL to create a temporary table. As the macro is saved it can used at any time without having to re-enter the SQL statements. The string sSQL in the Macro contains the SQL statements to create the temporary Table.

edit flag offensive delete link more

Comments

Hi petwewt There is a confussion here, my questions were always oriented to query design. 1) and 2) If you open the query design view and copy / paste that code I wrote, you create a query in SQL mode in which you load the data into a temporary table and show the data from the temporary table. The aim was to illustrate the creation of a temporary table into a query design in SQL, I meant "create" as using the select to define a temporary table but not in the strict sense of the word create.

mrmister gravatar imagemrmister ( 2015-10-10 22:39:33 +0100 )edit

Anyway I am very confussed about the code @pierre-yves samyn wrote... is that LibreOffice base? I never saw those loops Sub / EndSub ... For / Next... that looks like LibreOffice Base, but I don't have any idea where may I introduce code of this kind and run it. In the query SQL design I don't think I can introduce this kind of code? so basically I don't know where to type it and run it? perhaps as a Macro?

mrmister gravatar imagemrmister ( 2015-10-10 22:43:07 +0100 )edit

@mrmister I see. I thought you wanted more general information on temporary tables.

peterwt gravatar imagepeterwt ( 2015-10-12 16:48:03 +0100 )edit
0

answered 2015-10-08 17:20:27 +0100

pierre-yves samyn gravatar image

updated 2015-10-11 07:58:33 +0100

Hi

Regina +1, but if you really want to create a temporary table the simplest seems to create a "standard" (not temporary) table, and delete it when you do need it more.

Anyway, to answer the question in the strict sense, here is a code that runs SQL statements to

  • Delete the temporary table in case it already exists
  • Create the temporary table
  • Insert in this table the Employee table records

The program then moves to the first record and "loop" to concatenate the three columns of text to display it.

Note: the temporary table is not accessible in the interface.

Sub CreateTempTable()

Dim oConnexion as Object, oQuery as Object, oResult as object
Dim sSQL as String, sMsg as String
Dim i as long

oConnexion = ThisDatabasedocument.CurrentController.ActiveConnection

sSQL = "DROP TABLE ""tbl_temp_WORKERS"" IF EXISTS "

sSQL = sSQL & " CREATE TEMP TABLE ""tbl_temp_WORKERS"""
sSQL = sSQL & "(""ID_Worker"" BIGINT PRIMARY KEY,"
sSQL = sSQL & """WorkerName"" VARCHAR(100),""WorkerLastname"" VARCHAR(100)) "

sSQL = sSQL & " INSERT INTO ""tbl_temp_WORKERS""(""ID_Worker"", ""WorkerName"", ""WorkerLastname"")"
sSQL = sSQL & " (SELECT ""ID_Employe"", ""EmployeeName"", ""EmployeeLastname"" FROM  ""tbl_EMPLOYEES"")"
sSQL = sSQL & "SELECT * FROM ""tbl_temp_WORKERS"""

oQuery = oConnexion.createStatement()
oResult = oQuery.executeQuery(sSQL)
oResult.next

for i = 0 to oResult.columns.count - 1
    sMsg =  sMsg & " - " & oResult.columns.getByIndex(i).string
next i

sMsg =  sMsg & " - "

msgbox sMsg

End Sub

[EDIT]

See the "SameResult" query in temporarytable.odb

[EDIT #2]

With the code (open the form): temporarytable.odb

Regards

edit flag offensive delete link more

Comments

Hi pierre what may I do with that code? I opened the file temporarytable.odb and tried to copy that code into Tools... SQL... and I won't get any result, also I cannot use this code in an SQL query... I'd like to see a code I could play a bit with it and modify it By the way this doesn't seems to be pure HSQLDB 1.8 ? This looks like Basic of some kind? Can LibreOffice base handle this? Can you use these kind code "Basic type" from the SQL query design? I find this code and topic very interesting

mrmister gravatar imagemrmister ( 2015-10-10 22:28:48 +0100 )edit

@pierre-yves samyn oResult.colums.count gives the number of columns in the result. What is the equivalent to get the number of rows (records)?

peterwt gravatar imagepeterwt ( 2015-10-12 16:52:29 +0100 )edit

@peterwt this type of resultset can only be read sequentially. We must therefore "loop" until the end to get this number. So the simplest would be to launch a sql: SELECT COUNT

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-10-12 18:18:53 +0100 )edit
0

answered 2015-10-09 14:03:28 +0100

mrmister gravatar image

updated 2015-10-10 03:48:53 +0100

Hi Pierre and thanks for your reply. However, I don't understand very well the example. I was asking for an example code working in HSQLDB 1.8, which is the SQL that attach LibreOffice Base. Could you please attach a simple demo database, with a query, using a temporary table and some comments explaining how to load the data in a temporary table.

The example I proposed was: let's imagine I have a very simple database. EMPLOYEES.ODB

That database have a table tbl_EMPLOYEES with 3 fields.

ID_Employee (the primary key) EmployeeName (text field to introduce the employee's name) EmployeeLastname (text field to introduce the employee's last name)

I have that database with 3 or 4 employees in it. Okay, now let's imagine I want to create the temporary table tbl_temp_WORKERS with the following fields

ID_Worker WorkerName WorkerLastname

And let's imagine I want to load all the data from the tbl_EMPLOYEES to tbl_temp_WORKERS and show the registries from the temporary table tbl_temp_WORKERS

How may I do that?

If you could attach an example database (ODB file) with a query in which you have the SQL code showing how to load the data from the table to the temporary table, I'd be grateful.

The code you attached perhaps is very difficult to understand for people starting from scratch.

With a simple code and a demo database, this would more easy to understand. I'm a bit lost with the code you typed and I don't understand it... sorry :(

Cheers


Okay, finally I managed how to add data from a real table to a temporary table

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_...

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

//
// 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 ...
(more)
edit flag offensive delete link more

Comments

Your question was about code, so I responded with a code sample (basic) working for HSQLDB 1.8.

Your edit shows that you do not look to use temporary tables but simple queries which misled me. Your solution is functional but unnecessarily resource intensive.

You can achieve the same result simply with two queries that will not require to create a join. I edited my answer to add an example.

Regards

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-10-10 11:55:16 +0100 )edit

I have to confess I never saw that kind of code before. I was aware about HSQLDB 1.8 which is basically a working SQL with minor differences, but loops of the kind for / next... ??? can that work in LibreOffice Base? I was not aware of that. I'm very interested in this. So may I write a query using this kind of code? What language is that? Looks like LibreOffice Basic... is that right? and directly from the SQL query design can you write LibreOffice Basic code?

mrmister gravatar imagemrmister ( 2015-10-10 22:32:45 +0100 )edit

As I said, it is LibreOffice Basic, you can not write queries with this syntax. I thought that was you are looking for, my mistake. Forget this example if you do not practice programming. However, I have included the code in my second edit of my answer so that you can see the result: open the form and click the button (must have allow execution of macros).

The important point is that you unnecessarily complicated things (selection & useless join) as explained in my previous comment.

Regards

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-10-11 07:57:20 +0100 )edit

@mrmister The code that @pierre-yves samyn wrote is a Basic Macro and needs to be put in a Macro. It then executes the SQL Query you wrote and caries out an action on the Query results - showing the data in the columns of the first record of the temporary Table.

peterwt gravatar imagepeterwt ( 2015-10-12 17:00:05 +0100 )edit
0

answered 2017-04-08 00:38:04 +0100

updated 2017-04-08 00:43:18 +0100

I was trying to create a temporary table to use as a sort of an index in a service desk DB. It would contain only 2 fields: "statusID"; and "statusDescription", so I could use it to retrieve and decode the meaning of the request status code, which was stored only as a number in the source table. For some reason, I did not manage to make it work through a LO Base SQL query, but then I found another way to accomplish it, by using a "CASE" conditional expression in the query, just like the example below:

SELECT call_id AS "ID", date AS "Date", (
CASE
  WHEN "statusID" = 1 THEN 'Open'
  WHEN statusID = 2 THEN 'Wating'
  WHEN statusID = 3 THEN 'Closed'
END
) AS "Status" FROM "serviceRequests";

Maybe this isn't the appropriate topic, but I hope to help someone with similar needs.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-10-07 23:58:52 +0100

Seen: 495 times

Last updated: Apr 08 '17