Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: Joining multiple tables and picking out latest date

    Hi peeps,

    Please help with this query. I am extracting invoice details and wish to join this up with another table and extract the exchange rate on that day. Problem is that the exchange rate is not held every day so I wish to extract the previous one.

    SELECT
    a.supplier,
    a.rec-date,
    a.kgrno,
    b.currency,
    c.rate,
    MAX( c.effdate )
    FROM
    order.grnhead a,
    OpenAcc.oa_supplier b,
    OpenAcc.oa_currency c
    WHERE
    b.company = a.company AND
    b.supplier = a.supplier AND
    c.company = b.company AND
    c.currency = b.currency AND
    c.effdate <= a.rec-date AND
    a.company = 10 AND
    a.loc-cd = 1 AND
    ( a.rec-date >= 5/24/3 AND
    a.rec-date <= 5/30/3) AND
    GROUP BY
    b.currency

    I have got this at the moment but it only retrieves back 1 of the 3 invoices that are actually there.

    Any ideas?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i am suprised that query even runs

    you cannot have dashes in column names, there's a hanging AND in front of the GROUP BY, and the date strings are actually arithmetic expressions (e.g. 5 divided by 24 divided by 3)

    try this:
    PHP Code:
    select a.supplier
         
    a.rec_date
         
    a.kgrno
         
    b.currency
         
    c.rate
         
    c.effdate
      from order
    .grnhead a
    inner
      join OpenAcc
    .oa_supplier b
        on a
    .company b.company 
       
    and a.supplier b.supplier 
    inner
      join OpenAcc
    .oa_currency c 
        on b
    .company c.company 
       
    and b.currency c.currency 
     where c
    .effdate 
           ( 
    select max(effdate)
               
    from OpenAcc.oa_currency
              where company 
    c.company
                
    and currency c.currency 
                
    and effdate <= a.rec_date 
           
    )            
       and 
    a.company 10 
       
    and a.loc_cd 
       
    and a.rec_date between '2003-05-24'      
                          
    and '2003-05-30' 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Shouldn't that be:

    PHP Code:
       and a.rec_date between DATE '2003-05-24'      
                       
    and DATE '2003-05-30' 
    ... otherwise they are just character strings?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    shouldn't that be...
    perhaps in your database

    in most databases, a comparison between a column of datetime datatype and a literal string will invoke implicit conversion of the string to datetime datatype

    i believe that's the sql standard as well

    perhaps you were thinking of
    PHP Code:
    and a.rec_date between CAST('2003-05-24' as DATETIME)
                       and 
    CAST('2003-05-30' as DATETIME
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by r937
    perhaps in your database

    in most databases, a comparison between a column of datetime datatype and a literal string will invoke implicit conversion of the string to datetime datatype

    i believe that's the sql standard as well

    perhaps you were thinking of
    PHP Code:
    and a.rec_date between CAST('2003-05-24' as DATETIME)
                       and 
    CAST('2003-05-30' as DATETIME
    Well, I am heavily biased towards Oracle, but Oracle have added the DATE '2003-05-24' format to their repertoire relatively recently, and describe it as ANSI syntax.

    Oracle also does an implicit conversion of strings to dates via its TO_DATE function, based on a format mask that can be set previously but generally defaults to 'DD-MON-YY'. In Oracle circles, relying on implicit conversions is heavily frowned upon, because you can never be sure what the implicit format mask has been set to. Instead you are advised to do TO_DATE('2003-05-20','YYYY-MM-DD') or whatever - or use the new "ANSI" DATE syntax above.

    I guess if the implicit mask were fixed as 'YYYY-MM-DD' and could not be changed, that wouldn't be an issue. But for historic reasons, Oracle won't do that.

    That's probably more than you ever wanted to know about Oracle dates, but anyway my main point is that according to Oracle DATE '2003-05-24' is ANSI syntax for a DATE constant.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my main point is that according to Oracle DATE '2003-05-24' is ANSI syntax for a DATE constant.
    thanks, i did not know this

    and no, it was not "more than you ever wanted to know about Oracle dates"

    i want to know everything about every feature, every syntax variation of every database

    and when i forget, i want google to find it for me

    by the way, google consistently returns dbforum.com posts ranked quite highly amongst other results when i'm searching for sql information, which especially pleases when i end up finding a post by myself that i had totally forgotten about
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    BTW, do you know of any website that documents the latest, full ANSI syntax for SQL? I know where to find Oracle SQL (of course), MS SQL Server SQL and even mySQL, but not ANSI.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't think the official sql standard is online

    you can buy it, of course

    however, anytime i've wanted to know if something is standard, i use the Mimer SQL Validator
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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