how to get values not present in the inlist
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: how to get values not present in the inlist

  1. #1
    Join Date
    Dec 2002
    Posts
    110

    how to get values not present in the inlist

    hi

    i have a table t (col number);

    with say 5 values
    1
    2
    3
    4
    5

    when i query select * from t where col in (1,2,3);
    it will
    output
    1
    2
    3


    what i wnat is something like this
    when i query select * from t where col in (1,2,3,6,7);

    it should give me
    6
    7

    values which are not part of the table


    Rgrds
    Sushant

  2. #2
    Join Date
    Dec 2002
    Posts
    110
    slight error in writing above

    what i want is a query which can give the values which i
    have passed in the list but are not part of the table

  3. #3
    Join Date
    Jan 2002
    Posts
    148

    Re: how to get values not present in the inlist

    Originally posted by zulu99

    what i wnat is something like this
    when i query select * from t where col in (1,2,3,6,7);

    it should give me
    6
    7

    values which are not part of the table


    When you say "Select * from table" then how do u expect values from a list ?
    Convert that list to a lookup table and then do a outerjoin query.
    Jr
    Last edited by Jr; 07-01-2003 at 12:50 PM.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Not that it's pretty nor is it terribly efficient, but it should work with positive integers (well, at least if numbers in the inlist are not too high - it should work with numbers of at least up to about 20.000) in your inlist:
    Code:
    select col from
    (select rownum as col from all_objects
     minus
     select col from t
    )
    where col not in (1,2,3,6,7);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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