FreeLists / oracle-l / Re: Is there any way we can exclude all possible values ?

["xiaoyan" <xiaoyezi.xyz@xxxxxxx>]

oh,a wonderful method,I have tried the second method,it does work very well. 
And that is what  I want.

Thank you very much!

Best Regards

  ----- Original Message ----- 
  From: Pratap.Singh@xxxxxxxxxxxxxx 
  To: xiaoyezi.xyz@xxxxxxx 
  Cc: oracle-l@xxxxxxxxxxxxx 
  Sent: Wednesday, January 03, 2007 12:58 AM
  Subject: RE: Is there any way we can exclude all possible values ?


  A sureshot method to work will be

  select 'x' from student 
  where
  course_score not in 
  (select course_score from student)

  Another easy way if course_score column is "Not NULL"
  select 'x' from student
  where course_score is NULL.

  Hope this help, although both solution maybe resource heavy.

  Thx,
  PB Singh



------------------------------------------------------------------------------
  From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of xiaoyan
  Sent: Friday, December 29, 2006 7:13 PM
  To: Job Miller
  Cc: oracle-l@xxxxxxxxxxxxx
  Subject: Re: Is there any way we can exclude all possible values ?


  hi,
     I am sorry for my poor english and thank you for your warmhearted reply.
     I mean I want to write a function which returns a predicate(like where 
clause), and the predicate is based on some column of a table,such as column 
'COURSE_SCORE' in table 'STUDENT', and the predicate can excluede all the 
possible values of the column to be assigned, For example, the value of 
'COURSE_SCORE' is perhaps 10,20,30,40.then I want the function returns 
'COURSE_SCORE not in (10,20,30,40)',perhaps the predicate is like COURSE_SCORE 
='a',here 'a' can be any character, because the value of the number column 
'COURSE_SCORE'  can not be   character,I just want the functon returns a 
predicate (where clause),when I execute query aginst table 'STUDENT' with this 
predicate,I will get no data row, in fact, here I want to use VPD to implement 
some need.
     for example:here is the table STUDENT:
     STUDENT_ID  STRUDENT_NAME COURSE_SCORE
      1           wxy           10
      2           feeling       20
      3           king          30
      4           merry         40
    if not any where clause with the query,I will get all the data rows through 
the following query:

    SQL> select * from student;
        STUDENT_ID  STRUDENT_NAME COURSE_SCORE 
         1           wxy           10
         2           feeling       20
         3           king          30
         4           merry         40

   if with a where clause like the following:
    SQL> select * from student where course_score no in (10,20,30,40) ;

   or
    SQL> select * from student where course_score='a';

  or

    SQL> select * from student where course_score='b';
  .....


   That is what I want to do, and based on some reason, the where clause must 
be based on one of the columns in the table.

   I do not know if I have made my intent clear...



  Best Regards and Happy New Year!