Re: parameterised SQL views

>Paul Singleton wrote in message <39E5A4ED.D8A735A5_at_keele.ac.uk>...
>>Joe Celko wrote:
>>
>>> Would you like to play with the idea of two or more
>>> copies of the same VIEW with different parmeters?
>>
>>I _work_ with this idea all the time, in Prolog: the semantics are
 clean,
>>declarative and a superset of the semantics which SQL realises.
>>
>>> The whole conceptual model falls apart.
>>
>>I don't think it's as serious as that.
>>
>>If I have a (naive) table of personnel records ("this person was in
 this
>>dept from this date until this date")
>>
>> Emp( Name, Dept, DateFrom, DateUntil)
>>
>>and a view
>>
>> EmpSnapshot( Name, Dept, When)
>>
>>as
>>
>> SELECT Name, Dept, When
>> FROM Emp
>> WHERE DateFrom <= When
>> AND When < DateUntil
>>
>>then I could use this view thus:
>>
>> SELECT T1.Name
>> FROM EmpSnapshot T1,
>> EmpSnapshot T2
>> WHERE T1.Name = T2.Name
>> AND T1.When = {12th Sep 1989}
>> AND T2.When = {25th Mar 1979}
>> AND T1.Dept <> T2.Dept
>>
>>to find names of folk in different depts on those dates.
>>
>>I don't think there's any problem with the semantics here, just that
>>EmpSnapshot is not a *materialisable* view, so SQL baulks (and leaves
>>me to the mercy of ad hoc vendor-specific "procedural" extensions :-)

Paul

Since you mention Prolog (presumably just the "Datalog with negation" subset), let me try to rephrase things the Prolog way. Your EmpSnapshot (actually a view) could be defined as Prolog rule as follows:

    empSnapshot(Name, Dept, When) :-

        emp(Name, Dept, DateFrom, DateUntil),
        DateFrom =< When,
        When < DateUntil.

Your above question in prolog would look like this:

    ?- empSnapshot(Name, Dept1, '12th Sep 1989'),

       empSnapshot(Name, Dept2, '25th Mar 1989'),
       Dept1 \= Dept2.

    ?- empSnapshot(Name, Dept1, When1),
       empSnapshot(Name, Dept2, When2),
       When1 = '12th Sep 1989',
       When2 = '25th Mar 1989',
       Dept1 \= Dept2.

Either of these two (equivalent) Prolog queries can be translated by an automatic translator into the SQL queries that you wrote. (About 10 years ago, we even implemented a system which did just that.)

The confusion might arise from the fact that a Prolog query can also be regarded as a procedure call, and that a constant c in such a call can be viewed as an "input parameter" to a procedure p:

    ?- p( ... , c , ... ).

This is of course equivalent to

    ?- p( ... , X , ... ), X = c. % X is a variable

which is translated into SQL as

    SELECT ...
    FROM p
    WHERE p.A = c

assuming that the attribute corresponding to the position of variable X is called A.

So passing a parameter to a Prolog procedure has to be specified using appropriate WHERE clauses in SQL. Received on Mon Oct 16 2000 - 22:45:27 CEST