Re: ISSUE-19 (Reuse SQL query as sub-query): Reuse a SQL query as a sub-query [R2RML]

>
> Actually, an example would be good, because I do not understand what you
> mean by 'sub-logical-table'
>

The R2RML spec allows logical tables to be defined as SQL queries. So a
"sub-logical-table" is really just a sub-query. What I am getting at is that
R2RML allows the creation of an abstraction: a SQL query as a logical table,
but it does not allow these logical tables to be composed together like
regular tables. Regular tables can be composed by writing a SQL query that
joins them together.

So for example, if a database has an EMPLOYEE and a DEPT table then I can
compose them in a logical table like this:

SELECT
    EMPLOYEE.NAME, DEPT.DEPT_NAME
FROM
    EMPLOYEE JOIN DEPT ON EMPLOYEE.DEPT_NUMBER = DEPT.DEPT_NUMBER

Let's call this logical table "EMP_DEPT".

I would like to create another logical table by joining EMP_DEPT with
another real table named BUDGET:

SELECT
    EMP_DEPT.NAME, BUDGET.AMOUNT
FROM
    EMP_DEPT JOIN BUDGET on EMP_DEPT.DEPT_NAME = BUDGET.DEPT_NAME

Allowing the creation of abstractions that can be composed together
unleashes great power in the mapping language. Currently logical tables in
some ways emulate real tables, but they cannot be combined as shown above.
By allowing them to be combined we are making them more like real tables.
The immediate benefit is that this approach allows SQL queries to be reused
as sub-queries in multiple, different contexts without copying-and-pasting
them.

As a practical matter, I often see database models where several tables must
be joined together to produce a desired high-level view of the data for
consumption by users. Allowing these joins to be expressed once and reused
keeps the mappings trim and avoids duplication.

-David