Re: masking column values

1.
The only way of column value masking is through Decode OR Case statements in 'Select clause'. Any filter criteria applied in where class applies on whole row and not on column basis. Also, its better to use CASE over Decode as Decode is another procedure call for each row selected and it has all overhead that any other procedue might have. Creating view using intersection of two select queries is also not advisable as it will incur two table scans(one for each select query ) and +sorting/merging cost of each select output.

You can create ur view as

create ore place view emp_clm
as
select name, id, dept,
CASE WHEN UPPER(name)=USER THEN sal ELSE NULL END sal from emp;

2.
You can use select quary inside select clause provided you inner quary should return single row.

  SELECT NAME,DEPT,JOB,
  CASE WHEN 5000 = (SELECT SAL FROM EMP_CLR B WHERE A.SAL = B.SAL) THEN SAL ELSE NULL END SAL
   FROM EMP_CLR A
/

NAME                                           DEPT JOB
        SAL
---------------------------------------- ----------
-------------------- ----------
 Jones                                           10 Clerk
       5000
 Smith                                           20  Smith
 Ford                                            10 Consultant

If (SELECT SAL FROM EMP_CLR B WHERE A.SAL = B.SAL) if this is returning more than 1 row then u will get error "single-row subquery returns more than one row"
So "where clause" should be such that for each row it returns one and only one row. Received on Fri Feb 11 2005 - 14:45:55 CST