Solved

Multiple "where" statements that include 'or'

Posted on 2006-06-29
5
234 Views
Last Modified: 2010-04-27
This is a question that is related to the issue at: http://www.experts-exchange.com/Databases/FileMaker/Q_21859046.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_Modified_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?
0
Comment
Question by:macfundi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 19

Expert Comment

by:billmercer
ID: 17014289
I assume you're doing an import into FileMaker based on an ODBC connection?
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::fDateModified_QUERY contains the date 5/21/2006, then the final query string will look like this:
  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
0
 

Author Comment

by:macfundi
ID: 17015218
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
0
 
LVL 19

Accepted Solution

by:
billmercer earned 250 total points
ID: 17015312
OK, then to get the result you described originally, you'll probably need to create a query that looks like this:

  SELECT * FROM "orders" WHERE   ( "orders"."Date_modified" > {ts '2006-5-31 15:41:0'}
       OR "orders"."Date_created" > {ts '2006-6-01 12:24:30'} )
   AND   "orders"."Completed" = 1

Notice the parentheses around the two parts of the OR.

(I'm putting it on multiple lines to be more legible, but for ODBC with FileMaker it probably needs to be all one line.)

What exactly isn't working with what you're trying now? Are you getting an error message? Or are you just not getting the query results you expect?
0
 
LVL 19

Expert Comment

by:billmercer
ID: 17052153
Hello?
0
 

Author Comment

by:macfundi
ID: 17062644
Sorry, I totally forgot to reply. Your solution worked. Thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question