Re: Difference between SYSDATE and TO_DATE

Wolfgang, you didn't read very carefully before you got on your soapbox

Tine was illustrating how the BETWEEN operator works, not how multiple predicates are evaluated.

This is the issue, in its simplest form:

SQL> select *
  2 from dept
  3 where deptno between 30 and 10;

no rows selected

SQL> select *
  2 from dept
  3 where deptno between 10 and 30;

    DEPTNO DNAME LOC
---------- -------------- -------------

        10 ACCOUNTING     New York, NY
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO

"Wolfgang Breitling" <john.doe_at_aol.com> wrote in message news:Xns9424C73239E7Fbreitliwcentrexcccom_at_198.161.157.145...
> Comment in-line:
> Tine Zorko <tine.zorko_at_email.si> wrote in
> news:bnpcse$r21$1_at_planja.arnes.si:
>
> > You should take a second look at your code.
> > If BETWEEN evaluation operator has left AND right arguments then:
> >
> > 1st SELECT:
> > left argument < right argument (LESS)
> > (28-oct-03) < (29-oct-03)
> >
> > 2nd SELECT
> > left argument > right argument (GREATER)
> > sysdate > sysdate - 1
> >
> > There is AND logical operator between both arguments.
> > As with most recent programming languages Oracle also
> > evaluates truth of 1st argument and if not true it is
> > logical, 2nd argument makes no sense:
> >
> > Arg1 AND Arg2 Outcome
> > 1 1 TRUE, 2nd argument will be considered in evaluation
> > 1 0 FALSE, 2nd argument will be considered in evaluation
> > 0 0 FALSE, only 1st argument is evaluated
> > 0 1 FALSE, only 1st argument is evaluated <- YOUR CASE!!
> >
> > BETWEEN can be interpreted like:
> >
> > operand > arg1 AND operand < arg2
> >
>
> This is total rubbish. The order of evaluation of the predicates is
> indeterminate. SQL is not a procedural language. With it you specify what
> your resultset should be, but the relational database determines how to
> get it, and that HOW can change from execution to execution.
> If you were right, the following could not happen:
>
> Create table p_order (
> SSN VARCHAR2(10),
> BIRTH_date DATE,
> ZIP VARCHAR2(10));
>
> insert into p_order values('8328486572', '31-OCT-03', '27722');
> insert into p_order values('8397333761', '31-OCT-03', 'A1180');
> insert into p_order values('8358002539', '31-OCT-03', '81225');
> insert into p_order values('8353962080', '31-OCT-03', '43933');
>
> SQL> select * from p_order;
>
> SSN BIRTH_DAT ZIP
> ---------- --------- ----------
> 8328486572 31-OCT-03 27722
> 8397333761 31-OCT-03 A1180
> 8358002539 31-OCT-03 81225
> 8353962080 31-OCT-03 43933
>
> 4 rows selected.
>
> SQL> select * from p_order
> 2 where ssn like '835%'
> 3 and birth_date = '31-OCT-03';
>
> SSN BIRTH_DAT ZIP
> ---------- --------- ----------
> 8358002539 31-OCT-03 81225
> 8353962080 31-OCT-03 43933
>
> SQL> select * from p_order
> 2 where ssn like '835%'
> 3 and birth_date = '31-OCT-03'
> 4 and zip = 43933;
> and zip = 43933
> *
> ERROR at line 4:
> ORA-01722: invalid number
>
> If Oracle did evaluate the predicates in the order specified, by the time
> it gets to evaluate zip = 43933 (which requires a to_number conversion)
> the zips ARE numbers and the conversion would succeed. However, it
> obviously evaluates "zip = " before "ssn like".
>
> Unless you specifically request it with the ORDERED_PREDICATES hint:
>
> SQL> select /*+ ORDERED_PREDICATES */ * from p_order
> 2 where ssn like '835%'
> 3 and birth_date = '31-OCT-03'
> 4 and zip = 43933;
>
> SSN BIRTH_DAT ZIP
> ---------- --------- ----------
> 8353962080 31-OCT-03 43933
>
> 1 row selected.
>
> --
> What lies behind us and what lies before us are small matters when
> compared to what lies within us.
>
> Wolfgang Breitling
> Oracle 7, 8, 8i, 9i OCP
Received on Thu Oct 30 2003 - 22:03:41 CST