Re: Converting MS Access SQL Query With INNER JOIN To Oracle SQL
Date: Fri, 10 Sep 2010 14:13:57 -0700 (PDT)
Message-ID: <3a7bb4bc-2ea7-4fdb-b877-8a656876d5a7_at_q16g2000prf.googlegroups.com>
On Sep 10, 11:52 am, Peter Schneider <pschneider1..._at_googlemail.com> wrote:
> 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! :-)
Yes, I almost put in that post that it is bad practice, I agree with you and Galen. It is an even worse problem at my site, because there is an application layer that has its own idea about masking and defaults and dates and such. But at least it is a real Oracle date and that type of query gives correct results, given the caveats you state. The app itself, on the other hand, isn't self consistent. For example, it has forms with a built-in list of values code, and an interpreter that allows things in those forms like saying %today for the current date and time. So you do a list of values for sales order headers and put in %today, and it doesn't find anything because of the time. But staying in the same form and just pressing return through it finds all of today's sales orders, because the lov has translated the masked date-only portion of %today into the equivalent of trunc(sysdate). Or something like that, one gets into strange superstitious habits after a while and it gets hard to remember why.
>
> 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 Twain
On vacation recently, I saw a Mark Twain exhibit in a museum in Angels Camp. It had on display several versions of the Calaveras story, 5 I think, of which 3 had never been published. Very interesting. http://www.angelscampmuseumfoundation.org/category/current-projects/mark-twain-exhibit/
jg
-- _at_home.com is bogus. http://www.newsnow.co.uk/h/Technology/Companies/Oracle?type=ts&TSPeriod=0Received on Fri Sep 10 2010 - 16:13:57 CDT