Funny problem with table selection
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Funny problem with table selection

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    select * from mwebrepseloptions where repopt_required not in (10, 20) ....... returns 0
    select * from mwebrepseloptions where repopt_required is not null ....returns 139 (correct)
    select * from mwebrepseloptions where repopt_required is null ....returns 8 (correct)
    select * from mwebrepseloptions where repopt_required <>10 or repopt_required <>20 ....returns 139(wrong)
    select * from mwebrepseloptions where repopt_required =10 or repopt_required =20....returns 139(might be correct)
    select count(*) from mwebrepseloptions... returns total 147(correct)

    Repopt_required Column is defined as number.


    My problem is why
    select * from mwebrepseloptions where repopt_required <>10 or repopt_required <>20 ....returns 139(wrong)
    select * from mwebrepseloptions where repopt_required =10 or repopt_required =20....returns 139(might be correct)


    these 2 queries return same number of rows(rather same rows) when <> should return 8 rows and not 139 rows ???


    thanks

    Sonali

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    The query is writtin incorrectly

    You want to say:

    select * from mwebrepseloptions where repopt_required <>10 AND repopt_required <>20

    Otherwise, you get all non-null records, because those that are 10 are NOT 20 and those that are 20 are NOT 10, so they all satisfy the query.


    Note that NULLS ALWAYS return false for = > < *or* <>

    The only way to find nulls is with IS NULL.

    HTH

    - Chris


  3. #3
    Join Date
    Jan 2001
    Posts
    318
    thanks a lot Chris
    Sonali

Posting Permissions

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


Click Here to Expand Forum to Full Width