Write Less with More – Part 7 (Lateral Inline Views)

This post is part 7 of the Write Less with More series of blog posts, focusing on new features of Oracle 12c that allow us developers to write less than in previous versions in order to achieve the same functionality. Each part is dedicated for one new feature that solves some common development task. For more details, including the setup of the examples used throughout the series and the list of development tasks that drive the series, please see part 1.

All the Series Parts

Published

Part 1 – write less configuration with SQL*Loader Express Mode
Part 2 – write less application code with Identity Columns
Part 3 – write less application code with In-Database Archiving
Part 4 – write less application code with Temporal Validity
Part 5 – write less code in SQL with Row Limiting
Part 6 – write less “inappropriately located” code with SELECT FROM Package-Level Collection Types
Part 7 – write less code in SQL with Lateral Inline Views
Part 8 – write less “inappropriately located” code with PL/SQL in the WITH Clause

Task #7

todo7

At this point, after using In-Database Archiving in part 3 and Temporal Validity in part 4, this is how the PROJECTS and PROJECT_ASSIGNMENTS tables look like:
ERD part 7
Assuming today is November 13th 2015, and assuming this is the content of the PROJECTS table:

> select project_id,
         project_name,
         last_days_to_show_in_reports
  from projects;

PROJECT_ID PROJECT_NA LAST_DAYS_TO_SHOW_IN_REPORTS
---------- ---------- ----------------------------
         1 Project A                             2
         2 Project B                             3
         3 Project C                             4

then the output of the query that we need to write should looks like this:

> select...

PROJECT_NAME DAY        NUM_OF_ASSIGNMENTS
------------ ---------- ------------------
Project A    13/11/2015                  3
Project A    12/11/2015                  3

Project B    13/11/2015                  2
Project B    12/11/2015                  4
Project B    11/11/2015                  5

Project C    13/11/2015                  1
Project C    12/11/2015                  0
Project C    11/11/2015                  0
Project C    10/11/2015                  1

Since LAST_DAYS_TO_SHOW_IN_REPORTS of Project A is 2, the query should return 2 rows for this project – for today and for yesterday.
LAST_DAYS_TO_SHOW_IN_REPORTS of Project B is 3, so the query should return 3 rows for this project – for today, for yestrday and for 2 days ago.
Similarly, the query should return 4 rows for project C, since LAST_DAYS_TO_SHOW_IN_REPORTS of this project is 4.

If you want to learn about many more new features of Oracle 12c, you may be interested in one of my in-house training sessions, particularly the Oracle 12c New Features for Developers full-day seminar.

NUM_OF_ASSIGNMENTS in each row is calculated simply as the number of rows in PROJECT_ASSIGNMENTS for the relevant project and day. Note that even if there are no assignments for specific project and day, the query should still return a row (with NUM_OF_ASSIGNMENTS=0), as you can see in the example in Project C at 11/11/2015 and 12/11/2015.

Generating the Last N Days

Let’s first see how to generate the last N days; for example, the last 4 days:

> select trunc(sysdate) + 1 - level day
  from dual
  connect by level <= 4;

DAY
----------
13/11/2015
12/11/2015
11/11/2015
10/11/2015

The Solution for a Fixed Number of Days

So if we were required to get the last 4 days (exactly 4) for each project, we would simply do a cartesian product between PROJECTS and an inline view with the previous query:

> break on project_name skip 1 dup

> select p.project_name,
         d.day
  from   projects p,
         (select trunc(sysdate) + 1 - level day
          from   dual
          connect by level <= 4) d
  order  by project_name,
            day desc;

PROJECT_NAME DAY
------------ ----------
Project A    13/11/2015
Project A    12/11/2015
Project A    11/11/2015
Project A    10/11/2015

Project B    13/11/2015
Project B    12/11/2015
Project B    11/11/2015
Project B    10/11/2015

Project C    13/11/2015
Project C    12/11/2015
Project C    11/11/2015
Project C    10/11/2015

and then we would add NUM_OF_ASSIGNMENTS.

A Pre-12c Solution

But the requirement here is to have a different number of days per project.
One option is to find the maximum possible number of days, generate this number of days for each project, and then filter out the excess records:

> select p.project_name,
         d.day
  from   projects p,
         (select level row_num,
                 trunc(sysdate) + 1 - level day
          from   dual
          connect by level <=
            (select max(last_days_to_show_in_reports) from projects)
         ) d
  where d.row_num <= p.last_days_to_show_in_reports
  order by project_name,
           day desc;

PROJECT_NAME DAY
------------ ----------
Project A    13/11/2015
Project A    12/11/2015

Project B    13/11/2015
Project B    12/11/2015
Project B    11/11/2015

Project C    13/11/2015
Project C    12/11/2015
Project C    11/11/2015
Project C    10/11/2015

But I don’t really like this solution. First, because it requires accessing PROJECTS twice. Second, because of the excess processing.
Another option is using Collection Unnesting:

> select p.project_name,
         d.column_value day
  from   projects p,
         table(
           cast(
	     multiset (
	       select trunc(sysdate) + 1 - level
               from   dual
               connect by level <= p.last_days_to_show_in_reports
	     )
           as sys.odcidatelist)
          ) d
  order  by project_name,
            day desc;

PROJECT_NAME DAY
------------ ----------
Project A    13/11/2015
Project A    12/11/2015

Project B    13/11/2015
Project B    12/11/2015
Project B    11/11/2015

Project C    13/11/2015
Project C    12/11/2015
Project C    11/11/2015
Project C    10/11/2015

This option addresses both issues that I dislike in the first option, but it is quite cumbersome, and if you don’t have an object type that matches the result of the MULTISET operator (I used sys.odcidatelist in the example) you need to create one.

A 12c Solution

In Oracle 12c an inline view can be defined as LATERAL. This allows the inline view to refer to other tables that appear to its left in the FROM clause. Which is exactly what we need here:

> select p.project_name,
         d.day
  from   projects p,
         LATERAL (select trunc(sysdate) + 1 - level day
                  from   dual
                  connect by level <= p.last_days_to_show_in_reports)  d
  order by project_name,
           day;

PROJECT_NAME DAY
------------ ----------
Project A    13/11/2015
Project A    12/11/2015

Project B    13/11/2015
Project B    12/11/2015
Project B    11/11/2015

Project C    13/11/2015
Project C    12/11/2015
Project C    11/11/2015
Project C    10/11/2015

Note: lateral inline views have been used internally by the optimizer in 11g, and could even be used by the end user in some undocumented and unsupported way.

Just to complete the solution, we need to add NUM_OF_ASSIGNMENTS to the result. For example, using outer join:

> select pd.project_name,
         pd.day,
         count(a.project_id) num_of_assignments
  from   (select p.project_id,
                 p.project_name,
                 d.day
          from   projects p,
                 lateral (select trunc(sysdate) + 1 - level day
                          from   dual
                          connect by level <= p.last_days_to_show_in_reports)  d) pd,
         project_assignments a
  where  nvl(a.assignment_period_start(+), date '0001-01-01') <= pd.day
  and    pd.day < nvl(a.assignment_period_end(+), date '9999-12-31')
  and    a.project_id(+) = pd.project_id
  group  by pd.project_name,
            pd.day
  order  by project_name,
            day;

PROJECT_NAME DAY        NUM_OF_ASSIGNMENTS
------------ ---------- ------------------
Project A    13/11/2015                  3
Project A    12/11/2015                  3

Project B    13/11/2015                  2
Project B    12/11/2015                  4
Project B    11/11/2015                  5

Project C    13/11/2015                  1
Project C    12/11/2015                  0
Project C    11/11/2015                  0
Project C    10/11/2015                  1

Another Example

My first post about an Oracle 12c feature was in 2013, when 12c has just been released. It was about Lateral Inline Views, and you can see there another example for using this feature.

Conclusion

We saw in this part of the Write Less with More series that Lateral Inline Views allow us to write less code in SQL.
The next post will be about another new Oracle 12c feature – PL/SQL in the WITH Clause.

Leave a Reply

Your email address will not be published.