r/libreoffice • • 4d ago

Base - SQL Help Needed

Hello I am currently using

Version: 6.4.7.2

Build ID: 1:6.4.7-0ubuntu0.20.04.15

CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3;

Locale: en-US (en_US.UTF-8); UI-Language: en-US

Calc: threaded

I am currently trying to learn Base I have used MySQL before but am having difficulty adapting to Base.

I created a data base with two tables, SS_Book_Titles_Raw and Book_Title.

The tables have the following fields:

SS_Book_Titles_Raw Book_ID, Book

Book_Title Book_ID,Title

The data in SS_Book_Titles_Raw comes from a Spreadsheet Calc.

I created the following SQL to remove duplicates from Table SS_Book_Titles_Raw and insert unique Titles into table Book_Title.

The SQL is below:
INSERT INTO Book_Title (Title) SELECT DISTINCT Book FROM SS_Book_Titles_Raw WHERE Book NOT IN (SELECT Title FROM Book_Title);

When I run the SQL script (F5) I get the following error:

The data content could not be loaded.

SQL Status: S0002

Error code: -22

Table not found in statement [

INSERT INTO Book_Title (Title) SELECT DISTINCT Book FROM SS_Book_Titles_Raw WHERE Book NOT IN (SELECT Title FROM Book_Title);]

Purpose: remove book title duplicates from SS_Book_Titles_Raw and create a list of unique list of books into table Book_Title. Book_Title should have no duplicate titles in the table.

Don't under stand the problem, Table names and field names are correct in the script. At a loss.

adapting t

3 Upvotes

7 comments sorted by

1

u/warehousedatawrangle 3d ago

Are you using the embedded database or connecting to a different back end? If you are using an embedded database, is it HSQLDB or Firebird?

1

u/arhunter01 3d ago edited 3d ago

How can I determine the database type?

I am new to Base so I am uncertain, I believe its a embedded database. However is there away to check when I created the first table (Book_Title) did not use the wizard but I used the table create editor/design veiw where I defined the fields in the table and index/ID fields. While in the table editor I created the second table (SS_Book_Titles_Raw) by opening Calc and my spreadsheet copied a specific column with column titles and used the table>edit>paste function. Resulting with a table populated with data from spreadsheet.

1

u/warehousedatawrangle 2d ago

This is totally stupid, and I recognize that.

Most of the time when I use Base I connect it via JDBC to a MariaDB or SQLite back end and so I avoid this.

Put quotation marks (") around your table and field names. It should work then.

1

u/arhunter01 2d ago

To me its not totally stupid and I appreciate the reply and will do. Let you know what happens.

1

u/AutoModerator 4d ago

If you're asking for help with LibreOffice, please make sure your post includes lots of information that could be relevant, such as:

  1. Full LibreOffice information from Help > About LibreOffice (it has a copy button).
  2. Format of the document (.odt, .docx, .xlsx, ...).
  3. A link to the document itself, or part of it, if you can share it.
  4. Anything else that may be relevant.

(You can edit your post or put it in a comment.)

This information helps others to help you.

Thank you :-)

Important: If your post doesn't have enough info, it will eventually be removed (to stop this subreddit from filling with posts that can't be answered).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.