Re: parameterised SQL views

Robert Marti wrote:

> ...
> So passing a parameter to a Prolog procedure has to be
> specified using appropriate WHERE clauses in SQL.

Yes indeed: I pass a parameter to an SQL view thus:

  SELECT Name
  FROM NameGender
  WHERE Gender = "female"

where the view might be

  CREATE VIEW NameGender( Name, Gender) AS

    SELECT Name, "male"
    FROM Men

  UNION     SELECT Name, "female"
    FROM Women

and my actual parameter causes the second subquery to be evaluated, but not (I hope) the first.

But I can't call

  SELECT Name
  FROM EmpEarnsAtLeast
  WHERE MinSalary = 20000

where

  CREATE VIEW EmpEarnsAtLeast( Name, MinSalary) AS     SELECT Emp.Name
    FROM Emp
    WHERE Emp.Salary >= MinSalary

because the view is not materialisable, although I could do this in Prolog, which would raise a run-time "mode" error if I tried the equivalent of

  SELECT *
  FROM EmpEarnsAtLeast

I have a couple of workarounds: the first I call the "join with finite domain" workaround: I create a table of all possible salaries, then

  CREATE VIEW EmpEarnsAtLeast( Name, MinSalary) AS     SELECT Emp.Name, AllSalaries.MinSalary     FROM Emp, AllSalaries
    WHERE Emp.Salary >= AllSalaries.MinSalary

Now I *can* use MinSalary as an "input parameter", as it is a field (the only one) of the (otherwise spurious) 'AllSalaries' table (if it's a primary key, this might even be not too inefficient, assuming I can spare the storage space).

My other workaround (following a suggestion by David Cressey in an email response) is (in general) to stash actual parameters in a new record in an extra table, then pass in a key (sequentially allocated?) to this record, e.g.

                              ParamKey:   Value1:
  INSERT INTO Params VALUES (     16052,    20000, ..)

  SELECT Name
  FROM EmpEarnsAtLeast
  WHERE ParamKey = 16052

where

  CREATE VIEW EmpEarnsAtLeast( Name, ParamKey) AS     SELECT Emp.Name, Params.ParamKey
    FROM Emp, Params
    WHERE Emp.Salary >= Params.Value1

Such keys could be passed down through many levels of nested views; in some cases I would pass two (or more) keys, e.g. when I need to call the same view with different parameters (e.g. when comparing two snapshots of a historical database).

If any RDBMS developer fancies supporting non-materialisable views, then I'm interested :-)

Paul Singleton
("it doesn't have to be FINITE to be RELATIONAL!") Received on Tue Oct 31 2000 - 18:14:53 CET