Wrong encoding with HTTPURITYPE()

Several days ago my colleague asked me - do you have any idea why the following query returns Currency Name in the wrong encoding:

SQL> SELECT EXTRACTVALUE(VALUE(P), '/Valute/NumCode') NUMCODE,
2 EXTRACTVALUE(VALUE(P), '/Valute/CharCode') CHARCODE,
3 EXTRACTVALUE(VALUE(P), '/Valute/Nominal') NOMINAL,
4 EXTRACTVALUE(VALUE(P), '/Valute/Name') NAME,
5 EXTRACTVALUE(VALUE(P), '/Valute/Value') VALUE
6 FROM TABLE(XMLSEQUENCE(HTTPURITYPE('http://www.cbr.ru/scripts/XML_daily.asp?date_req=22.03.2008')
7 .GETXML().EXTRACT('//ValCurs/Valute'))) P
8 WHERE EXTRACTVALUE(VALUE(P), '/Valute/CharCode') = 'USD'
9 /

NUMCODE CHARCODE NOMINAL NAME VALUE
-------------------- -------------------- -------------------- -------------------- --------------------
840 USD 1 Aieea? NOA 23,7773

SQL>

It is a link to our central bank site where rates of different currencies are listed in rouble equivalent.

I have not come across such stuff before - so after reading manual on this topic, I found out that the encoding is not sent explicitly from this cbr http url in the content-type:

SQL> set serveroutput on
SQL>
SQL> DECLARE
2 httpuri HTTPURIType;
3 BEGIN
4 httpuri := HTTPURIType('http://www.rambler.ru/news/top/rss.html');
5 DBMS_OUTPUT.put_line('rambler: ' || httpuri.getContentType());
6 httpuri := HTTPURIType('http://www.cbr.ru/scripts/XML_daily.asp?date_req=22.03.2008');
7 DBMS_OUTPUT.put_line('cbr: ' || httpuri.getContentType());
8 END;
9 /

rambler: text/xml; charset=windows-1251
cbr: text/xml

PL/SQL procedure successfully completed

SQL>

as we can find from the documentation:
The HTTPURITYPE is a subtype of the UriType that provides support for the HTTP protocol. This uses the UTL_HTTP package underneath to access the HTTP URLs.

And in the chapter about UTL_HTTP we can find that:
Following the HTTP protocol standard specification, if the media type of a request or a response is text, but the character set information is missing in the Content-Type header, the character set of the request or response body should default to ISO-8859-1. A response created for a request inherits the default body character set of the request instead of the body character set of the current session. The default body character set is ISO-8859-1 in a database user session.

So this is what happens in our case - default encoding is ISO-8859-1 and names of currencies are wrongly displayed.

The easiest way is to specify the default charset with SET_BODY_CHARSET:

SQL> set colwidth 20
SQL> select utl_http.get_body_charset() from dual;

UTL_HTTP.GET_BODY_CH
--------------------
ISO-8859-1

SQL>
SQL> select * from v$nls_valid_values
2 where value like '%1251%'
3 /

PARAMETER VALUE ISDEPRECATED
-------------------- -------------------- ------------
CHARACTERSET CL8MSWIN1251 FALSE

SQL> exec utl_http.set_body_charset('CL8MSWIN1251');

PL/SQL procedure successfully completed

SQL> select utl_http.get_body_charset() from dual;

UTL_HTTP.GET_BODY_CH
--------------------
CL8MSWIN1251

SQL>
SQL> SELECT EXTRACTVALUE(VALUE(P), '/Valute/NumCode') NUMCODE,
2 EXTRACTVALUE(VALUE(P), '/Valute/CharCode') CHARCODE,
3 EXTRACTVALUE(VALUE(P), '/Valute/Nominal') NOMINAL,
4 EXTRACTVALUE(VALUE(P), '/Valute/Name') NAME,
5 EXTRACTVALUE(VALUE(P), '/Valute/Value') VALUE
6 FROM TABLE(XMLSEQUENCE(HTTPURITYPE('http://www.cbr.ru/scripts/XML_daily.asp?date_req=22.03.2008')
7 .GETXML().EXTRACT('//ValCurs/Valute'))) P
8 WHERE EXTRACTVALUE(VALUE(P), '/Valute/CharCode') = 'USD'
9 /

NUMCODE CHARCODE NOMINAL NAME VALUE
-------------------- -------------------- -------------------- -------------------- --------------------
840 USD 1 Доллар США 23,7773

SQL>

So now the currency name is properly displayed. But this method affects only current session. And this default charset will be left for future requests also.

Another way - is a little bit difficult.
It is based on the fact, that in case you pump blob from the http link - the result encoding is not changed:
URIType Methods:

getBLOB()

Returns the target of the URI as a BLOB value. No character conversion is performed, and the character encoding is that of the URI target. This method can also be used to fetch binary data.

So the idea is to transform this blob value into XML with the needed encoding.
I found a link from the OTN forum, which can be very useful: http://forums.oracle.com/forums/thread.jspa?messageID=1864895�
We can easily create a function, that will take as an input the blob value and the needed encoding for conversion and would return as a result XMLType value:

SQL> create or replace function BLOB2XML(l_blob blob, l_charset varchar2)
2 return xmltype is
3 l_clob clob;
4 l_src_offset number;
5 l_dest_offset number;
6 l_blob_csid number := nvl(nls_charset_id(l_charset), dbms_lob.default_csid);
7 v_lang_context number := dbms_lob.default_lang_ctx;
8 l_warning number;
9 l_amount number;
10 begin
11 dbms_lob.createtemporary(l_clob, true);
12 l_src_offset := 1;
13 l_dest_offset := 1;
14 l_amount := dbms_lob.getlength(l_blob);
15 dbms_lob.converttoclob(l_clob,
16 l_blob,
17 l_amount,
18 l_dest_offset,
19 l_src_offset,
20 l_blob_csid,
21 v_lang_context,
22 l_warning);
23 return xmltype(l_clob);
24 end;
25 /

Function created

SQL> show err blob2xml;
No errors for FUNCTION SYSTEM.BLOB2XML

SQL> set colwidth 20
SQL> select utl_http.get_body_charset() from dual;

UTL_HTTP.GET_BODY_CH
--------------------
ISO-8859-1

SQL>
SQL> SELECT EXTRACTVALUE(VALUE(P), '/Valute/NumCode') NUMCODE,
2 EXTRACTVALUE(VALUE(P), '/Valute/CharCode') CHARCODE,
3 EXTRACTVALUE(VALUE(P), '/Valute/Nominal') NOMINAL,
4 EXTRACTVALUE(VALUE(P), '/Valute/Name') NAME,
5 EXTRACTVALUE(VALUE(P), '/Valute/Value') VALUE
6 FROM TABLE(XMLSEQUENCE(blob2XML(HTTPURITYPE('http://www.cbr.ru/scripts/XML_daily.asp?date_req=22.03.2008')
7 .GETblob(),'CL8MSWIN1251').EXTRACT('//ValCurs/Valute'))) P
8 WHERE EXTRACTVALUE(VALUE(P), '/Valute/CharCode') = 'USD'
9 /

NUMCODE CHARCODE NOMINAL NAME VALUE
-------------------- -------------------- -------------------- -------------------- --------------------
840 USD 1 Доллар США 23,7773

SQL> select utl_http.get_body_charset() from dual;

UTL_HTTP.GET_BODY_CH
--------------------
ISO-8859-1

SQL>

As we can see the default charset for UTL_HTTP package has not being changed.
Just to be sure that the problem was in encoding - let's try our query with ISO-8859-1 charset:

SQL> select * from v$nls_valid_values
2 where upper(value) like '%ISO%8859%1'
3 /

PARAMETER VALUE ISDEPRECATED
-------------------- -------------------- ------------
CHARACTERSET WE8ISO8859P1 FALSE

SQL>
SQL> SELECT EXTRACTVALUE(VALUE(P), '/Valute/NumCode') NUMCODE,
2 EXTRACTVALUE(VALUE(P), '/Valute/CharCode') CHARCODE,
3 EXTRACTVALUE(VALUE(P), '/Valute/Nominal') NOMINAL,
4 EXTRACTVALUE(VALUE(P), '/Valute/Name') NAME,
5 EXTRACTVALUE(VALUE(P), '/Valute/Value') VALUE
6 FROM TABLE(XMLSEQUENCE(blob2XML(HTTPURITYPE('http://www.cbr.ru/scripts/XML_daily.asp?date_req=22.03.2008')
7 .GETblob(),'WE8ISO8859P1').EXTRACT('//ValCurs/Valute'))) P
8 WHERE EXTRACTVALUE(VALUE(P), '/Valute/CharCode') = 'USD'
9 /

NUMCODE CHARCODE NOMINAL NAME VALUE
-------------------- -------------------- -------------------- -------------------- --------------------
840 USD 1 Aieea? NOA 23,7773

SQL>

Yes, this is exactly what we got at our initial query.

Fragments of string between special patterns

Although my solution wasn't rated highly on the forum, I decided to put it here :)

Task:
There is a string value given, comprising a number of elements that are put into particular patterns on both sides.
We need to get all such patterns and inside value out of the string.
E.g. the beginning pattern is '<<<' and the ending pattern is '>>>'.

Solution:

SQL> with t as (select 'xyz<<<testdata>>>123' col1 from dual union all
2 select 'zzz<<<test><<<data>>>ssf' from dual union all
3 select 'sss<<<test><data>>>sffsd' from dual union all
4 select 'ggg<<<test>>>34345<<<data>>>lks' from dual)
5 --
6 select t.*,
7 reverse(regexp_replace(reverse(regexp_replace(col1, '(<<<.*?>>>)|.', '\1')),
8 '(>>>.*?<<<)|.', '\1')) col2
9 from t;

COL1 COL2
------------------------------- ----------------------------------
xyz<<<testdata>>>123 <<<testdata>>>
zzz<<<test><<<data>>>ssf <<<data>>>
sss<<<test><data>>>sffsd <<<test><data>>>
ggg<<<test>>>34345<<<data>>>lks <<<test>>><<<data>>>

SQL>

Explanation:
The first thing what we are doing - is getting all patterns that start from '<<<' and end with '>>>'. By using non-greedy search we have as a result not only one long value, but separated patterns (it is well seen in the fourth row):

SQL> with t as (select 'xyz<<<testdata>>>123' col1 from dual union all
2 select 'zzz<<<test><<<data>>>ssf' from dual union all
3 select 'sss<<<test><data>>>sffsd' from dual union all
4 select 'ggg<<<test>>>34345<<<data>>>lks' from dual)
5 --
6 select t.*,
7 regexp_replace(col1, '(<<<.*?>>>)|.', '\1') col2
8 from t;

COL1 COL2
------------------------------- ----------------------------------
xyz<<<testdata>>>123 <<<testdata>>>
zzz<<<test><<<data>>>ssf <<<test><<<data>>>
sss<<<test><data>>>sffsd <<<test><data>>>
ggg<<<test>>>34345<<<data>>>lks <<<test>>><<<data>>>

SQL>

This technique of getting only desired pattern from the string is described here.

But the result we got is not 100% what were going to receive.
As you can see in the second line there is a resulting value '<<<test><<<data>>>', which is not correct. Because there is an opening pattern '<<<' is met inside the value. So it looks as only '<<<data>>>' should be returned.
For that purpose we need to read the string starting from the end and moving in a backward direction to the very beginning of the value.
In such cases using REVERSE() function could be a solution, although it is not documented. So first we reverse the string and secondly we pass it in the usual way from beginning to the end.
And by the way as we have it reversed all our patterns should also be reversed: the first one '>>>' would be met and then closing '<<<'. In the end we just put the second REVERSE() to get the initial direction of the string:

SQL> with t as (select 'xyz<<<testdata>>>123' col1 from dual union all
2 select 'zzz<<<test><<<data>>>ssf' from dual union all
3 select 'sss<<<test><data>>>sffsd' from dual union all
4 select 'ggg<<<test>>>34345<<<data>>>lks' from dual)
5 --
6 select t.*,
7 regexp_replace(col1, '(<<<.*?>>>)|.', '\1') col2,
8 reverse(regexp_replace(col1, '(<<<.*?>>>)|.', '\1')) col3,
9 regexp_replace(reverse(regexp_replace(col1, '(<<<.*?>>>)|.', '\1')),'(>>>.*?<<<)|.', '\1') col4,
10 reverse(regexp_replace(reverse(regexp_replace(col1, '(<<<.*?>>>)|.', '\1')),'(>>>.*?<<<)|.', '\1')) col5
11 from t;

COL1 COL2 COL3 COL4 COL5
------------------------------- ------------------------ ------------------------ ---------------------- ---------------------
xyz<<<testdata>>>123 <<<testdata>>> >>>atadtset<<< >>>atadtset<<< <<<testdata>>>
zzz<<<test><<<data>>>ssf <<<test><<<data>>> >>>atad<<<>tset<<< >>>atad<<< <<<data>>>
sss<<<test><data>>>sffsd <<<test><data>>> >>>atad<>tset<<< >>>atad<>tset<<< <<<test><data>>>
ggg<<<test>>>34345<<<data>>>lks <<<test>>><<<data>>> >>>atad<<<>>>tset<<< >>>atad<<<>>>tset<<< <<<test>>><<<data>>>

SQL>

As you can see the redundant part '<<<test>' have dissapeared from the line #2.

PS
The opening pattern and closing pattern could be any, e.g. '<<~' as opening and '><<-' as closing, in this case the query would look like:

SQL> with t as (select 'xyz<<~testdata><<-123' col1 from dual union all
2 select 'zzz<<~test><<-data><<-ssf' from dual union all
3 select 'sss<<~test>>>><<-data>>~sffsd' from dual union all
4 select 'ggg<<~test><<-34345<<~data<<~data2><<-lks' from dual)
5 --
6 select t.*,
7 reverse(regexp_replace(reverse(regexp_replace(col1, '(<<~.*?><<-)|.', '\1')),'(-<<>.*?~<<)|.', '\1')) col2
8 from t;

COL1 COL2
----------------------------------------- -----------------------------------
xyz<<~testdata><<-123 <<~testdata><<-
zzz<<~test><<-data><<-ssf <<~test><<-
sss<<~test>>>><<-data>>~sffsd <<~test>>>><<-
ggg<<~test><<-34345<<~data<<~data2><<-lks <<~test><<-<<~data2><<-

SQL>

Patterns evaluation order in regular expressions

After a lull in my blogging activity I decided to write a note on Oracle regular expressions, which can be useful for those who wants to use Oracle regexp functions more efficiently.

We will talk about masks with several alternatives.
Lets look at the following example:

SQL> with t as (select '1H1' str from dual)
2 select regexp_replace(str, '1|1H', 'A') mask1,
3 regexp_replace(str, '1H|1', 'A') mask2
4 from t
5 /

MASK1 MASK2
----- -----
AHA AA

SQL>

"|" (pipe) is OR operator in regular expressions. It is used to list several alternatives to be matched.
But the most important thing is that they are passed one by one in order of apperance inside search mask.
As you can see in the example we have two columns (mask1, mask2). The difference is that we changed places of "1" and "1H", and the results are absolutely different.

So how it is working:
MASK1 ('1|1H'):
In the initial string '1H1' we start to search for the first occurence.
'1' matches the first pattern ('1') and hence replaced with 'A'.
Then proceeding with the rest of the line ('H1').
The next symbol 'H' is not matching '1' so we go to the next pattern ('1H'), but it doesn't match it also.
So we leave it as it is and go to the rest '1', which matches the first pattern of a mask ('1') and consequently replaced with 'A'.

So if we combine all the changes done - we finally get 'AHA'.

MASK2('1H|1'):
Now when we changed the order of the patterns inside the mask - the result would be different.
So we start with the first symbol '1' again.
It matches the beginning of our first pattern ('1H').
And inspite that this symbol is enough to cover the second pattern, we add one more letter to watch whether it satisfies the first pattern or not.
So we add the next symbol 'H' and get '1H' which matches the first pattern, and hence replaced with 'A'.
Then we proceed with the rest of line ('1').
So it doesn't match the first pattern '1H', we check with the second pattern and we find a match, so change '1' to 'A'.
In the final result we have 'AA'.

The main point what should be learnt here is that we don't proceed with the next pattern until we know for sure - that the current pattern wouldn't match.

How can it be used in practice.
In one of my previous posts I already used this technique, but here are couple of other examples recently posted on OTN forum.

Example #1.
Task:
The column comprises the list of names.
We need to get the following result: in case there is only one name in a column - we need to return this name (not touched) with trimmed preceding and trailing spaces.
In case when there are several words in a name - we need to return only first letters (initials).
Solution:

SQL> with t as (select 'Mark Thomsan' str from dual union all
2 select 'Allen' from dual union all
3 select 'John Trovolta Robert' from dual union all
4 select ' John' from dual union all
5 select 'Frederick ' from dual union all
6 select ' Erick Cartman ' from dual union all
7 select ' Michael ' from dual)
8 --
9 select str,regexp_replace(str,'^ *([^ ]*) *$|(^| )([^ ])|.','\1\3') str_new from t
10 /

STR STR_NEW
----------------------- ----------------
Mark Thomsan MT
Allen Allen
John Trovolta Robert JTR
John John
Frederick Frederick
Erick Cartman EC
Michael Michael

7 rows selected

SQL>

Explanation:
We have a mask comprising 3 patterns (splitted with '|'):
1) '^ *([^ ]*) *$'
2) '(^| )([^ ])'
3) '.'

So the first one matches the whole string ('^' as the beginning and '$' as the end), that contains no or only one word ('([^ ]*)'), which is preceded or trailed by any number of spaces (' *').
If our column value is like this - then we return only the word as a result ('\1').
If there is more than one word in a column value - this mask is of no use.
Hence we proceed with the second pattern.
This matches the first letters of each word. We specify that the letters are the first only - by placing '(^| )' before any non-space symbol '([^ ])'.
So all the first letters would be returned in the result '\3' (this will happen only for string which contains > 1 word, otherwise the whole string would be covered by first pattern, and we will never reach the second pattern).
The last pattern '.' is symply any other character - not mentioned in the previous two patterns.
So it is kind of clean up technique to put '|.' in the end of regexp_replace mask.

The result is what we need: Allen, John, Frederick and Michael were returned as they were in the input data. The others (contain more than 1 word) are replaced by initials only.

Example #2.
Task: We need to eliminate all the spaces, which are not between two words.
If there are more than one space between words they should be trimmed to only one.
Solution:

SQL> with t as (select ' 6213, 2345, Application Developer' str from dual union all
2 select '123, Avenue, app. 324, first door second floor' from dual)
3 --
4 select regexp_replace(str,'([[:alpha:]] ) *([[:alpha:]])| |(.)','\1\2\3') new_str from t
5 /

NEW_STR
-------------------------------------------------
6213,2345,Application Developer
123,Avenue,app.324,first door second floor

SQL>

Exaplnation:
We have a mask comprising 3 patterns again:
1) '([[:alpha:]] ) *([[:alpha:]])'
2) ' '
3) '(.)'

The first pattern searches for two letters with at least one space between and returnes these letters with only one space between them ('\1\2').
The second pattern contains only one space and matches all the other spaces, that were not covered by the first pattern.
As we don't have any backreference for this pattern - all such spaces would be eliminated from the initial value.
The third pattern matches any character '(.)'. We remember that all the spaces were covered by first or second pattern, so no spaces would match this '(.)'.
As we have '\3' for this pattern - all such symbols would be returned in the result.
That's how we left one space between letters only, and erased all the other spaces from the sentence.

Magic MIT whiteboard

I came across a post from Claudia Zeiler, where she mentioned about a really interesting stuff. It is not regarding Oracle, but I liked it, so decided to post in my blog also.
I remember the times at my school, when we used wooden boards and were writing with chalks.
Then came soft-tip pens with paper and white boards or stand with throw-over lists of paper.
And look what can be used now :) BTW the reproduction of physical objects natural behaviour is pretty good.
So no doubts, that our children would be cleverer us, using such a stuff while studying.

Prime factors of a number

All we know that every natural number (except 1) can be written as a product of prime numbers, and such a decomposition would be unique for each number. This is what the Fundamental theorem of arithmetic is about.

Let's get a query in SQL, which should return (output) a prime factorization of a given natural number (input).

Some time ago I wrote on a OTN forum a query, using model clause, for finding all prime numbers less than a given one. It uses Sieve of Eratosthenes method.

So using this stuff I added one more model part that would iteratively find out all prime factors of a number (one prime factor at an iteration).

And here is my query:

SQL> var n number
SQL> exec :n:=1260

PL/SQL procedure successfully completed
n
---------
1260

SQL>
SQL> with t as (select level l from dual connect by level <= :n),
2 --
3 t1 as (select l prim_num from
4 (select * from t
5 model
6 dimension by (l dim)
7 measures (l,2 temp)
8 rules iterate (1e8) until (power(temp[1],2)>:n)
9 (l[DIM>TEMP[1]]=decode(mod(l[CV()],temp[1]),0,null,l[CV()]),
10 temp[1]=min(l)[dim>temp[1]])
11 )
12 where l is not null)
13 --
14 select '('||prim_num||'^'||pow||')' prim_factors from (
15 select * from t1
16 model
17 dimension by (rownum rn)
18 measures(prim_num, :n val, 0 pow)
19 rules iterate(1000) until (val[1]=1)
20 (pow[any] order by rn = decode(mod(val[1],prim_num[CV()]),0,pow[CV()]+1,pow[CV()]),
21 val[rn>1] order by rn = decode(mod(val[CV()-1],prim_num[CV()]),0,val[CV()-1]/prim_num[CV()],val[CV()-1]),
22 val[1]=min(val)[any]))
23 where rn>1 and pow>0
24 /

PRIM_FACTORS
--------------------------------------------------------------------------------
(2^2)
(3^2)
(5^1)
(7^1)
n
---------
1260

SQL>

It was not very effective, e.g. to find prime factors of numbers near 100000 it took about 2 or more minutes to execute on my laptop.

So I decided to post it as challenge on russian SQL.RU forum (russian version and english mirror) and there were pretty good solutions.

But I liked the one from andreymx:

SQL> var p_value number
SQL> exec :p_value:=123432435

PL/SQL procedure successfully completed

Executed in 0,03 seconds
p_value
---------
123432435

SQL>
SQL> set timing on
SQL> WITH s1 AS (SELECT LEVEL lv FROM dual CONNECT BY ROWNUM <= SQRT(:p_value)),
2 s2 AS (SELECT lv id1, :p_value/lv id2 FROM s1 WHERE MOD(:p_value, lv)=0),
3 s3 AS (SELECT id1 ID FROM s2 UNION SELECT id2 FROM s2),
4 s4 AS (SELECT ID, (SELECT MIN(ID) FROM s3 s3_1 WHERE s3_1.ID / s3.ID = TRUNC(s3_1.ID / s3.ID) AND s3_1.ID > s3.ID) idd FROM s3)
5 SELECT :p_value||'='||MAX(LTRIM(RTRIM(SYS_CONNECT_BY_PATH(idd/ID, '*'), '*'), '*')) FROM s4
6 CONNECT BY ID=PRIOR IDd
7 START WITH ID=1
8 /

:P_VALUE||'='||MAX(LTRIM(RTRIM
--------------------------------------------------------------------------------
123432435=3*3*5*7*71*5519

Executed in 0,22 seconds
p_value
---------
123432435

SQL>

As you can see it executes a query for huge numbers less than a second :))
Of course, it depends, but still...

Pair of words about it:
First, he finds all the numbers less than square root of a number (s1).

Then finds all the factors of that number among them and the opposite factor (dividing the number by each factor) (s2).
Thats how he saves a lot of resources.

Then Andrey builds a full list of factors for the initial number (s3).

Then he finds for each factor the next bigger factor which is divided by the current one without rest (s4). This trick was interesting.

At the last step he builds a hierarchy from the 1 factor up to the end (the initial number). All the prime factors are calculated as "child" factor/"parent" factor (IDD/ID in the query).

nice, isn't it? :))

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>