Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2012
    Posts
    5

    Question Unanswered: Strange case of "!=" and "AND" not returning an expected result

    Hello,
    I need to select rows from a table, excluding rows having col2='O' AND 'EA' in BOXCODE column.
    Rows with col2='O' where BOXCODE is equal to any other value except 'EA' need to be included into the result.
    I created a test table, inserted a few rows:

    create table test56(col1 varchar2(1),col2 varchar2(1), boxcode varchar2(2))
    insert into test56 values ('1' , 'O', 'EA')
    insert into test56 values ('2' , 'O', 'BB')
    insert into test56 values ('3' , '1', 'EA')

    I need to return these rows:
    ('2' , 'O', 'BB')
    ('3' , '1', 'EA')

    Here is my SQL:
    select * from test56
    where col2!='O' AND BOXCODE !='EA'
    /

    No rows returned:
    COL1 COL2 BOXCODE
    ------- ------- ----------

    0 record(s) selected

    Why?? Oh why?? Any explanation?

    Thanks for your input

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by Milenna View Post
    Hello,
    I need to select rows from a table, excluding rows having col2='O' AND 'EA' in BOXCODE column.
    Rows with col2='O' where BOXCODE is equal to any other value except 'EA' need to be included into the result.
    I created a test table, inserted a few rows:

    create table test56(col1 varchar2(1),col2 varchar2(1), boxcode varchar2(2))
    insert into test56 values ('1' , 'O', 'EA')
    insert into test56 values ('2' , 'O', 'BB')
    insert into test56 values ('3' , '1', 'EA')

    I need to return these rows:
    ('2' , 'O', 'BB')
    ('3' , '1', 'EA')

    Here is my SQL:
    select * from test56
    where col2!='O' AND BOXCODE !='EA'
    /

    No rows returned:
    COL1 COL2 BOXCODE
    ------- ------- ----------

    0 record(s) selected

    Why?? Oh why?? Any explanation?

    Thanks for your input
    which single record has both col2!='O' AND BOXCODE !='EA'

    >I need to return these rows:
    then use below
    WHERE col2!='O' OR BOXCODE !='EA'
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2014
    Location
    World Wide On The Web
    Posts
    16
    Quote Originally Posted by Milenna View Post
    I need to return these rows:
    ('2' , 'O', 'BB')
    ('3' , '1', 'EA')
    those rows would be returned ONLY when you specify an OR condition as already suggested. But, if you want to use AND condition, then you MUST have a row which satisfies both conditions together.

    To test and understand, insert the following record and execute your AND query -

    Code:
    INSERT INTO test56 VALUES ('4' , '1', 'XY');
    Regards,
    Lalit

  4. #4
    Join Date
    Sep 2012
    Posts
    5
    I see. It works with OR just fine, even though, since I am looking for the rows that satisfy BOTH "=" conditions, using "AND" seemed more appropriate.


    Thanks!

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Have a look at DeMorgan's laws.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by Milenna View Post
    I see. It works with OR just fine, even though, since I am looking for the rows that satisfy BOTH "=" conditions, using "AND" seemed more appropriate.


    Thanks!
    WRONG!
    it is NOT both "=".
    it is both "!="; which reverses the logic
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The easiest way is

    select * from test56
    where not (col2='O' AND BOXCODE ='EA')
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •