SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL statement question "WHERE" clause

    I'm specifying the following to weed out the correct records.

    WHERE (((tbl37.col13)=recordid1) AND (tbl37.col17=0) AND (tbl37.col6 > date(now())) AND (tbl37.col19 < tbl37.col4))


    I'd like this to apply to all records:

    (((tbl37.col13)=recordid1) AND (tbl37.col17=0)
    This filters only client(recordid) and undeleted records (col17)

    And then include all records where this applies:
    (tbl37.col6 > date(now()))
    This compares end date(col6) to today's date.

    and/or this applies:
    (tbl37.col19 < tbl37.col4))
    This compares a "paid amount" field (col19) with the "total owing" field (col4).

    So in the end I'd like all records(for the specified client) that are not deleted, and that are not expired and/or have not been paid in full. I'm just not certain how to specify "and / or". Hope this makes sense.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    this is microsoft access, right? darn those stupid parentheses!
    Code:
    where tbl37.col13 = recordid1 
      and tbl37.col17 = 0 
      and (
          tbl37.col6  > now() 
       or tbl37.col19 < tbl37.col4
          )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I forgot to specify the database type (just like last time you helped me out). It's postgresql 7.4. I see how the parenthesis work to specify the and/or but I don't think it's making a difference with the now() in there. Does postgresql use something different or am I wrong here?

    Quote Originally Posted by r937
    this is microsoft access, right? darn those stupid parentheses!
    Code:
    where tbl37.col13 = recordid1 
      and tbl37.col17 = 0 
      and (
          tbl37.col6  > now() 
       or tbl37.col19 < tbl37.col4
          )

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've done some more testing on this. It works fine when I use your WHERE statement from Access(linked to tables in Postgresql) but when I run it directly against postgresql(same tables as what Access is looking at) it includes "expired" contracts. Weird. Not sure why it's acting this way. A bug in postgresql?

  6. #6
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I limited the query to simply compare the end date with NOW() without any other restrictions. This is where it's failing. It's returning all dates. I'll post anything more I find out.

  7. #7
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think everything is just fine with the query. I believe the postgresql db is posting now() incorrectly(behind a couple months). The now() date postgresql posts is different than the system date for some reason. If anyone knows how to change the time postgresql thinks is current please let me know.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •