Why doesn't this SQL statement work?

[CraigBest]

I am trying to run a statement against a view as follows:

SELECT ApprLevel, AssignTo, VendorName, DeptName, PreparedBy, OnBehalfOf, CreationDate, Indicator FROM v_Tardy_Docs WHERE PreparedBy = 'Bill Harris' OR OnBehalfOf = 'Bill Harris' AND CreationDate <= '09-jul-2003'

When I run this, I get the selection of the individual fine, but the date restriction doesn't work. If I remove the selection of the individual the date restriction works fine.

I think it has something to do with the OR statement, but I can't figure out what. If I place the two parts of the OR statement in parentheses, the statement returns no records.

What am I doing wrong here?

 

[stefanhei]

Hi
Try:
SELECT ApprLevel, AssignTo, VendorName, DeptName, PreparedBy, OnBehalfOf, CreationDate, Indicator FROM v_Tardy_Docs WHERE (PreparedBy = 'Bill Harris' OR OnBehalfOf = 'Bill Harris') AND CreationDate <= '09-jul-2003'

AND is stronger than OR

Stefan

 

[CraigBest]

Stefan, thanks. but I tried that already, it didn't work. I did just figure out that the following does work:

((PreparedBy = 'Bill Harris') OR (OnBehalfOf = 'Bill Harris'))

Not sure why this works... thanks anyway.

 

[sem]

Try this:

SELECT ApprLevel, AssignTo, VendorName, DeptName, PreparedBy, OnBehalfOf, CreationDate, Indicator FROM v_Tardy_Docs WHERE (PreparedBy = 'Bill Harris' OR OnBehalfOf = 'Bill Harris') AND CreationDate <= '09-jul-2003'

Regards, Dima

 

[issahar]

Hi,
Try something like:

SELECT ApprLevel, AssignTo, VendorName, DeptName, PreparedBy, OnBehalfOf, CreationDate, Indicator FROM v_Tardy_Docs WHERE PreparedBy = 'Bill Harris' OR OnBehalfOf = 'Bill Harris' AND CreationDate <= TO_DATE('09-jul-2003','DD/MM/YYYY')



Issahar Noam Gourfinkel
senior software engineer

Warecase

http://www.warecase.com

Israel

 

[zMonk]

just a small note instead of &quot;TO_DATE('09-jul-2003','DD/MM/YYYY')&quot; do &quot;TO_DATE('09/07/2003','DD/MM/YYYY')&quot; - slashes and month format will error out ....

 

[CraigBest]

Thanks folks, but the 09-jul-2003 format I'm passing in works just fine (in fact the reason I do that is that I've tried other ways in the past and this one was the most reliable (still is). but I'll keep a note of the to-date function and try it in the future. Thanks.

Craig in NJ

 

[zMonk]

Craig,
if you use date format the way you do you rely on the format provided by a session at a particular login. But, if you use to_date()date will not have any chances to error out!

 

[sem]

Please don't ignore Stefan's my posts. Your date constraint is applied only to OnBehalfOf records thus your query is similar to:

SELECT ApprLevel, AssignTo, VendorName, DeptName, PreparedBy, OnBehalfOf, CreationDate, Indicator FROM v_Tardy_Docs WHERE PreparedBy = 'Bill Harris'
union
SELECT ApprLevel, AssignTo, VendorName, DeptName, PreparedBy, OnBehalfOf, CreationDate, Indicator FROM v_Tardy_Docs WHERE OnBehalfOf = 'Bill Harris' AND CreationDate <= '09-jul-2003'

Is it really what you want?

Regards, Dima

 

[CraigBest]

Dima, I'm not sure what you are getting at. I have that portion of the query set up as follows:

'... WHERE ((PreparedBy = 'Bill Harris') OR (OnBehalfOf = 'Bill Harris')) AND CreationDate <= '09-jul-2003'

...and it appears to be working correctly, as far as I have been able to determine. When I tried the query as you and Stefan suggested (one pair of parentheses), I got back no records (which was incorrect). I'm not seeing the results you predicted.

Believe me I'm not ignoring you and really do appreiciate the advice, but I don't understand what I'm doing wrong when I'm getting back the results I want... As far as I can see the double parentheses are getting the job done.

 

[SantaMufasa]

Craig,

Your situation reminds me of the guy whose engine was really clanking, but when his buddy threw a couple of handsful of sawdust in the crankcase, it got rid of the noise. We all know it didn't solve the problem; it just sounded better.

That's what is happening with your query: just because you are getting better results doesn't mean you are getting the &quot;correct&quot; results. First of all, the date situation is a definite failure point. As Issahar mentioned, you really DO need the to_date function; without it, you receive totally coincidental results. Without to_date, your WHERE clause is just doing a straight binary/bit comparison between Oracle's internal date format and the literal characters '09-jul-2003'...which is not even a close comparison. I'll illustrate with the following simple data DUMPs:

select dump('09-jul-2003') from dual;
Typ=96 Len=11: 48,57,45,106,117,108,45,50,48,48,51

select dump(to_date('09-jul-2003')) from dual;
Typ=13 Len=8: 7,211,7,9,0,0,0,0

The first format (without to_date function) produces an 11-byte, character (Type 96) result. The second format (Oracle's standard 8-byte date format, Type 13) is nothing close to the first results. The fact that you are getting output from your SELECT is strictly because the binary representation of (Oracle) dates in your table are coincidentally &quot;less than&quot; the ASCII representation of '09-jul-2003'.

Here is another (less theoretical) example:

Select * from dual where sysdate = '25-Jul-2003';
no rows selected

Select * from dual where sysdate = to_date('25-Jul-2003');
no rows selected

(Surprise, surprise: even this doesn't display records...That is because Oracle dates ALWAYS contain not only the date, but also the time. If we supply no time factor for '25-Jul-2003', then the default time is midnight on the morning of the date (&quot;20030725000000&quot. Sysdate contained &quot;20030725101757&quot;: 25-Jul-2003 @ 10:17:57 a.m. So, to ignore the TIME factor of SYSDATE we must truncate the time:

Select * from dual where trunc(sysdate) = to_date('25-Jul-2003');
DUMMY
------
X

1 row selected.

So, you MUST use the TO_DATE function (and most likely a TRUNC function) when attempting a comparison between a literal and a DATE expression.

Secondly, the order of evaluation without parentheses in Oracle's SQL (and most other programming languages) is:
1) arithmetic expressions (-,+ before *,/)
2) comparison operators (=, >, <, >=, <=, <>)
3) NOT
4) AND
5) OR

Therefore, without parentheses, your WHERE clause SELECTs data for rows that:
1) PreparedBy = 'Bill Harris' [Specifically, all Bill Harris-prepared rows no matter what date]
or
2) OnBehalfOf = 'Bill Harris' AND CreationDate <= '09-jul-2003' [Specifically, OnBehalfOf-Bill Harris rows whose dates compare less than the spurious '09-jul-2003' character string.]

I recommend always using parentheses to 'force' Oracle to evaluate in the order you want rather than leave order of evaluation up to Oracle's defaults.

Cheers,

Dave Hunt

 

[CraigBest]

OK, I think you've got me convinced. I'll give it a try and see how it comes out. Thanks for bearing with me.

Craig in NJ

 

[sem]

Dave, I'm sure that Oracle doesn't compare binary representatios, but rather makes some implicit casts:



SQL> alter session set nls_date_format='dd.mm.rr';

Session altered.

SQL> select 1 from dual where '01.01.03' = to_date('01.01.03');

1
----------
1

SQL> select 1 from dual where '01.01.03' = to_date('02.01.03');

no rows selected



Regards, Dima

 

[SantaMufasa]

Dima,

Granted, Oracle, does &quot;re-cast&quot; into a common format for comparison purposes. In the example you give (&quot;select 1 from dual where '01.01.03' = to_date('01.01.03');&quot one operand is of type CHARACTER, the other operand is of type DATE. In such cases, Oracle converts the date operand to characters based upon the current NLS_DATE_FORMAT. Oracle does not somehow internally convert the character operand ('01.01.03') to a date.

The correct and safe principle being: when comparisons involve dates, don't allow Oracle to use character comparisons; explicity ensure that if any comparison operand is of type DATE, that other comparison operands are also of type DATE.

 

[sem]

Yes, I know this, but in any case Oracle doesn't compare dumps

As for your statement about converting dates to strings but not vice versa, one more test:

SQL> create table date_strings(a varchar2(8));

Table created.

SQL> insert into date_strings values(trunc(sysdate));

1 row created.

SQL> select * from date_strings where a=sysdate;

no rows selected

SQL> select * from date_strings where a=trunc(sysdate);

A
--------
28.07.03

SQL> insert into date_strings values('ERROR');

1 row created.

SQL> select * from date_strings where a=trunc(sysdate);
ERROR:
ORA-01858: a non-numeric character was found where a numeric was expected



no rows selected


I suppose that this PROVES that Oracle tries to convert varchar2 column to date to compare it with DATE parameter.

Regards, Dima