Unanswered: Joining multiple tables and picking out latest date
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.
MAX( c.effdate )
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
I have got this at the moment but it only retrieves back 1 of the 3 invoices that are actually there.
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)
from order.grnhead a
join OpenAcc.oa_supplier b
on a.company = b.company
and a.supplier = b.supplier
join OpenAcc.oa_currency c
on b.company = c.company
and b.currency = c.currency
where c.effdate =
( select max(effdate)
where company = c.company
and currency = c.currency
and effdate <= a.rec_date
and a.company = 10
and a.loc_cd = 1
and a.rec_date between '2003-05-24'
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
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.
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