Re: help with replacing OR in query

vismaymandloi_at_hotmail.com (vismay) wrote in message news:<2954112e.0208130120.77c42774_at_posting.google.com>...
> dear erick,
> insted of putting OR just Put AND if you want that both the
> constraints must be satisfied. like
> select xyz from table1
> where col1=* and col2=*;
> my email is
> vismaymandloi_at_rediffmail.com

Using "AND" will give you wrong resultset. Here is why, (COL2 = "XXX" OR COL3 = "XXX") will return TRUE if either COL2 or COL3 = "XXX" thus the row will be returned.
(COL2 = "XXX" AND COL3 = "XXX") will return TRUE *only* if both COL2 and COL3 = "XXX" thus any row returning FALSE on above operation will not be returned. Example below.

SQL> create table foo (test1 varchar2(10), test2 varchar2(10));

Table created.

SQL> insert into foo values ('XXX', 'XXX');

1 row created.

SQL> insert into foo values ('XXX', 'YYY');

1 row created.

SQL> insert into foo values ('ZZZ', 'XXX');

1 row created.

SQL> COMMIT; Commit complete.

SQL> select * from foo where test1 = 'XXX' AND test2 = 'XXX';

TEST1 TEST2
---------- ----------
XXX XXX SQL> select * from foo where test1 = 'XXX' OR test2 = 'XXX';

TEST1 TEST2
---------- ----------

XXX        XXX
XXX        YYY
ZZZ        XXX

It is a wrong approach to use incorrect OPERATOR or change business logic just because one wants to use constraints. Refer to Jim Kennedy's post above for correct solution.

//Rauf Sarwar Received on Tue Aug 13 2002 - 19:20:01 CEST