Re: Converting MS Access SQL Query With INNER JOIN To Oracle SQL
From: Peter Schneider <pschneider1968_at_googlemail.com>
Date: Fri, 10 Sep 2010 20:52:43 +0200
Message-ID: <i6dupl$f6p$1_at_online.de>
Am 10.09.2010 18:28, schrieb joel garry:
> On Sep 10, 8:17 am, "takv..._at_gmail.com"<takv..._at_gmail.com> wrote:
>> http://www.progneer.com/wp/information_more_public.aspx?search_fd0=13...
>
>> "Moreover, in MS Access using dates is very simple and straight-forward, all you have to do is to surround the date by # (hash symbol) but in Oracle SQL to use the date in the WHERE clause you have to use the TO_DATE function."
>
> WTF??? No, I don't.
>
> select count(*) from sales_order_headers where order_date> '01-
> jan-2010';
>
> COUNT(*)
> ----------
> 3740
>
Date: Fri, 10 Sep 2010 20:52:43 +0200
Message-ID: <i6dupl$f6p$1_at_online.de>
Am 10.09.2010 18:28, schrieb joel garry:
> On Sep 10, 8:17 am, "takv..._at_gmail.com"<takv..._at_gmail.com> wrote:
>> http://www.progneer.com/wp/information_more_public.aspx?search_fd0=13...
>
>> "Moreover, in MS Access using dates is very simple and straight-forward, all you have to do is to surround the date by # (hash symbol) but in Oracle SQL to use the date in the WHERE clause you have to use the TO_DATE function."
>
> WTF??? No, I don't.
>
> select count(*) from sales_order_headers where order_date> '01-
> jan-2010';
>
> COUNT(*)
> ----------
> 3740
>
Hi Joel,
although you are right that the original poster is just a spammer and it probably isn't worth the time, I'd like to point out a problem in your query.
If sales_order_headers.order_date is a date column (well if it is not, you are doing a lexical comparison anyway ;-) ), you are relying on an implicit conversion. This is a bad thing, because you are relying on something you don't control:
- the table column may or may not have a time value. If it has, you will most likely not get the results you expected, i.e. '01-jan-2010 10:05:55' is greater than '01-jan-2010 00:00:00'...
- the format mask implicitly used for the conversion may or may not have a time value. Do you know the mask used for sure? Can you guarantee it? If not, you will probably not get the results you are expecting.
- if the date literal has the name of the month (or the abbreviation) like in your example, you rely on the correct NLS_LANGUAGE. For example if you used '03-MAY-2010', it won't work e.g. with GERMAN_GERMANY, as in german the month is 'MAI'.
Implicit conversion will jump on you with errors with a probability of 95%! ;-)
It is better to always use an explicit format mask.
But you probably know that! :-)
Just my 2cts.
Regards
Peter
-- The only way to keep your health is to eat what you don't want, drink what you don't like, and do what you'd rather not. -- Mark TwainReceived on Fri Sep 10 2010 - 13:52:43 CDT