SQL in Base Does Not Work As I Expect

Discuss the database features
Post Reply
claytoncramer
Posts: 6
Joined: Mon Oct 08, 2018 6:59 am

SQL in Base Does Not Work As I Expect

Post by claytoncramer »

I tried to use the query wizard to select from a table. The query wizard asked for fields to match. In this case year = 1911. Here is the SQL it created:

Code: Select all

SELECT "Query_MassMurder"."ID" AS "ID", "Query_MassMurder"."month" AS "month", "Query_MassMurder"."date" AS "date", "Query_MassMurder"."year" AS "year", "Query_MassMurder"."city" AS "city", "Query_MassMurder"."state" AS "state", "Query_MassMurder"."dead victim count" AS "dead victim count", "Query_MassMurder"."undead victim count" AS "undead victim count", "Query_MassMurder"."suicide?" AS "suicide?", "Query_MassMurder"."UNKNOWN" AS "UNKNOWN", "Query_MassMurder"."AX" AS "AX", "Query_MassMurder"."HATCHET" AS "HATCHET", "Query_MassMurder"."KNIFE" AS "KNIFE", "Query_MassMurder"."OTHER SHARP" AS "OTHER SHARP", "Query_MassMurder"."BLUNT" AS "BLUNT", "Query_MassMurder"."EXPLOSIVE" AS "EXPLOSIVE", "Query_MassMurder"."POISON" AS "POISON", "Query_MassMurder"."STRANGLE" AS "STRANGLE", "Query_MassMurder"."DROWN" AS "DROWN", "Query_MassMurder"."ARSON" AS "ARSON", "Query_MassMurder"."HANG" AS "HANG", "Query_MassMurder"."OTHER" AS "OTHER", "Query_MassMurder"."FIREARM (unidentified)" AS "FIREARM (unidentified)", "Query_MassMurder"."shotgun" AS "shotgun", "Query_MassMurder"."rifle" AS "rifle", "Query_MassMurder"."pistol" AS "pistol", "Query_MassMurder"."machine gun" AS "machine gun", "Query_MassMurder"."cause" AS "cause", "Query_MassMurder"."category" AS "category" FROM "Query_MassMurder" "Query_MassMurder"
No WHERE clause. I tried to add one.

Code: Select all

WHERE "QueryMassMurder"."year" = 1911
The data content could not be loaded. What am I missing?

Thanks, that fixed it. Now, if I just get booleans to test. TRUE 'TRUE', or 'T'?
Last edited by claytoncramer on Tue Oct 09, 2018 6:26 pm, edited 1 time in total.
OpenOffice 4.1.6
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: SQL in Base Does Not Work As I Expect

Post by Sliderule »

claytoncramer wrote:
No WHERE clause. I tried to add one.

Code: Select all

WHERE "QueryMassMurder"."year" = 1911
The name in the FROM clause is "Query_MassMurder" NOT "QueryMassMurder" as you indicated in the WHERE clause.

Additionally, since ( I assume ) you are using a predefined QUERY rather than either a TABLE or VIEW, you must NOT send this to the database back-end directly, but, rather use the Base Parser ( since the database back-end does not know about Base Queries ).

Explanation: I do not know the column type of "Query_MassMurder"."year". Therefore:
  1. If "Query_MassMurder"."year" is a text field ( either CHAR, VARCHAR ) 1911 should be enclosed in single quotes . . . such as . . . '1911'
  2. If "Query_MassMurder"."year" is an integer or decimal or numeric field, 1911 should NOT be enclosed in single quotes . . . so, enter it as . . . 1911
  3. You have indicated you are using OpenOffice 4.1.6, however, as the date I am writing this, the current STABLE Version is 4.1.5, whereas, BETA Version 4.1.6 may exist.
  4. Below is your Query as YOU defined it above with the WHERE clause corrected using the names you assigned in the FROM clause, AND assuming "year" is an Integer, Decimal or Numeric columrn. :D

    Code: Select all

    -- Your Query as you wrote it with revised WHERE clause assuming "Query_MassMurder"."year" is NOT TEXT
    SELECT 
       "Query_MassMurder"."ID" AS "ID",
       "Query_MassMurder"."month" AS "month",
       "Query_MassMurder"."date" AS "date",
       "Query_MassMurder"."year" AS "year",
       "Query_MassMurder"."city" AS "city",
       "Query_MassMurder"."state" AS "state",
       "Query_MassMurder"."dead victim count" AS "dead victim count",
       "Query_MassMurder"."undead victim count" AS "undead victim count",
       "Query_MassMurder"."suicide?" AS "suicide?",
       "Query_MassMurder"."UNKNOWN" AS "UNKNOWN",
       "Query_MassMurder"."AX" AS "AX",
       "Query_MassMurder"."HATCHET" AS "HATCHET",
       "Query_MassMurder"."KNIFE" AS "KNIFE",
       "Query_MassMurder"."OTHER SHARP" AS "OTHER SHARP",
       "Query_MassMurder"."BLUNT" AS "BLUNT",
       "Query_MassMurder"."EXPLOSIVE" AS "EXPLOSIVE",
       "Query_MassMurder"."POISON" AS "POISON",
       "Query_MassMurder"."STRANGLE" AS "STRANGLE",
       "Query_MassMurder"."DROWN" AS "DROWN",
       "Query_MassMurder"."ARSON" AS "ARSON",
       "Query_MassMurder"."HANG" AS "HANG",
       "Query_MassMurder"."OTHER" AS "OTHER",
       "Query_MassMurder"."FIREARM (unidentified)" AS "FIREARM (unidentified)",
       "Query_MassMurder"."shotgun" AS "shotgun",
       "Query_MassMurder"."rifle" AS "rifle",
       "Query_MassMurder"."pistol" AS "pistol",
       "Query_MassMurder"."machine gun" AS "machine gun",
       "Query_MassMurder"."cause" AS "cause",
       "Query_MassMurder"."category" AS "category"
    FROM "Query_MassMurder" "Query_MassMurder"
    WHERE "Query_MassMurder"."year" = 1911
    
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SQL in Base Does Not Work As I Expect

Post by UnklDonald418 »

I think some of your problems stem from the limited capabilities of Wizard to Create Query... Instead use Create Query in Design View... which is a GUI query editor that is not much more difficult to use than the wizard and it offers significantly more options, among them a list of available functions, sort options, and Criterion to set up WHERE clauses.
Once you've got the basics of your query then select the toolbar icon for Switch Design View On/Off (or View->Switch Design View On/Off ) to do any final SQL editing.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
claytoncramer
Posts: 6
Joined: Mon Oct 08, 2018 6:59 am

Re: SQL in Base Does Not Work As I Expect

Post by claytoncramer »

Wizard to Create Query seems to not work. That it cannot add WHERE clauses is a serious defect. Create Query in Design View was not obvious. Create Query in SQL View is obvious.

Bigger problem is that because I imported a spreadsheet, many of the numeric columns were imported as TEXT. So:

Code: Select all

alter table "MassMurder" alter column "dead victim count" SMALLINT
produces

Code: Select all

1: Wrong data type: java.lang.NumberFormatException: For input string: "?" in statement [alter table "MassMurder" alter column "dead victim count" SMALLINT]
OpenOffice 4.1.6
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SQL in Base Does Not Work As I Expect

Post by UnklDonald418 »

When importing a spreadsheet you get a Copy Table dialog. By default it copies most spreadsheet columns as Text, but that can be changed.
Make any adjustments on the first page of the Copy Table dialog and select Next to get the Apply columns page.
Select any or all the Existing columns before selecting Next.
That should bring up the Type formatting page. Select the Auto button and it will attempt to determine the correct formatting for all the fields based on the data in the spreadsheet columns. Also, on that page you can check and if needed, adjust the Field type on a field by field basis.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: SQL in Base Does Not Work As I Expect

Post by Sliderule »

claytoncramer wrote:Wizard to Create Query seems to not work. That it cannot add WHERE clauses is a serious defect. Create Query in Design View was not obvious. Create Query in SQL View is obvious.
The Wizard does work, but, I suggest it is because you do not know how to use it. And, YES, the Wizard to Create Query CAN add WHERE clause(s).

For the column, "year", on the Criterion line, if you enter: 1911 you should get what you desire.
claytoncramer wrote: Bigger problem is that because I imported a spreadsheet, many of the numeric columns were imported as TEXT. So:

Code: Select all

alter table "MassMurder" alter column "dead victim count" SMALLINT
produces

Code: Select all

1: Wrong data type: java.lang.NumberFormatException: For input string: "?" in statement [alter table "MassMurder" alter column "dead victim count" SMALLINT]
Therefore, user error in defining the database column type when you, as you put it, "imported a spreadsheet". Perhaps, you have some values that are larger than a SMALLINT or you have NULL ( undefined values ) that you should first define, maybe as 0. This too is user error.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
claytoncramer
Posts: 6
Joined: Mon Oct 08, 2018 6:59 am

Re: SQL in Base Does Not Work As I Expect

Post by claytoncramer »

QueryWizard1.JPG
Attached are the screens I used in the Query Wizard. Here is the SQL it created with no WHERE clause.

Code: Select all

SELECT "MassMurder"."ID" AS "ID", "MassMurder"."month" AS "month", "MassMurder"."date" AS "date", "MassMurder"."year" AS "year", "MassMurder"."city" AS "city", "MassMurder"."state" AS "state", "MassMurder"."dead victim count" AS "dead victim count", "MassMurder"."undead victim count" AS "undead victim count", "MassMurder"."suicide?" AS "suicide?", "MassMurder"."UNKNOWN" AS "UNKNOWN", "MassMurder"."AX" AS "AX", "MassMurder"."HATCHET" AS "HATCHET", "MassMurder"."KNIFE" AS "KNIFE", "MassMurder"."OTHER SHARP" AS "OTHER SHARP", "MassMurder"."BLUNT" AS "BLUNT", "MassMurder"."EXPLOSIVE" AS "EXPLOSIVE", "MassMurder"."POISON" AS "POISON", "MassMurder"."STRANGLE" AS "STRANGLE", "MassMurder"."DROWN" AS "DROWN", "MassMurder"."ARSON" AS "ARSON", "MassMurder"."HANG" AS "HANG", "MassMurder"."OTHER" AS "OTHER", "MassMurder"."FIREARM (unidentified)" AS "FIREARM (unidentified)", "MassMurder"."shotgun" AS "shotgun", "MassMurder"."rifle" AS "rifle", "MassMurder"."pistol" AS "pistol", "MassMurder"."machine gun" AS "machine gun", "MassMurder"."cause" AS "cause", "MassMurder"."category" AS "category" FROM "MassMurder" "MassMurder"
I will atach the next few attachments in next post.
Attachments
QueryWizard3.JPG
QueryWizard2.JPG
OpenOffice 4.1.6
claytoncramer
Posts: 6
Joined: Mon Oct 08, 2018 6:59 am

Re: SQL in Base Does Not Work As I Expect

Post by claytoncramer »

Three more screenshots of the Query Wizard.
Attachments
QueryWizard6.JPG
QueryWizard5.JPG
QueryWizard4.JPG
OpenOffice 4.1.6
claytoncramer
Posts: 6
Joined: Mon Oct 08, 2018 6:59 am

Re: SQL in Base Does Not Work As I Expect

Post by claytoncramer »

More screenshots of the Query Wizard
Attachments
QueryWizard7.JPG
OpenOffice 4.1.6
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SQL in Base Does Not Work As I Expect

Post by UnklDonald418 »

In a database I use for testing purposes I created a MassMurder table similar to yours and using the wizard I got the same results, no WHERE clause.
Oddly, I tried the wizard on another table in my database and it did generate a WHERE clause.
Next I opened the my database with LibreOffice and the wizard generated the following query

Code: Select all

SELECT "MassMurder"."ID" "ID", "MassMurder"."month" "month", "MassMurder"."date" "date", "MassMurder"."year" "year", "MassMurder"."city" "city", "MassMurder"."state" "state", "MassMurder"."dead victim count" "dead victim count", "MassMurder"."undead victim count" "undead victim count", "MassMurder"."suicide?" "suicide?", "MassMurder"."UNKNOWN" "UNKNOWN", "MassMurder"."AX" "AX", "MassMurder"."HATCHET" "HATCHET", "MassMurder"."KNIFE" "KNIFE", "MassMurder"."BLUNT" "BLUNT", "MassMurder"."EXPLOSIVE" "EXPLOSIVE", "MassMurder"."POISON" "POISON", "MassMurder"."STRANGLE" "STRANGLE", "MassMurder"."DROWN" "DROWN", "MassMurder"."ARSON" "ARSON", "MassMurder"."HANG" "HANG", "MassMurder"."OTHER" "OTHER", "MassMurder"."FIREARM (unidentified)" "FIREARM (unidentified)", "MassMurder"."shotgun" "shotgun", "MassMurder"."rifle" "rifle", "MassMurder"."pistol" "pistol", "MassMurder"."machine gun" "machine gun", "MassMurder"."cause" "cause", "MassMurder"."category" "category" FROM "MassMurder" "MassMurder" WHERE ( "MassMurder"."AX" = 1 AND "MassMurder"."HATCHET" = 1 )
But as I advised earlier, the wizard has limited usefulness. Personally, I had never used the wizard until I read your question. I often begin in the Design View to get correctly spelled field names and then I switch to the SQL View to complete the query, because though more flexible than the wizard, Design View also has limitations.
By the way, a bug report would likely be futile since the Base module of OpenOffice has seen no active development since 2008.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply