Reports: matrix report with percentage totals

...previous

On my previous note on reports I was asked for a query, that would return percentage ratios for vertical and horizontal subtotals (at least as I have understood ).

So in this case our report draft could be represented in the following way:

| Departments names block | Total
------------+----------+---------+----------+---------------
| | | |
Cities -+- Detailed data -+- Ratio (%) of totals
Names | for each | for every city
block -+- city & department -+- to the very total
| | | |
------------+----------+---------+----------+----------------
Total | Ratio (%) of totals for every | Overall total
| department to the very total | (100%)

As I have not very much spare time I decided to put here only both solutions (non-model and model) without any explanation, and if you have any doubts/questions - you can put it as a comment, then I will elaborate on this.

Input data you can still find here.

Non-Model solution:

SQL> select nvl(city, 'TOTAL') as city_,
2 nvl2(city,to_char(sum(decode(dep, 'DEP1', sal, 0))),round(sum(decode(dep, 'DEP1', sal, 0))*100/sum(decode(flag,3,sal)),1)||'%') dep1,
3 nvl2(city,to_char(sum(decode(dep, 'DEP2', sal, 0))),round(sum(decode(dep, 'DEP2', sal, 0))*100/sum(decode(flag,3,sal)),1)||'%') dep2,
4 nvl2(city,to_char(sum(decode(dep, 'DEP3', sal, 0))),round(sum(decode(dep, 'DEP3', sal, 0))*100/sum(decode(flag,3,sal)),1)||'%') dep3,
5 nvl2(city,to_char(sum(decode(dep, 'DEP4', sal, 0))),round(sum(decode(dep, 'DEP4', sal, 0))*100/sum(decode(flag,3,sal)),1)||'%') dep4,
6 round(2*100*ratio_to_report(sum(decode(flag, 1, sal, 3, sal, 0))) over (),1)||'%' total
7 from (
8 select city, dep, sum(sales) sal, grouping_id(city, dep) flag
9 from t
10 where trunc(year, 'y') = date '2007-01-01'
11 group by cube(city, dep))
12 group by city
13 order by nvl2(city, 0, 1), total desc
14 /

CITY_ DEP1 DEP2 DEP3 DEP4 TOTAL
------ ----------- ----------- ----------- ---------- ----------
Moscow 762 657 1020 487 87.9%
Omsk 34 213 156 0 12.1%
TOTAL 23.9% 26.1% 35.3% 14.6% 100%

SQL>

Model solution:

SQL> select city,dep1,dep2,dep3,dep4,total from t
2 where trunc(year,'y') = date '2007-01-01'
3 model
4 return updated rows
5 dimension by (city, dep)
6 measures(to_char(sales) dep1, to_char(sales) dep2, to_char(sales) dep3, to_char(sales) dep4, to_char(sales) total)
7 rules
8 upsert all
9 (dep1[any,null]=nvl(dep1[CV(),'DEP1'],0),
10 dep2[any,null]=nvl(dep1[CV(),'DEP2'],0),
11 dep3[any,null]=nvl(dep1[CV(),'DEP3'],0),
12 dep4[any,null]=nvl(dep1[CV(),'DEP4'],0),
13 total['TOTAL',null]=sum(dep1)[any,null]+sum(dep2)[any,null]+sum(dep3)[any,null]+sum(dep4)[any,null],
14 dep1['TOTAL',null]=round(sum(dep1)[any,null]*100/total['TOTAL',null],1)||'%',
15 dep2['TOTAL',null]=round(sum(dep2)[any,null]*100/total['TOTAL',null],1)||'%',
16 dep3['TOTAL',null]=round(sum(dep3)[any,null]*100/total['TOTAL',null],1)||'%',
17 dep4['TOTAL',null]=round(sum(dep4)[any,null]*100/total['TOTAL',null],1)||'%',
18 total[city<>'TOTAL',null]=round((dep1[CV(),CV()]+dep2[CV(),CV()]+dep3[CV(),CV()]+dep4[CV(),CV()])*100/total['TOTAL',null],1)||'%',
19 total['TOTAL',null]='100%'
20 )
21 /

CITY DEP1 DEP2 DEP3 DEP4 TOTAL
------ ----------- ----------- ---------- ---------- ----------
Moscow 762 657 1020 487 87.9%
Omsk 34 213 156 0 12.1%
TOTAL 23.9% 26.1% 35.3% 14.6% 100%

SQL>

PS
Added after the comment.

"A good problem description worth half a solution" :)
So again I'm not sure whether this is needed or not, but if you want to calculate only percentages in matrix report even for detailed data you can use the following:

So the scheme of our report would be:

| Departments names block | Total
------------+----------+---------+----------+---------------
| | | |
Cities -+- Ratio (%) of detailed -+- Ratio (%) of totals
Names | data for each city & | for every city
block -+- department to total -+- to the very total
| | | |
------------+----------+---------+----------+----------------
Total | Ratio (%) of totals for every | Overall total
| department to the very total | (100%)

Non-model solution:

SQL> select nvl(city, 'TOTAL') as city_,
2 round(sum(decode(dep, 'DEP1', sal, 0)), 2) || '%' dep1,
3 round(sum(decode(dep, 'DEP2', sal, 0)), 2) || '%' dep2,
4 round(sum(decode(dep, 'DEP3', sal, 0)), 2) || '%' dep3,
5 round(sum(decode(dep, 'DEP4', sal, 0)), 2) || '%' dep4,
6 round(sum(decode(flag, 1, sal, 3, sal, 0)), 2) || '%' total
7 from (select city, dep, sum(rtr) sal, grouping_id(city, dep) flag
8 from (select t.*, 100 * ratio_to_report(sales) over() rtr
9 from t
10 where trunc(year, 'y') = date '2007-01-01')
11 group by cube(city, dep))
12 group by city
13 order by nvl2(city, 0, 1), total desc
14 /

CITY_ DEP1 DEP2 DEP3 DEP4 TOTAL
------ ------------- ------------ ------------- ------------- -----------
Moscow 22.89% 19.74% 30.64% 14.63% 87.89%
Omsk 1.02% 6.4% 4.69% 0% 12.11%
TOTAL 23.91% 26.13% 35.33% 14.63% 100%

SQL>

Model solution:

SQL> select city,
2 round(dep1,2)||'%' dep1,
3 round(dep2,2)||'%' de2,
4 round(dep3,2)||'%' de3,
5 round(dep4,2)||'%' de3,
6 round(total,2)||'%' total from (
7 select t.*, 100*ratio_to_report(sales) over () rtr from t
8 where trunc(year,'y') = date '2007-01-01')
9 model
10 return updated rows
11 dimension by (city, dep)
12 measures(rtr dep1, rtr dep2, rtr dep3, rtr dep4, rtr total)
13 rules
14 upsert all
15 (dep1[any,null]=nvl(dep1[CV(),'DEP1'],0),
16 dep2[any,null]=nvl(dep1[CV(),'DEP2'],0),
17 dep3[any,null]=nvl(dep1[CV(),'DEP3'],0),
18 dep4[any,null]=nvl(dep1[CV(),'DEP4'],0),
19 dep1['TOTAL',null]=sum(dep1)[any,null],
20 dep2['TOTAL',null]=sum(dep2)[any,null],
21 dep3['TOTAL',null]=sum(dep3)[any,null],
22 dep4['TOTAL',null]=sum(dep4)[any,null],
23 total[any,null]=dep1[CV(),CV()]+dep2[CV(),CV()]+dep3[CV(),CV()]+dep4[CV(),CV()]
24 )
25 /

CITY DEP1 DE2 DE3 DE3 TOTAL
------ ----------- ------------ ----------- ------------ -----------
Moscow 22.89% 19.74% 30.64% 14.63% 87.89%
Omsk 1.02% 6.4% 4.69% 0% 12.11%
TOTAL 23.91% 26.13% 35.33% 14.63% 100%

SQL>

Puzzle

On the russian SQL forum there was a question for friday brain warm-up. The main idea is that the OP knows the answer beforehands, but he wants forum members to put some efforts on it to come up with easier solution.
So here is the problem. I've taken the description from that site.
You have a list of numbers:

1
11
21
1211
111221
312211
13112221
1113213211
...

You need to understand the logic of building such a sequence and then post a SQL solution for that.

Ok. If you want to try your skills - now it is the best time to stop and proceed by yourown. For solution look forward.

Solution: It's actually quite simple :) After starting the sequence with 1, each term in the sequence consists of groups of two numbers based on the previous term - the first being the quantity and the second specifying which digit.

Example: the first term is 1, which has "one 1" in it, therefore 11.
11 has "two 1's" in it, therefore 21.
21 has "one 2 and one 1" in it and therefore 1211.

Now let's proceed with the second part - SQL solution.
Although a query with similar idea was posted before I did, mine was:

SQL> select s from (select * from dual
2 model
3 dimension by (0 d)
4 measures(cast('1' as varchar2(1000)) s, cast(null as varchar2(1000)) s_new, 10 n, 1 flag)
5 rules iterate (10000000) until(flag[iteration_number+1]=n[0])
6 (s_new[iteration_number+1]=decode(flag[CV()-1],0,s_new[CV()-1],null)||
7 length(regexp_substr(s[CV()-1],'^(.)\1*'))||substr(s[CV()-1],1,1),
8 s[iteration_number+1]= regexp_replace(s[CV()-1],'^(.)\1*'),
9 flag[iteration_number+1]=nvl2(s[CV()],0,max(flag)[d 10 s[iteration_number+1]=nvl(s[CV()], s_new[CV()])
11 ))
12 where flag>0
13 /

S
--------------------------------------------------------------------------------
1
11
21
1211
111221
312211
13112221
1113213211
31131211131221
13211311123113112211

10 rows selected

SQL>

Reports: matrix report

...previous

After a while I decided to write one more note on reports. Now it would be about matrix reports.

The input data you can take here.
As you can find out it is information on sales for two years (2006, 2007) of four departments (DEP1, DEP2, DEP3, DEP4) in two cities (Omsk, Moscow).
Imagine we need to get sales only for the year 2007, but present it as a matrix report.
Horizontally information on departments should be placed, vertically it should be information on cities. In the last row and column of a table we need to show totals on departments and cities consequently. At the bottom in the most right column we need to place overall total sales.
So the general draft of our report would look like:

| Departments names block | Total
------------+----------+---------+---------+-------------
| | | |
Cities -+- Detailed data -+- Totals
Names | for each | for
block -+- city & department -+- every city
| | | |
------------+----------+---------+---------+-------------
Total | Totals for every department | Overall total

Non-Model solution:
To build such a report we need to get subtotals both on departments and cities. We also need to get the overall total of all sales for the year 2007. For such requirments we need to use CUBE Extension to GROUP BY.
So the whole query would be:

SQL> select nvl(city, 'TOTAL') as city_,
2 sum(decode(dep, 'DEP1', sal, 0)) dep1,
3 sum(decode(dep, 'DEP2', sal, 0)) dep2,
4 sum(decode(dep, 'DEP3', sal, 0)) dep3,
5 sum(decode(dep, 'DEP4', sal, 0)) dep4,
6 sum(decode(flag, 1, sal, 3, sal, 0)) total
7 from (select city, dep, sum(sales) sal, grouping_id(city, dep) flag
8 from t
9 where trunc(year, 'y') = date '2007-01-01'
10 group by cube(city, dep))
11 group by city
12 order by nvl2(city, 0, 1), total desc
13 /

CITY_ DEP1 DEP2 DEP3 DEP4 TOTAL
------ ---------- ---------- ---------- ---------- ----------
Moscow 762 657 1020 487 2926
Omsk 34 213 156 0 403
TOTAL 796 870 1176 487 3329

SQL>

This is the report we were looking for. So we can easily see that Dep3 sales in Omsk in the year 2007 were 156, while total sales of Dep3 were 1176, and total sales in Omsk were 403. Overall total sales were 3329.

Model solution:
Using model clause we need to have a tricky RETURN UPDATED ROWS clause. So that we would not group data in the outer query.
Also we will use UPSERT ALL command for all rules. What is this and how it can be used you can read in one of my previous notes. So the query would be:

SQL> select city,dep1,dep2,dep3,dep4,total from t
2 where trunc(year,'y') = date '2007-01-01'
3 model
4 return updated rows
5 dimension by (city, dep)
6 measures(sales dep1, sales dep2, sales dep3, sales dep4, sales total)
7 rules
8 upsert all
9 (dep1[any,null]=nvl(dep1[CV(),'DEP1'],0),
10 dep2[any,null]=nvl(dep1[CV(),'DEP2'],0),
11 dep3[any,null]=nvl(dep1[CV(),'DEP3'],0),
12 dep4[any,null]=nvl(dep1[CV(),'DEP4'],0),
13 dep1['TOTAL',null]=sum(dep1)[any,null],
14 dep2['TOTAL',null]=sum(dep2)[any,null],
15 dep3['TOTAL',null]=sum(dep3)[any,null],
16 dep4['TOTAL',null]=sum(dep4)[any,null],
17 total[any,null]=dep1[CV(),CV()]+dep2[CV(),CV()]+dep3[CV(),CV()]+dep4[CV(),CV()]
18 )
19 /

CITY DEP1 DEP2 DEP3 DEP4 TOTAL
------ ---------- ---------- ---------- ---------- ----------
Moscow 762 657 1020 487 2926
Omsk 34 213 156 0 403
TOTAL 796 870 1176 487 3329

SQL>

Notice that we have put as many measures for departments as we have in our data. So we are doing a column to row transformation, but as it was in the non-model solution it is static transformation. So we need to know how many columns we need to get beforehand.

As you know the model clause is working by executing a bunch of rules (one by one in a sequential order in our case).

We are placing the following groups of rules:
First we create new rows with department equal NULL, where we place sales of each department in each city.
Then we place rules for creating a TOTAL row, where we find the total sales for each department.
Finaly we write a rule (column TOTAL) - to calculate totals for every city and to find the overall total also.
So in the end we get the same results as with non-model solution.

to be continued...

Combinatorial problem

The resource is from Russian Forum.

Input:
We've got an alphabet consisting of N unique symbols.
E.g. alphabet='AB'.

Problem:
We need to find all possible variations with length M (so there would be power(N,M) number of combinations).
For our query let it be 4 as in the original source.

Solution:
ALthough there were several other solutions, e.g. using hierarchical queries, I post here my solution with model clause.

SQL> with t as (select 'AB' str from dual),
2 t1 as (select str, level-1 lvl from t connect by level<=power(length(str),4))
3 --
4 select lpad(num,4,substr(str,1,1)) path from t1
5 model
6 partition by (lvl part)
7 dimension by (0 dim)
8 measures (lvl, cast(null as varchar2(4)) num, str)
9 rules iterate (999) until (lvl[0] = 0)
10 (num[0] = substr(str[0],mod(lvl[0],length(str[0]))+1,1)||num[0],
11 lvl[0] = trunc(lvl[0]/length(str[0]))
12 )
13 order by part
14 /

PATH
----
AAAA
AAAB
AABA
AABB
ABAA
ABAB
ABBA
ABBB
BAAA
BAAB
BABA
BABB
BBAA
BBAB
BBBA
BBBB

16 rows selected

SQL>

As Chen Shapira asked me for some comments on the logic.
I can elucidate a little bit.
The first thing we need to understand that when we input an alphabet of N unique symbols and want to create all unique combinations of such symbols with length M - finally we get power(N,M) combinations.
It is kind of combinatorial stuff which is learnt at school, so the description you can find in Wiki Permutations with repetitions.

So the first what we do in our query - is generating the needed number of values we will have in the final result ('with clause' query connect by level<=...).
To distinguish these values we assign ordinal numbers starting from 0 up to the max number of value. Why we start from zero you understand later.

Now what we are going to do with these numbers? We are going to transform them from denary numeral system to the numeral system of the needed base which is length(str) in our case.

So probably if my english is not so good to understand I'll explain it with examples.
So here we're transforming to the binary system:

SQL> with t as (select '01' str from dual),
2 t1 as (select str, level-1 lvl from t connect by level<=power(length(str),4))
3 --
4 select initial_value, num, lpad(num,4,substr(str,1,1)) path from t1
5 model
6 partition by (lvl part)
7 dimension by (0 dim)
8 measures (lvl initial_value, lvl, cast(null as varchar2(4)) num, str)
9 rules iterate (999) until (lvl[0] = 0)
10 (num[0] = substr(str[0],mod(lvl[0],length(str[0]))+1,1)||num[0],
11 lvl[0] = trunc(lvl[0]/length(str[0]))
12 )
13 order by part
14 /

INITIAL_VALUE NUM PATH
------------- ---- ----
0 0 0000
1 1 0001
2 10 0010
3 11 0011
4 100 0100
5 101 0101
6 110 0110
7 111 0111
8 1000 1000
9 1001 1001
10 1010 1010
11 1011 1011
12 1100 1100
13 1101 1101
14 1110 1110
15 1111 1111

16 rows selected

SQL>

The next example is transformation to ternary numeral system:

SQL> with t as (select '012' str from dual),
2 t1 as (select str, level-1 lvl from t connect by level<=power(length(str),4))
3 --
4 select initial_value, num, lpad(num,4,substr(str,1,1)) path from t1
5 model
6 partition by (lvl part)
7 dimension by (0 dim)
8 measures (lvl initial_value, lvl, cast(null as varchar2(4)) num, str)
9 rules iterate (999) until (lvl[0] = 0)
10 (num[0] = substr(str[0],mod(lvl[0],length(str[0]))+1,1)||num[0],
11 lvl[0] = trunc(lvl[0]/length(str[0]))
12 )
13 order by part
14 /

INITIAL_VALUE NUM PATH
------------- ---- ----
0 0 0000
1 1 0001
2 2 0002
3 10 0010
4 11 0011
5 12 0012
6 20 0020
7 21 0021
8 22 0022
9 100 0100
10 101 0101
11 102 0102
12 110 0110
13 111 0111
14 112 0112
15 120 0120
16 121 0121
17 122 0122
18 200 0200
19 201 0201
20 202 0202
21 210 0210
22 211 0211
23 212 0212
24 220 0220
25 221 0221
26 222 0222
27 1000 1000
28 1001 1001
29 1002 1002
30 1010 1010
31 1011 1011
32 1012 1012
33 1020 1020
34 1021 1021
35 1022 1022
36 1100 1100
37 1101 1101
38 1102 1102
39 1110 1110
40 1111 1111
41 1112 1112
42 1120 1120
43 1121 1121
44 1122 1122
45 1200 1200
46 1201 1201
47 1202 1202
48 1210 1210
49 1211 1211
50 1212 1212
51 1220 1220
52 1221 1221
53 1222 1222
54 2000 2000
55 2001 2001
56 2002 2002
57 2010 2010
58 2011 2011
59 2012 2012
60 2020 2020
61 2021 2021
62 2022 2022
63 2100 2100
64 2101 2101
65 2102 2102
66 2110 2110
67 2111 2111
68 2112 2112
69 2120 2120
70 2121 2121
71 2122 2122
72 2200 2200
73 2201 2201
74 2202 2202
75 2210 2210
76 2211 2211
77 2212 2212
78 2220 2220
79 2221 2221
80 2222 2222

81 rows selected

SQL>

So at every iteration in our model clause we cut out the value at the appropriate position in our number. It is general algorithm of Radix Change.
As we generate the amount of numbers that would be unique in the corresponding numeral system we get unique values of the needed length (M) by padding the first letter/digit from the input string.
And instead of '01', '012' etc we can use any other string consisting of unique symbols and we get the needed result.

By the way, if we input '0123456789' string we get transformation to the denary system. So the output would be the same to the inputed numbers:

SQL> with t as (select '0123456789' str from dual),
2 t1 as (select str, level-1 lvl from t connect by level<=20/*power(length(str),4)*/)
3 --
4 select initial_value, num, lpad(num,4,substr(str,1,1)) path from t1
5 model
6 partition by (lvl part)
7 dimension by (0 dim)
8 measures (lvl initial_value, lvl, cast(null as varchar2(4)) num, str)
9 rules iterate (999) until (lvl[0] = 0)
10 (num[0] = substr(str[0],mod(lvl[0],length(str[0]))+1,1)||num[0],
11 lvl[0] = trunc(lvl[0]/length(str[0]))
12 )
13 order by part
14 /

INITIAL_VALUE NUM PATH
------------- ---- ----
0 0 0000
1 1 0001
2 2 0002
3 3 0003
4 4 0004
5 5 0005
6 6 0006
7 7 0007
8 8 0008
9 9 0009
10 10 0010
11 11 0011
12 12 0012
13 13 0013
14 14 0014
15 15 0015
16 16 0016
17 17 0017
18 18 0018
19 19 0019

20 rows selected

SQL>

Hope, now it is more clear. If still not - don't hesitate to ask.

Grouping overlapping number intervals, date periods etc...

Today I spent some time on a quite simple query. I'm speaking about this thread on OTN.

It is a simple task - to get overlapping intervals grouped. Sometimes this problem is formulated as to find a continious date period out of several separate periods.
Simple solution here would be to use analytic functions an so called start_of_group type of query:

SQL> with mytable as (select 1 id, 1 begin_data, 10 end_data from dual union all
2 select 1 id, 5 begin_data, 7 end_data from dual union all
3 select 1 id, 4 begin_data, 8 end_data from dual union all
4 select 1 id, 11 begin_data, 15 end_data from dual union all
5 select 1 id, 13 begin_data, 18 end_data from dual union all
6 select 2 id, 1 begin_data, 18 end_data from dual union all
7 select 2 id, 13 begin_data, 23 end_data from dual union all
8 select 2 id, 31 begin_data, 34 end_data from dual)
9 select id, min(begin_data) beg_d, max(end_data) end_d
10 from (select t1.*,
11 sum(start_of_group) over(partition by id order by begin_data, end_data) gr
12 from (select t.*,
13 case
14 when begin_data >
15 nvl(max(end_data)
16 over(partition by id order by begin_data,end_data
17 rows between unbounded preceding and 1 preceding),
18 begin_data-1)
19 then 1
20 else 0
21 end start_of_group
22 from mytable t) t1)
23 group by id, gr
24 order by 1, 2
25 /

ID BEG_D END_D
---------- ---------- ----------
1 1 10
1 11 18
2 1 23
2 31 34

SQL>

BTW if the table contains duplicate rows - I'd better add some unique key (e.g. add the most inner inline view with row_number() over() in there), and then use this row_number value to get rows sorted in the same order at all levels of query.

I'm speaking about the following case:

ID BEGIN_DATA END_DATA
---------- ---------- ----------
1 1 10
1 2 7
1 11 15
1 11 15
1 14 17

Although, I couldn't simulate such a situation, but it is not clear for me, if there are two separate (same) sortings in the query, would oracle sort rows equally, when there are duplicate rows.

I mean can't it happen, that while getting the value of start_of_group the Oracle gives a value of 1 to one row of the duplicate rows, and while summing this start_of_group exchange the places of these rows:

ID BEGIN_DATA END_DATA START_OF_GROUP
---------- ---------- ---------- --------------
1 1 10 1
1 2 7 0
1 11 15 0 <--+
|
1 11 15 1 <--+
1 14 17 0

In that case, the overall result would be wrong.
If you know how Oracle will behave in such a case, please, leave a comment.

PS
A small test case shows that you shouldn't rely on it:

SQL> with test as (select 1 id, 1 val from dual union all
2 select 1 id, 10 val from dual union all
3 select 1 id, 5 val from dual union all
4 select 1 id, 7 val from dual)
5 --
6 select t2.*, row_number() over(order by id) rn3
7 from (select t1.*, row_number() over(order by id, val) rn2
8 from (select test.*, row_number() over(order by id) rn1 from test) t1
9 order by val) t2
10 /

ID VAL RN1 RN2 RN3
---------- ---------- ---------- ---------- ----------
1 1 1 1 1
1 10 4 4 2
1 7 2 3 3
1 5 3 2 4

SQL>

row_number() over(order by id) is absoultely the same in the most inner query and the most outer. But produce different results, because another sortings happened between these two layers. So even if there are no sortings between them happen, I'd shurely won't rely on it, and rewrite the query in the way I mentioned before:

select id, min(begin_data) beg_d, max(end_data) end_d
from (select t1.*,
sum(start_of_group) over(partition by id order by rn) gr
from (select t.*,
case
when begin_data >
nvl(max(end_data)
over(partition by id order by rn
rows between unbounded preceding and 1 preceding),
begin_data - 1)
then 1
else 0
end start_of_group
from (select tt.*,
row_number() over(partition by id order by begin_data, end_data) rn
from mytable tt) t) t1)
group by id, gr
order by 1, 2

Current month calendar (model clause)

I know there were plenty calendar creation scripts through SQL, but I couldn't find any using the Model clause.

So here what I wrote:

SQL> create or replace view calendar_view as
2 select case when dim=0 then to_char(dw1,' DY')
3 when trunc(sysdate,'mm')<>trunc(dw1,'mm') then null
4 when trunc(sysdate)=dw1 then '['||to_char(dw1,'dd')||']'
5 else to_char(dw1,' dd')
6 end dw1,
7 case when dim=0 then to_char(dw2,' DY')
8 when trunc(sysdate,'mm')<>trunc(dw2,'mm') then null
9 when trunc(sysdate)=dw2 then '['||to_char(dw2,'dd')||']'
10 else to_char(dw2,' dd')
11 end dw2,
12 case when dim=0 then to_char(dw3,' DY')
13 when trunc(sysdate,'mm')<>trunc(dw3,'mm') then null
14 when trunc(sysdate)=dw3 then '['||to_char(dw3,'dd')||']'
15 else to_char(dw3,' dd')
16 end dw3,
17 case when dim=0 then to_char(dw4,' DY')
18 when trunc(sysdate,'mm')<>trunc(dw4,'mm') then null
19 when trunc(sysdate)=dw4 then '['||to_char(dw4,'dd')||']'
20 else to_char(dw4,' dd')
21 end dw4,
22 case when dim=0 then to_char(dw5,' DY')
23 when trunc(sysdate,'mm')<>trunc(dw5,'mm') then null
24 when trunc(sysdate)=dw5 then '['||to_char(dw5,'dd')||']'
25 else to_char(dw5,' dd')
26 end dw5,
27 case when dim=0 then to_char(dw6,' DY')
28 when trunc(sysdate,'mm')<>trunc(dw6,'mm') then null
29 when trunc(sysdate)=dw6 then '['||to_char(dw6,'dd')||']'
30 else to_char(dw6,' dd')
31 end dw6,
32 case when dim=0 then to_char(dw7,' DY')
33 when trunc(sysdate,'mm')<>trunc(dw7,'mm') then null
34 when trunc(sysdate)=dw7 then '['||to_char(dw7,'dd')||']'
35 else to_char(dw7,' dd')
36 end dw7
37 from dual
38 model
39 dimension by (0 dim)
40 measures(cast(null as date) dw1,
41 cast(null as date) dw2,
42 cast(null as date) dw3,
43 cast(null as date) dw4,
44 cast(null as date) dw5,
45 cast(null as date) dw6,
46 cast(null as date) dw7)
47 rules iterate(7) until (dw7[iteration_number]>last_day(sysdate))
48 (dw1[iteration_number]=trunc(sysdate,'mm')-to_char(trunc(sysdate,'mm'),'d')+1+7*(iteration_number-1),
49 dw2[iteration_number]=dw1[CV()]+1,
50 dw3[iteration_number]=dw1[CV()]+2,
51 dw4[iteration_number]=dw1[CV()]+3,
52 dw5[iteration_number]=dw1[CV()]+4,
53 dw6[iteration_number]=dw1[CV()]+5,
54 dw7[iteration_number]=dw1[CV()]+6
55 )
56 /

View created

SQL> alter session set nls_territory=CIS nls_date_language=RUSSIAN;

Session altered

SQL> select * from calendar_view;

DW1 DW2 DW3 DW4 DW5 DW6 DW7
---- ---- ---- ---- ---- ---- ----
ПН ВТ СР ЧТ ПТ СБ ВС
01 02 03 04 05 06
07 08 [09] 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31

6 rows selected

SQL> alter session set nls_territory=AMERICA nls_date_language=AMERICAN;

Session altered

SQL> select * from calendar_view;

DW1 DW2 DW3 DW4 DW5 DW6 DW7
---- ---- ---- ---- ---- ---- ----
SUN MON TUE WED THU FRI SAT
01 02 03 04 05
06 07 08 [09] 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31

6 rows selected

SQL> alter session set nls_territory=GERMANY nls_date_language=GERMAN;

Session altered

SQL> select * from calendar_view;

DW1 DW2 DW3 DW4 DW5 DW6 DW7
---- ---- ---- ---- ---- ---- ----
MO DI MI DO FR SA SO
01 02 03 04 05 06
07 08 [09] 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31

6 rows selected

SQL>

The code is NLS-independant as you can see.
It is pretty easy to understand to my mind, so I'm going to explain it in details as I did with queries in my previous posts, but if you have any questions - you are welcome.

Happy New Year

I know, that I'm a little bit late, but I'd like to say 'Happy New Year' to everyone who reads my blog. As a proverb says "better late than never" ;-)
I was on vacation and was hurring on the last day not to be late for the flight departure.
Now looking at holidays in the retrospective view I liked the most the following (taken from Russian forum) out of all funny queries on New Year's theme:

SQL> WITH
2 q AS (
3 SELECT
4 25-(trunc(ROWNUM/5)+1)*3 + MOD(-ROWNUM,5)*2 num
5 FROM dual
6 CONNECT BY LEVEL<=20
7 )
8 SELECT
9 substr(TRANSLATE(dbms_random.VALUE || dbms_random.VALUE, '0123456789.', '* *'), 1, num)||
10 lpad('/',(MAX(num) over ()-num+1),'/')||lpad('\',(MAX(num) over ()-num+1),'\')||
11 substr(TRANSLATE(dbms_random.VALUE || dbms_random.VALUE, '0123456789.', '* *'), 1, num) new_years_tree
12 FROM q
13 /

NEW_YEARS_TREE
--------------------------------------------------------------------------------
* * * * /\ * * * ** *
** * * * ///\\\ * *
* * * * /////\\\\\ ** *
** * *///////\\\\\\\ * *
** * * //\\ * **
* * ***////\\\\ *
* * //////\\\\\\
* * ////////\\\\\\\\* * *
** * * *//////////\\\\\\\\\\ * *
* * * * */////\\\\\ *
* * ///////\\\\\\\* * * *
* /////////\\\\\\\\\ * *
* ///////////\\\\\\\\\\\ * *
* */////////////\\\\\\\\\\\\\ *
* * * ////////\\\\\\\\* * *
* ** * //////////\\\\\\\\\\ * *
* ////////////\\\\\\\\\\\\ *
* * //////////////\\\\\\\\\\\\\\ *
*////////////////\\\\\\\\\\\\\\\\ * *
**** *///////////\\\\\\\\\\\ * * *

20 rows selected

SQL>