Re: masking column values

From: <chopras_at_gmail.com>
Date: 11 Feb 2005 11:23:07 -0800
Message-ID: <1108149787.511845.21290@c13g2000cwb.googlegroups.com>

Well maybe I was not clear:
What I want accomplished is the following:

Say there are 3 rows in the database:

Name           Job                Dept               SAL
Jones           Clerk               10                5000
Smith           Analyst             20               10000
Ford            Consultant          10               20000

Say user Smith issues the query on the emp_clm (as define above) such as

select * from emp_clm,

I want Smith to only view his salary and nothing else. So I want the result of a query to return:

Name           Job                Dept               SAL
Jones          Clerk              10
Smith          Analyst             20                   10000
Ford           Consultant         10

Note that Jones' and Ford's Sal information is missing (the SAL column has been masked)

One way to do this is using the decode command in the view definition for emp_clm

create ore place view emp_clm
as
select name, id, dept,
DECODE(name, USER, sal, NULL) sal
from emp

However, the DECODE or CASE WHEN statements are written in the Select clause of queries. I want to accomplish the same thing but provide the column masking using the WHERE clause of a query. I do not think this is actually possible, but if anyone knows how, that would be great.

---

Additionally I wanted to know if the CASE WHEN clause in SQL is powerful enough to include queries. In general is it possible to do something like:

select name, id, dept
case when

   5000 = select sal from emp_clr
   then

      sal
   else
     null
end as sal
from emp_clr

In other words, can case statements include queries as part of the when clause.  

Thanks...

PS: This is not a homework :) Received on Fri Feb 11 2005 - 13:23:07 CST