Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: parameterised SQL views

Re: parameterised SQL views

From: Paul Singleton <>
Date: Fri, 27 Oct 2000 16:26:01 +0100
Message-ID: <>

Bob Badour wrote:

> Going back to the earlier example, what would happen if the DateFrom and
> DateUntil columns were derived from data in a different table, and you had a
> view like:
> Create View Emp as
> select e.*
> , as DateFrom
> , coalesce(,'99991231') as DateUntil
> from ( employee e join job_record h
> on e.empid = h.empid
> ) left outer join job_record t
> on h.empid = t.empid
> and h.job_id = t.job_id
> where h.record_type = 'Hire'
> and t.record_type = 'Term'
> ;
> Consider how much more complex the Prolog might be without the view (Y10K
> problem notwithstanding).

I guess my tentative conclusion (offered up here for criticism, confirmation or refutation) is:

E.g. I could express your 'Emp' view in Prolog as

  emp( EmpId, EmpName, ..., DateFrom, DateUntil) :-

      employee( EmpId, EmpName, ...),
      job_record( EmpId, JobId, 'Hire', DateFrom),
      (   job_record( EmpId, JobId, 'Term', DateUntil)
      ->  true
      ;   DateUntil = 9999/12/31

and unroll it into my generated SQL query, although a smart "compiler" should spot that it is a candidate for saving as a view...

I've skimmed through Richard Snodgrass' recent "Developing Time-Oriented Database Applications in SQL" book, where he shows that, with ingenuity and some inelegance, it is possible to express a usefully complete set of temporal queries in SQL, but there is no suitable language construct for modularising and reusing the common code patterns; views are too restrictive, and stored procedures are too non-portable.

I can see parallels between SQL and PostScript, which was introduced as a programming (?) language, but with poor support for shared libraries, modularity and other "software engineering" aspects; now it is mostly used as an invisible intermediate language, mechanically generated and then mostly thrown away...

The temporal database community like to invent higher-level supersets of SQL for expressing their queries, and for compiling into ugly, long-winded "trad" SQL equivalents; my preference for Prolog is due to my preference for writing applications in Prolog (in practice, I mostly use VB, but that's another, even sadder, story :-)

Paul Singleton Received on Fri Oct 27 2000 - 10:26:01 CDT

Original text of this message