LO Base: I have problems with creating a View

Carlos_E_R · October 28, 2022, 2:20pm

Hi,

My first problem is that I have not found a tutorial on writing Views in Base:tables. Even the help doesn’t work (not found).

I managed to create the view I want, it works, but I am not allowed to save it. The error is:

SQL Status: 42S21
Error code: 1060

Duplicate column name 'Autor' /home/abuild/rpmbuild/BUILD/libreoffice-7.3.6.2/connectivity/source/drivers/mysqlc/mysqlc_general.cxx:119

You can see a photo of what I created here:

https://susepaste.org/75164811

I can paste the SQL it generates, but I don’t know SQL at all.

SELECT "Libros"."Indice", "Libros"."Titulo", "Libros"."Subtitulo", "Libros"."Comentario", "Colecciones"."Coleccion", "Libros"."NumEnCol", "Autores"."Autor", "Autores_1"."Autor", "Autores_2"."Autor", "Generos"."Generos", "Libros"."NumPartes", "Libros"."F_creacion", "Libros"."F_1a_edici", "Libros"."F_edicion", "Libros"."F_adquis", "Libros"."C_adquis", "Adquisicion"."Adquisicion", "Libros"."Cmnt_adquis", "Editoriales"."Editorial", "Libros"."Titulooriginal", "Traductores"."Traductor", "Libros"."Unfinished", "Libros"."NoLeido" FROM { oj "Biblioteca"."Editoriales" AS "Editoriales" RIGHT OUTER JOIN "Biblioteca"."Libros" AS "Libros" ON "Editoriales"."Clave" = "Libros"."Editorial" LEFT OUTER JOIN "Biblioteca"."Colecciones" AS "Colecciones" ON "Colecciones"."Indice" = "Libros"."Coleccion" LEFT OUTER JOIN "Biblioteca"."Autores" AS "Autores" ON "Autores"."Indice" = "Libros"."Autor" LEFT OUTER JOIN "Biblioteca"."Autores" AS "Autores_1" ON "Autores_1"."Indice" = "Libros"."SegundoAutor" LEFT OUTER JOIN "Biblioteca"."Autores" AS "Autores_2" ON "Autores_2"."Indice" = "Libros"."TercerAutor" LEFT OUTER JOIN "Biblioteca"."Generos" AS "Generos" ON "Generos"."Clave" = "Libros"."Genero" LEFT OUTER JOIN "Biblioteca"."Adquisicion" AS "Adquisicion" ON "Adquisicion"."Indice" = "Libros"."L_adquis" LEFT OUTER JOIN "Biblioteca"."Traductores" AS "Traductores" ON "Traductores"."clave" = "Libros"."Traductor" }

I have the view open, unsaved, hoping that somebody tells me what to do.

I’m using openSUSE Leap Linux 15.3. LO is:

Version: 7.3.6.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 12; OS: Linux 5.3; UI render: default; VCL: gtk3
Locale: es-ES (en_US.UTF-8); UI: en-US
Calc: threaded

Thanks.

Villeroy · October 28, 2022, 2:33pm

Try alias names for all duplicates. For instance:
“Autores”.“Autor”,
“Autores_1”.“Autor” AS “Autor1”,
“Autores_2”.“Autor” AS “Autor2”,

Carlos_E_R · October 28, 2022, 6:26pm

Makes sense. I produced this SQL:

SELECT "Libros"."Indice", "Libros"."Titulo", "Libros"."Subtitulo", "Libros"."Comentario", "Colecciones"."Coleccion", "Libros"."NumEnCol", "Autores"."Autor", "“Autor”" AS "“Autor1”", "“Autor”" AS "“Autor2”", "Generos"."Generos", "Libros"."NumPartes", "Libros"."F_creacion", "Libros"."F_1a_edici", "Libros"."F_edicion", "Libros"."F_adquis", "Libros"."C_adquis", "Adquisicion"."Adquisicion", "Libros"."Cmnt_adquis", "Editoriales"."Editorial", "Libros"."Titulooriginal", "Traductores"."Traductor", "Libros"."Unfinished", "Libros"."NoLeido" FROM { oj "Biblioteca"."Libros" AS "Libros" LEFT OUTER JOIN "Biblioteca"."Colecciones" AS "Colecciones" ON "Libros"."Coleccion" = "Colecciones"."Indice" LEFT OUTER JOIN "Biblioteca"."Editoriales" AS "Editoriales" ON "Libros"."Editorial" = "Editoriales"."Clave" LEFT OUTER JOIN "Biblioteca"."Autores" AS "Autores" ON "Libros"."Autor" = "Autores"."Indice" LEFT OUTER JOIN "Biblioteca"."Autores" AS "Autores_1" ON "Libros"."SegundoAutor" = "Autores_1"."Indice" LEFT OUTER JOIN "Biblioteca"."Autores" AS "Autores_2" ON "Libros"."TercerAutor" = "Autores_2"."Indice" LEFT OUTER JOIN "Biblioteca"."Generos" AS "Generos" ON "Libros"."Genero" = "Generos"."Clave" LEFT OUTER JOIN "Biblioteca"."Adquisicion" AS "Adquisicion" ON "Libros"."L_adquis" = "Adquisicion"."Indice" LEFT OUTER JOIN "Biblioteca"."Traductores" AS "Traductores" ON "Libros"."Traductor" = "Traductores"."clave" }

I noticed that there is a row for alias in the design view.

When run, it produces this error (hand copied):

The data content could not be loaded. /home/abuild/-rpmbuild/BUILD/libreoffice7.3.6.2/connectivity/source/commontools/dbtools.cxx:747

Unknown column '"autor'" in 'field list/' /home/abuild/-rpmbuild/BUILD/libreoffice7.3.6.2/connectivity/source/drivers/mysqlc/mysqlc_general.cxx:119

I undid the change, then tried to enter alias in the design view instead. This generates this SQL:

SELECT "Libros"."Indice", "Libros"."Titulo", "Libros"."Subtitulo", "Libros"."Comentario", "Colecciones"."Coleccion", "Libros"."NumEnCol", "Autores"."Autor", "Libros"."Autor" AS "Autor1", "Libros"."Autor" AS "Autor2", "Generos"."Generos", "Libros"."NumPartes", "Libros"."F_creacion", "Libros"."F_1a_edici", "Libros"."F_edicion", "Libros"."F_adquis", "Libros"."C_adquis", "Adquisicion"."Adquisicion", "Libros"."Cmnt_adquis", "Editoriales"."Editorial", "Libros"."Titulooriginal", "Traductores"."Traductor", "Libros"."Unfinished", "Libros"."NoLeido" FROM { oj "Biblioteca"."Libros" AS "Libros" LEFT OUTER JOIN "Biblioteca"."Colecciones" AS "Colecciones" ON "Libros"."Coleccion" = "Colecciones"."Indice" LEFT OUTER JOIN "Biblioteca"."Editoriales" AS "Editoriales" ON "Libros"."Editorial" = "Editoriales"."Clave" LEFT OUTER JOIN "Biblioteca"."Autores" AS "Autores" ON "Libros"."Autor" = "Autores"."Indice" LEFT OUTER JOIN "Biblioteca"."Autores" AS "Autores_1" ON "Libros"."SegundoAutor" = "Autores_1"."Indice" LEFT OUTER JOIN "Biblioteca"."Autores" AS "Autores_2" ON "Libros"."TercerAutor" = "Autores_2"."Indice" LEFT OUTER JOIN "Biblioteca"."Generos" AS "Generos" ON "Libros"."Genero" = "Generos"."Clave" LEFT OUTER JOIN "Biblioteca"."Adquisicion" AS "Adquisicion" ON "Libros"."L_adquis" = "Adquisicion"."Indice" LEFT OUTER JOIN "Biblioteca"."Traductores" AS "Traductores" ON "Libros"."Traductor" = "Traductores"."clave" }

(I think it is the same, but LO thinks differently)

This can be saved to file, but the results are wrong. For autor I get the proper name, for for autor 1 and 2 I get the indexes. See photo:

Problem now is that if I click “Edit” (the view) I can not go back to the original edit graph or SQL, they are inaccessible. I get a different edit:

https://susepaste.org/92157723

and clicking on the icon I can get icon design. I don’t see how I can use any of that.

I have to go back, delete this view, and create another from scratch. This time, I will create the alias the time I enter that table.

Villeroy · October 28, 2022, 6:30pm

Indeed, LibreOffice can generate views but once they are stored in the database backend, you can’t edit them directly. You are using MySQL, so edit your views with an adequate tool for MySQL.
I use to keep a Base query for editing and if I need an equivalent view, I do right-click>“Create as view…”.

Carlos_E_R · October 28, 2022, 6:46pm

Sorry, I can’t. As I said, I know nothing of SQL.

Initially, this database was created using Rekall, a database front-end in KDE3, now abandonware:

https://store.kde.org/p/1126366

Things work there, but it is not build anymore and soon I will loose access to my library database.

I need something that allows me a graphical design without getting down to the code. I hoped that Libre Office Base would be there by now. It is very close but not there yet.

Villeroy · October 28, 2022, 7:10pm

Looking at your first sceenshot on SUSE Paste, I can see the status bar of your Base window. It indicates that the Base document is connected to a MySQL database running on the local machine, logged in as user “cer”. This is the most important information when dealing with any Base related question because Base is not a database. Base is a tool to work with databases.
Create a query instead of a view. Create a new query in SQLview, paste your above SQL and add the aliases. The query is stored in the Base document. If there is any reason to store the query in the MySQL database (e.g. to make it usable from Rekall or whatever), you right-click the query icon and choose “Create as view …”. This “exports” the Base query to the underlying database.
When Base interpretes a query with duplicate field names, Base silently adds subsequent numbers to the names (Name1, Name2 etc.) wheras MySQL raises the error you mentioned.

Carlos_E_R · October 28, 2022, 9:39pm

Correct.
Yes, I understood that.
Years ago I created the database in MySQL using Rekall with the idea that the database could be usable from other programs.

Ok, thanks, I will have a look. I see there is a whole chapter on queries in “Base Guide 7.3”. I hope I don’t have to create the SQL text, because I know nothing of that.

(sorry, I had to remove the susepaste link in your quoted text, the webforum produced an error with it.)

Carlos_E_R · October 28, 2022, 9:49pm

Hi,

Something else. I’m getting messages from “noreply@ask.libreoffice.org” that say my message(s) have been hidden because it is spam. I don’t get it, what have I done, or whom I have to contact to remove that. I am not aware of having posted any advertisement or promotional :-?

Your post was flagged as spam: the community feels it is an advertisement, something that is overly promotional in nature instead of being useful or relevant to the topic as expected.

Carlos_E_R · October 28, 2022, 10:28pm

Ok, I got it done! I have a view that works.
I added each field one by one, runing the query each time. When I reached Autor, I added an alias in the row below it, and tested, and continued till the end. In less than 5 minutes I got it done. I tested, and as far I could see, it worked. Then I saved it, and it also worked.

Thank you!

Now, I will also read the documentation on “SQL View”, and try that method too. But I have more time for that

RobertG · October 28, 2022, 5:00pm

Try Base Guide

Carlos_E_R · October 28, 2022, 6:33pm

I already did. Searching for “View” in the file, finds these entries in the index:

Contents
...
One view – many forms........................................................................................................ 228
...
Creating Queries in SQL View...............................................................................................261
...
More rapid access to queries using table views....................................................................284

That’s all. The last entry is only a paragraph. There is no chapter on Views.

I found a youtube in Hindi, though. I don’t speak the language, but I watched the screens in it. They are way more simple that what I am doing, and the menus do not match fully. I don’t get “edit SQL”, for instance.

https://www.youtube.com/watch?v=DMUBlOxADCk

RobertG · October 29, 2022, 4:59am

Views and Queries will be edited the same way. So have a look at chapter “Queries”. A view is only a query, which is saved directly in the database. Run a query in direct SQL and it will run like a view. Two fields in a view couldn’t get the same name.

Carlos_E_R · October 29, 2022, 11:22am

Yes, I just created a query in “Create a query in Design View”, without needing to read the documentation, and it shows all my 304 books correctly, AFAICS. It saves directly, no issues, and when I open to edit it shows me the original graphical design, so I do not need to use SQL myself, which I can not even read.

It was not required, but I created an alias for the other two author entries, because it is easier to read in the resulting columns. I might create other alias to rename some columns better.

I am very happy

But I do not understand why my posts are flagged as spam. Anyway, thank you all for your help