Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    USA
    Posts
    4

    Question Unanswered: Views - with a twist

    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!

  2. #2
    Join Date
    Mar 2004
    Location
    USA
    Posts
    4
    Sorry - forgot my version

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

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Keith Cunningha
    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.
    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?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •