Home » SQL & PL/SQL » SQL & PL/SQL » Should the order of the where clause affect result sets
Should the order of the where clause affect result sets [message #2173] Wed, 26 June 2002 00:40 Go to next message
Anthony Wong
Messages: 2
Registered: June 2002
Junior Member
Look the following 2 sqls that are accessing the same db. I would expect both of them should give me the "invalid number" error. This sql is run against Oracle 9.2. The only different is line 3 and 4 have been swopped in the 2nd sql.

Could anyone understand??

1 select COUNT(1)
2 from tb_commission_money
3 where TO_NUMBER(prem_yr) > 6
4 AND prem_yr not in ('S', 'N', 'RS')
5 --AND TO_NUMBER(prem_yr) > 6
6 -- and type_code in ('BASE')
7* -- AND IAC_BAL_MP_ID = '2297680'
SQL> /
where TO_NUMBER(prem_yr) > 6
*
ERROR at line 3:
ORA-01722: invalid number

1 select COUNT(1)
2 from tb_commission_money
3 where prem_yr not in ('S', 'N', 'RS')
4 AND TO_NUMBER(prem_yr) > 6
5 --AND TO_NUMBER(prem_yr) > 6
6 -- and type_code in ('BASE')
7* -- AND IAC_BAL_MP_ID = '2297680'
SQL> /

COUNT(1)
---------
538

Report message to a moderator

Re: Should the order of the where clause affect result sets [message #2176 is a reply to message #2173] Wed, 26 June 2002 02:08 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
The problem here is: you're doing some implicit character to number conversion. All depends on what Oracle executes first. If all lines are eliminated that contain a non-numeric character and then you evaluate it with a number, Oracle uses implicit character-to-number conversion. But if Oracle evaluates the line with the numeric comparison first, it gives the 'invalid number', since the lines that contain a non-numeric character first.

It's strange that these lines give the error like that, because normally the where clause that contains only 'AND' keywords, is being evaluated Bottom-Up, and not Top-Down.

MHE

Report message to a moderator

Re: Should the order of the where clause affect result sets [message #2221 is a reply to message #2173] Fri, 28 June 2002 13:48 Go to previous message
Andrew
Messages: 144
Registered: March 1999
Senior Member
If you are using the cost based optimizer, then the order of the where clause should be irrelevant. Use the CBO by analyzing at least one of the tables in the query:

analyze table tb_commission_money estimate statistics;

Else rule based optimizer kicks in.

Report message to a moderator

Previous Topic: CURSORS
Next Topic: Database triggers
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue May 07 03:12:56 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.