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
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:
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.