I need to construct a view so that a certain field from the underlying table will not display any values BUT I can still enter selection criteria agasint that field in the Where clause of an SQL running against that view.

Your assistance will be greatly appreciated.

Thanks!

Sorry - forgot my version

I am using DB2 V8.1 on a UNIX AIX server

just a guess.. plz check it out

suppose view V(c1) is crerated from table T( c1, c2):
select * from v where c1 in (select c1 from T where c2 like < > );
but u have to take care rthat c1 and c2 both are having unique values.

Rahul

So you want to create a view that has READ permission on one of its columns, as the user must be able to use it in its WHERE clause and that same column must have NO READ permission because its contents may not be made visual. Hmmm, sounds like a real contradictio in terms. Can you explain a bit more about this requirement ? How will this view be used and its results displayed ?

Suppose the rule is: you may only show the managers who make less than USD 1,000,000 a year:
You could write
CREATE VIEW V (nr_emp, name)
FROM employee
WHERE salary < 1000000;

So in a sense you used the 'secret' column salary, but it doesn't show in the view, but this is not what you want.

You could write a stored procedure that accepts the value for the secret column and make it return a table without the secret column, but you would lose the felxibility of a true WHERE clause (>, >=, = , <, between, ...) you would have to write multiple SP's top handle them all or one (very) flexible one that can handle all those possibilities.

Please can you elaborate a bit more on this constraint and use of the view?