SQL in Base Does Not Work As I Expect

Discuss the database features

SQL in Base Does Not Work As I Expect

Postby claytoncramer » Tue Oct 09, 2018 1:13 am

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   Expand viewCollapse view
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   Expand viewCollapse view
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
claytoncramer
 
Posts: 6
Joined: Mon Oct 08, 2018 6:59 am

Re: SQL in Base Does Not Work As I Expect

Postby Sliderule » Tue Oct 09, 2018 2:02 am

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

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
    -- 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.
User avatar
Sliderule
Volunteer
 
Posts: 1148
Joined: Thu Nov 29, 2007 9:46 am

Re: SQL in Base Does Not Work As I Expect

Postby UnklDonald418 » Tue Oct 09, 2018 5:59 pm

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.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 965
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SQL in Base Does Not Work As I Expect

Postby claytoncramer » Tue Oct 09, 2018 7:09 pm

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   Expand viewCollapse view
alter table "MassMurder" alter column "dead victim count" SMALLINT
produces
Code: Select all   Expand viewCollapse view
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
claytoncramer
 
Posts: 6
Joined: Mon Oct 08, 2018 6:59 am

Re: SQL in Base Does Not Work As I Expect

Postby UnklDonald418 » Tue Oct 09, 2018 8:18 pm

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.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 965
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SQL in Base Does Not Work As I Expect

Postby Sliderule » Tue Oct 09, 2018 8:21 pm

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   Expand viewCollapse view
alter table "MassMurder" alter column "dead victim count" SMALLINT
produces
Code: Select all   Expand viewCollapse view
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.
User avatar
Sliderule
Volunteer
 
Posts: 1148
Joined: Thu Nov 29, 2007 9:46 am

Re: SQL in Base Does Not Work As I Expect

Postby claytoncramer » Wed Oct 10, 2018 7:00 pm

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   Expand viewCollapse view
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

Postby claytoncramer » Wed Oct 10, 2018 7:03 pm

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

Postby claytoncramer » Wed Oct 10, 2018 7:06 pm

More screenshots of the Query Wizard
Attachments
QueryWizard7.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

Postby UnklDonald418 » Wed Oct 10, 2018 9:02 pm

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   Expand viewCollapse view
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.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 965
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA


Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests