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?

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:

Shouldn't that be:

... otherwise they are just character strings?

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

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.

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

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.

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