Re: Will an SQL where statement with an OR clause work?

G wrote:

> I'm hitting an Oracle 9.2 DB. Will an SQL where statement with an OR
> clause properly work?
>
> For example:
>
> Select * from mytable where mfield = corn or myfield = oats
>
> I would also appreciate any help with the syntax
>
> Thanks.
>
> G

Yes, it will work 'properly'. However, your interpretation of 'properly' may not be the same as the database's definition <g>

SELECT *
  FROM mytable
 WHERE mfield = 'corn'

    OR myfield = 'oats'

will select all rows where column _mfield_ contains lower case 'oats' and all rows where column _myfield_ contains lower case 'corn'

Comments:
- character data literals must be in single-quotes and are case sensitive. - use functions as described in the SQL Language Reference manual

   (manuals are found at http://docs.oracle.com) - consider 'IN' or 'EXISTS' for lists

    myfield IN ('CORN', 'OATS')

- use OR when selecting from different columns
- use parenthesis '()' to prioritize and group 
- recognize that OR is generally slower than AND
- create some sort of statement layout standard (see my example)

    occasionally helps identify typos

O'Reilly's book 'Mastering Oracle SQL' is a great, and inexpensive, treatise on the language. It shows so many tricks that the average programmer gets 100% ROI withing a few weeks.

/Hans Received on Fri Aug 06 2004 - 14:53:25 CDT