[Solved] Query Problems

[fatcat]

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.

[UnklDonald418]

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.

[eremmel]

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.

[fatcat]

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

[eremmel]

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

Run a query

Code: Select all

UPDATE "table1" SET "field1"=RTRIM("field1"), "field2"=RTRIM("field2"), ....

You can run this type of query via (out of my head): menu -> Tools -> SQL...

[fatcat]

Thanks for that eremmel. Just ran the suggested query and everything is perfect. Cant tell you how much I appreciate the time savings