Where boolean and broken query wizard

Discuss the database features

Where boolean and broken query wizard

Postby claytoncramer » Mon Oct 08, 2018 7:05 am

Importing my spreadsheet was easy, but when I used the query wizard, I asked to see records where year =1911 (or "1911" in case someone was confused about its type), I got all records. Here is the SQL the wizard created:
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"


Shouldn't there be a WHERE clause? So I added "where "MassMurder"."year" = 1911" to the SQL and saving it worked. When I wanted to add another WHERE clause to test for boolean values:
Code: Select all   Expand viewCollapse view
where "MassMurder"."AX" = On OR "MassMurder"."HATCHET" = On

I tried TRUE, 1, On. What should I be testing against?
OpenOffice 4.1.6
claytoncramer
 
Posts: 6
Joined: Mon Oct 08, 2018 6:59 am

Re: Where boolean and broken query wizard

Postby robleyd » Mon Oct 08, 2018 11:11 am

What type are the fields you want to select?
Cheers
David
Apache OpenOffice 4.2.0 Build 9820 alpha version - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2629
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Where boolean and broken query wizard

Postby UnklDonald418 » Mon Oct 08, 2018 5:21 pm

For each FROM you can have a single WHERE clause.
Assuming "MassMurder"."year" is a numeric type field plus "MassMurder"."AX" and "MassMurder"."HATCHET" are Boolean fields, try
Code: Select all   Expand viewCollapse view
WHERE "MassMurder"."year" = 1911
AND  ("MassMurder"."AX" = TRUE OR "MassMurder"."HATCHET" = TRUE)

If "MassMurder"."year" is text then use '1911'
 Edit: If "MassMurder"."date" is a Date type field in the table "MassMurder", try this where clause
Code: Select all   Expand viewCollapse view
WHERE YEAR("MassMurder"."date") = 1911
AND  ("MassMurder"."AX" = TRUE OR "MassMurder"."HATCHET" = TRUE)
 
Last edited by UnklDonald418 on Mon Oct 08, 2018 10:16 pm, edited 2 times in total.
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1121
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Where boolean and broken query wizard

Postby UnklDonald418 » Mon Oct 08, 2018 5:31 pm

It just occurred to me, with all those potential search options you should look at this example
[Example #3] Filter/Search with Forms (leveraging SubForms)
Download the sample database "LIKE_user_input_filter.odb"
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1121
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA


Return to Base

Who is online

Users browsing this forum: Google [Bot] and 3 guests