Re: parameterised SQL views

Paul,

The thing you describe as:

EmpSnapshot( Name, Dept, When)

is not a relation nor is it anything like SQL' s attempt at one. 'When' has no distinct value.

A much more relational approach would define a domain to represent the date range or period with a Contains operator. Then you could have:

EmpSnapshot( Name, Dept, Period)

and query it with something along the lines:

 SELECT T1.Name
 FROM EmpSnapshot T1,

        EmpSnapshot T2

 WHERE  T1.Name = T2.Name
 AND    T1.Period Contains {12th Sep 1989}
 AND    T2.Period Contains {25th Mar 1979}
 AND    T1.Dept <> T2.Dept

Unfortunately, SQL doesn't really have domains and operators.

Of course, the original original Emp table already provides everything you need to construct the query without the new domain. There is really no need for a view here:

SELECT T1.Name
FROM Emp T1, Emp T2
WHERE T1.Name = T2.Name
AND {12th Sep 1989} BETWEEN T1.DateFrom AND T1.DateUntil+1 AND {25th Mar 1979} BETWEEN T2.DateFrom AND T2.DateUntil+1 AND T1.Dept <> T2.Dept

Why do you need to compile it every time? Use bind variables:

SELECT T1.Name
FROM Emp T1, Emp T2
WHERE T1.Name = T2.Name

AND :1 BETWEEN T1.DateFrom AND T1.DateUntil+1
AND :2 BETWEEN T2.DateFrom AND T2.DateUntil+1
AND T1.Dept <> T2.Dept

Compile the above once and execute it many times with different values bound to the query.

I would stay away from stored procedures unless they are absolutely necessary, and they almost never are.

Regards,
Bob

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 :-)
>
>> Stored Procedures have parameters. Each call to a procedure is not
>> done inside a query where you would have those scoping problems.
>
>Is there a consensus on how stored procedures handle parameters,
>and can I use them to achieve the sort of thing illustrated above?
>
>Ones I've glanced at were more like #define textual substitutions
>than genuine runtime parameters, and I'd have trouble passing different
>actual parameter values to two instances of the same procedure...
>
>I really need to do this: I can generate an equivalent SQL query
>dynamically, and pass it to the RDBMS to be compiled each time, but
>can I do better than this?
>--
> _ _ Paul Singleton PhD CEng MBCS tel: 01782 618379 (UK)
>|_)(_` SmartArts Computing Consultancy tel: +44 1782 618379
>| ._) 36 Larchwood, Keele, email: p.singleton_at_keele.ac.uk
> Newcastle, Staffs ST5 5BB UK road: UK M6 J15 -> Keele Uni.
>
Received on Sat Oct 14 2000 - 13:20:52 CEST