macfundi
asked on
Multiple "where" statements that include 'or'
This is a question that is related to the issue at: https://www.experts-exchange.com/questions/21859046/Peform-multiple-'Where'-statements-in-SQL-query-for-Filemaker-update.html
I'm updating a FM8 table from an SQL table with new records and modified records. Below are the 3 ways that i've decided to update it...
• I am performing an SQL query that needs to update records that are greater than the greatest (by date) modified record in the current db
• It also needs to add any records that are greater than the greatest 'added' date.
• It needs to only get the records that have a 'completed' value of '1'
Curently my query reads:
" SELECT * FROM \"orders\" WHERE \"orders\".\"Date_modified \" > " & Orders_MEMBERS::fDate_Modi fied_QUERY & " and \"orders\".\"Completed\" = 1"
I've tried adding an 'or' query before the 'and' operation (even tried removing the 'and' operation), but i just can seem to get it to work. What is the best way to do this?
I'm updating a FM8 table from an SQL table with new records and modified records. Below are the 3 ways that i've decided to update it...
• I am performing an SQL query that needs to update records that are greater than the greatest (by date) modified record in the current db
• It also needs to add any records that are greater than the greatest 'added' date.
• It needs to only get the records that have a 'completed' value of '1'
Curently my query reads:
" SELECT * FROM \"orders\" WHERE \"orders\".\"Date_modified
I've tried adding an 'or' query before the 'and' operation (even tried removing the 'and' operation), but i just can seem to get it to work. What is the best way to do this?
ASKER
Sorry, I left out a few details. I'm using timestamps (a calculation converts the timestamp into SQL format) to do the comparison. Here's an example of what the above looks like in the SQL query (via the ODBC driver log)....
SELECT * FROM "orders" WHERE "orders"."Date_modified" > {ts '2006-5-31 15:41:0'} and "orders"."Completed" = 1
SELECT * FROM "orders" WHERE "orders"."Date_modified" > {ts '2006-5-31 15:41:0'} and "orders"."Completed" = 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hello?
ASKER
Sorry, I totally forgot to reply. Your solution worked. Thanks!
If so, you may need to escape the date value to make it work.
The string you provided is the FileMaker calculation to create the query. You will want to look at the actual result of this calculation to see exactly what is being sent to the SQL
If we assume that the field Orders_Members::fDateModif
SELECT * FROM "orders" WHERE "orders"."Date_modified" > 5/21/2006 and "orders"."Completed" = 1
The problem is, how does the ODBC driver know that 5/21/2006 is a date, and not a math expression?
To avoid this ambiguity, ODBC provides special syntax for this kind of stuff in queries. The data is formatted in a standard way, ( YYYY-MM-DD for , then enclosed in single quotes, then that is enclosed that in curly braces with a letter to indicate the data type, d for date, t for time, etc.)
For example, instead of entering 5/21/2006 for the date, you'd enter { d '2006-05-21' }
It's ugly, but it's the established way to standardize how dates and times are formatted with ODBC queries.
Here's more information...
http://msdn2.microsoft.com/en-us/library/ms190234.aspx