[Solved] Query Problems

Creating tables and queries

[Solved] Query Problems

Postby fatcat » Thu Mar 24, 2016 4:42 am

Trying to do a simple query to isolate month using the following;

SELECT DISTINCT "ID" AS "ID", "FIRST NAME" AS "FIRST NAME", "LAST NAME" AS "LAST NAME", "BIRTH DAY" AS "BIRTH DAY", "BIRTH MONTH" AS "BIRTH MONTH" FROM "THSCTABLE" WHERE "BIRTH MONTH" = 'october'

results come up blank for every month, so I started fooling around with it a bit and replaced WHERE "BIRTH MONTH" = 'october' with WHERE "LAST NAME" = 'smith' (a random last name) and it came back with all the smiths in the database. Then tried it with another last name and it came up blank, then with another last name and it hit all those, I kept trying last names that I know are in the database and it is hit and miss as to whether or not I get results. I have also done it with first name and it is also hit and miss.

any ideas for this newby.
Last edited by Hagar Delest on Fri Mar 25, 2016 10:33 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.2 on Windows 7
fatcat
 
Posts: 3
Joined: Thu Mar 24, 2016 4:22 am

Re: Query Problems

Postby UnklDonald418 » Thu Mar 24, 2016 4:58 pm

After a quick look at your problem I wonder if the issue is with "BIRTH MONTH" = 'october'
Look at your table and make sure of your spelling and case. In SQL 'october' is not the same as 'October".
Also try SELECT instead of SELECT DISTINCT.
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.7 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1286
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Query Problems

Postby eremmel » Thu Mar 24, 2016 8:45 pm

When you have an issue with capitals in your fields, try: WHERE LOWER("BIRTH MONTH") = 'october'.

Removing the word DISTINCT will not help with your issue, but one should use DISTINCT with care 'cause most of times its use 'solves' buggy queries and that is not what you want.
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1041
Joined: Tue Dec 30, 2008 1:15 am

Re: Query Problems [SOLVED]

Postby fatcat » Thu Mar 24, 2016 10:53 pm

Thanks for your help, made me realize the the problem was more basic than expressions. Went back and checked the database table and discovered that all of queries that where not coming back as expected was a result of the data in the cell having spaces behind them. Remove the spaces and the query works. Now I just need an easy way to remove all the spaces in my database.

Thanks again
OpenOffice 4.1.2 on Windows 7
fatcat
 
Posts: 3
Joined: Thu Mar 24, 2016 4:22 am

Re: Query Problems [SOLVED]

Postby eremmel » Thu Mar 24, 2016 11:06 pm

fatcat wrote: Now I just need an easy way to remove all the spaces in my database.
Thanks again


Run a query
Code: Select all   Expand viewCollapse view
UPDATE "table1" SET "field1"=RTRIM("field1"), "field2"=RTRIM("field2"), ....
You can run this type of query via (out of my head): menu -> Tools -> SQL...
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1041
Joined: Tue Dec 30, 2008 1:15 am

Re: Query Problems [SOLVED] again

Postby fatcat » Fri Mar 25, 2016 12:24 am

Thanks for that eremmel. Just ran the suggested query and everything is perfect. Cant tell you how much I appreciate the time savings
OpenOffice 4.1.2 on Windows 7
fatcat
 
Posts: 3
Joined: Thu Mar 24, 2016 4:22 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests