Re: Is there any way we can exclude all possible values ?

From: xiaoyan <xiaoyezi.xyz_at_163.com>
Date: Wed, 3 Jan 2007 09:44:30 +0800
Message-ID: <002201c72ed8$b7349a40$d810a8c0@buaad538c81ca1>

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_at_wellsfargo.com To: xiaoyezi.xyz_at_163.com Cc: oracle-l_at_freelists.org 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of xiaoyan   Sent: Friday, December 29, 2006 7:13 PM   To: Job Miller
  Cc: oracle-l_at_freelists.org
  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';

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!

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jan 02 2007 - 19:44:30 CST