SQL Model Clause Tutorial, part one
SQL Model Clause Tutorial, part three
If you prefer to read in French, then you can find a translated version of this tutorial here, and if you prefer to read in German, then you can find a translated version here.
Multi-cell references
It is possible to address multiple cells with a single rule. This is done by doing a so-called multi-cell reference. To show this I will introduce a new demonstration table with a composite primary key to be able to work with multiple dimensions. The table holds monthly sales numbers of two books in 2005 and 2006 and is created like this:
SQL> create table sales 2 as 3 select 'The Da Vinci Code' book, date '2005-03-01' month, 'Netherlands' country, 5 amount from dual union all 4 select 'The Da Vinci Code', date '2005-04-01', 'Netherlands', 8 from dual union all 5 select 'The Da Vinci Code', date '2005-05-01', 'Netherlands', 3 from dual union all 6 select 'The Da Vinci Code', date '2005-07-01', 'Netherlands', 2 from dual union all 7 select 'The Da Vinci Code', date '2005-10-01', 'Netherlands', 1 from dual union all 8 select 'The Da Vinci Code', date '2005-02-01', 'United Kingdom', 15 from dual union all 9 select 'The Da Vinci Code', date '2005-03-01', 'United Kingdom', 33 from dual union all 10 select 'The Da Vinci Code', date '2005-04-01', 'United Kingdom', 47 from dual union all 11 select 'The Da Vinci Code', date '2005-05-01', 'United Kingdom', 44 from dual union all 12 select 'The Da Vinci Code', date '2005-06-01', 'United Kingdom', 11 from dual union all 13 select 'The Da Vinci Code', date '2005-08-01', 'United Kingdom', 2 from dual union all 14 select 'The Da Vinci Code', date '2005-05-01', 'France', 2 from dual union all 15 select 'The Da Vinci Code', date '2005-08-01', 'France', 3 from dual union all 16 select 'The Da Vinci Code', date '2006-01-01', 'France', 4 from dual union all 17 select 'Bosatlas', date '2005-01-01', 'Netherlands', 102 from dual union all 18 select 'Bosatlas', date '2005-02-01', 'Netherlands', 55 from dual union all 19 select 'Bosatlas', date '2005-03-01', 'Netherlands', 68 from dual union all 20 select 'Bosatlas', date '2005-04-01', 'Netherlands', 42 from dual union all 21 select 'Bosatlas', date '2005-05-01', 'Netherlands', 87 from dual union all 22 select 'Bosatlas', date '2005-06-01', 'Netherlands', 40 from dual union all 23 select 'Bosatlas', date '2005-07-01', 'Netherlands', 31 from dual union all 24 select 'Bosatlas', date '2005-08-01', 'Netherlands', 26 from dual union all 25 select 'Bosatlas', date '2005-09-01', 'Netherlands', 22 from dual union all 26 select 'Bosatlas', date '2005-10-01', 'Netherlands', 23 from dual union all 27 select 'Bosatlas', date '2005-11-01', 'Netherlands', 88 from dual union all 28 select 'Bosatlas', date '2005-12-01', 'Netherlands', 143 from dual union all 29 select 'Bosatlas', date '2006-01-01', 'Netherlands', 31 from dual union all 30 select 'Bosatlas', date '2006-02-01', 'Netherlands', 18 from dual union all 31 select 'Bosatlas', date '2006-03-01', 'Netherlands', 15 from dual union all 32 select 'Bosatlas', date '2006-04-01', 'Netherlands', 11 from dual union all 33 select 'Bosatlas', date '2006-05-01', 'Netherlands', 17 from dual union all 34 select 'Bosatlas', date '2006-06-01', 'Netherlands', 9 from dual union all 35 select 'Bosatlas', date '2006-07-01', 'Netherlands', 12 from dual union all 36 select 'Bosatlas', date '2006-08-01', 'Netherlands', 20 from dual union all 37 select 'Bosatlas', date '2006-09-01', 'Netherlands', 4 from dual union all 38 select 'Bosatlas', date '2006-10-01', 'Netherlands', 5 from dual union all 39 select 'Bosatlas', date '2006-11-01', 'Netherlands', 1 from dual union all 40 select 'Bosatlas', date '2006-12-01', 'Netherlands', 1 from dual 41 / Tabel is aangemaakt.
The book called “Bosatlas” has a record for every month, but only in the Netherlands. “The Da Vinci Code” has been sold in three countries, but not for every month. The columns book, month and country make up the primary key for this table. To show the months a little better, I change the date format mask like this:
SQL> alter session set nls_date_format = 'fmmonth yyyy' 2 /
The example below shows how all sales of “Bosatlas” after june 2006 are doubled:
SQL> select book 2 , month 3 , country 4 , amount 5 from sales 6 model 7 return updated rows 8 partition by (country) 9 dimension by (book,month) 10 measures (amount) 11 rules 12 ( amount['Bosatlas',month > date '2006-06-01'] = 13 amount['Bosatlas',cv(month)] * 2 14 ) 15 / BOOK MONTH COUNTRY AMOUNT ----------------- -------------- -------------- ---------- Bosatlas juli 2006 Netherlands 24 Bosatlas augustus 2006 Netherlands 40 Bosatlas september 2006 Netherlands 8 Bosatlas oktober 2006 Netherlands 10 Bosatlas november 2006 Netherlands 2 Bosatlas december 2006 Netherlands 2 6 rijen zijn geselecteerd.
Measure amount now has two dimensions. So we have to provide a book as well as a month, to refer to a cell in the model. With one rule six cells are being modified, due to using the expression month > date ‘2006-06-01’ on the left side of the rule. At the right side the function cv is used to refer to the corresponding value at the left side, where cv stands for current value. Instead of cv(month), I also could have used cv(); because there is only one multi-cell reference present. Even if more multi-cell references are used, cv() may be used, provided it is clear which dimension is being referred to. If this is not clear, you’ll get an ORA-32611:
FOUT in regel N: .ORA-32611: onjuist gebruik van operator MODEL CV
When you want to refer to all dimension values, instead of a subset of the values, you use the word ANY:
SQL> select book 2 , month 3 , country 4 , amount 5 from sales 6 model 7 return updated rows 8 partition by (country) 9 dimension by (book, month) 10 measures (amount) 11 rules 12 ( amount[any,date '2005-08-01'] = 200 13 ) 14 order by book, month 15 / BOOK MONTH COUNTRY AMOUNT ----------------- -------------- -------------- ---------- Bosatlas augustus 2005 Netherlands 200 The Da Vinci Code augustus 2005 United Kingdom 200 The Da Vinci Code augustus 2005 France 200 3 rijen zijn geselecteerd.
In this rule the word ANY is used to refer to all books that have sales records for august 2005. Multi-cell references are also possible with a FOR keyword, for example: amount[‘Bosatlas’,for month from date ‘2005-03-01’ to date ‘2005-08-01’ increment 1], and with a BETWEEN, for example: aantal[‘Bosatlas’],month between date ‘2005-03-01’ and date ‘2005-08-01’], and with all other comparison operators.
Reference models
Reference models are sub models within a model. The main model is able to use all values provided in the reference model, but the values from the reference model are not accessible to the outer select list. We are dealing here with auxiliary data that is read only for the main model. To illustrate this I create an extra table storing prices of the two books:
SQL> create table prices 2 as 3 select 'Bosatlas' book, 42.95 price from dual union all 4 select 'The Da Vinci Code', 19.95 from dual 5 / Tabel is aangemaakt.
The two rows in the price table are supplied to the main model by means of a reference model:
SQL> select book 2 , month 3 , country 4 , amount 5 , to_char(turnover,'99G990D00') turnover 6 from sales 7 where month between date '2005-07-01' and date '2005-12-31' 8 model 9 reference prices on (select book, price from prices) 10 dimension by (book) 11 measures (price) 12 main result 13 partition by (country) 14 dimension by (book, month) 15 measures (0 as turnover, amount) 16 rules 17 ( turnover[any,any] = amount[cv(),cv()] * price[cv(book)] 18 ) 19 order by book 20 , month 21 / BOOK MONTH COUNTRY AMOUNT TURNOVER ----------------- -------------- -------------- ---------- ---------- Bosatlas juli 2005 Netherlands 31 1.331,45 Bosatlas augustus 2005 Netherlands 26 1.116,70 Bosatlas september 2005 Netherlands 22 944,90 Bosatlas oktober 2005 Netherlands 23 987,85 Bosatlas november 2005 Netherlands 88 3.779,60 Bosatlas december 2005 Netherlands 143 6.141,85 The Da Vinci Code juli 2005 Netherlands 2 39,90 The Da Vinci Code augustus 2005 United Kingdom 2 39,90 The Da Vinci Code augustus 2005 France 3 59,85 The Da Vinci Code oktober 2005 Netherlands 1 19,95 10 rijen zijn geselecteerd.
So the reference model also contains dimensions and measures, but it cannot contain a partition clause nor its own rules. You also may have noticed the fact that both the main model and the reference model are now named: the reference model is called prices, prefixed by the keyword REFERENCE, and the main model is called result, prefixed by the keyword MAIN. The measure price from the reference model is being used inside the rule of the main model by the phrase price[cv(book)]. Also new here is the creation of an extra measure called turnover, initially filled with 0 for all existing cells.
Iteration
Iteration offers the opportunity to repeat the same rule multiple times on the same model. In the example below the number of sales of The Da Vinci Code is taken to the fourth power, without using the POWER function, just to show how iterations work. To do so we have to multiply the number of sales three times with the original number of sales, like this:
SQL> select book 2 , month 3 , country 4 , a1 amount 5 , a2 amount_to_the_fourth 6 from sales 7 where book = 'The Da Vinci Code' 8 and country = 'Netherlands' 9 model 10 partition by (country) 11 dimension by (book,month) 12 measures (amount a1, amount a2) 13 rules iterate (3) 14 ( a2[any,any] = a2[cv(),cv()] * a1[cv(),cv()] 15 ) 16 order by month 17 / BOOK MONTH COUNTRY AMOUNT AMOUNT_TO_THE_FOURTH ----------------- -------------- -------------- ---------- -------------------- The Da Vinci Code maart 2005 Netherlands 5 625 The Da Vinci Code april 2005 Netherlands 8 4096 The Da Vinci Code mei 2005 Netherlands 3 81 The Da Vinci Code juli 2005 Netherlands 2 16 The Da Vinci Code oktober 2005 Netherlands 1 1 5 rijen zijn geselecteerd.
Directly after the RULES keyword, we place the keyword ITERATE, followed by the number of iterations. Measure a1 contains all original and unchanged number of sales. Measure a2 initially contains the numbers 5, 8, 3, 2 and 1; the same as a1. During each of the three iterations these numbers are multiplied with the numbers of measure a1. So after 1 iteration, a2 contains 25, 64, 9, 4 and 1, and after the second iteration 125, 512, 27, 8 and 1. And finally the numbers are as can be seen in the result set above.
Very handy in working with iterations is the keyword UNTIL followed by a boolean expression to be able skip unnecessary iterations. The word ITERATION_NUMBER can be used in the rule which holds the number of iterations. But be warned: it starts with 0. So in the example above, with iterate (3), it increases from 0 until 2.
To show how UNTIL and ITERATION_NUMBER work, I came up with an example where predictions of future sales are generated according to the rule that they will drop with 75% each year. So each year we will only sell 25% of the books as the same month previous year, where any fractions are truncated. The statement will have to generate sales records until there isn’t a single book sold in an entire year. This query will do that for you:
SQL> select book 2 , country 3 , to_date(to_char(y) || to_char(m),'yyyymm') month 4 , amount 5 from sales 6 where book = 'Bosatlas' 7 and extract (year from month) = 2006 8 model 9 partition by ( book, country) 10 dimension by ( extract(year from month) y, extract(month from month) m) 11 measures (amount, 0 max_monthly_amount) 12 rules upsert all 13 iterate (100) until (max_monthly_amount[2007+iteration_number,1] < 4) 14 ( amount[2007+iteration_number,any] 15 = trunc(amount[2006+iteration_number,cv()]/4) 16 , max_monthly_amount[2007+iteration_number,1] 17 = max(amount)[2007+iteration_number,m between 1 and 12] 18 ) 19 order by y, m 20 /
Now this is a lot more complex than what we have seen before. As you can see by the where clause, we’ll start with the 12 rows for the book “Bosatlas” in 2006. The month column is split into two dimensions: the year and the month number. This is done to be able to make 12 rows for an entire year in one shot. To prevent making a year with only 0 sales for all months, I created an auxiliary measure called max_monthly_amount. This measure is populated by an analytic function with the syntax max(amount)[2007+iteration_number, month_number between 1 and 12] and gives the maximum sales number for the current year. This measure is used in the UNTIL clause. The iterations should stop when this highest number is below 4. For if there is a sales amount of 4 or higher, this will lead to a non zero number in the next year. With the current data set, out of the specified 100 iterations only two iterations are executed leading to this result set:
BOOK COUNTRY MONTH AMOUNT ----------------- -------------- -------------- ---------- Bosatlas Netherlands januari 2006 31 Bosatlas Netherlands februari 2006 18 Bosatlas Netherlands maart 2006 15 Bosatlas Netherlands april 2006 11 Bosatlas Netherlands mei 2006 17 Bosatlas Netherlands juni 2006 9 Bosatlas Netherlands juli 2006 12 Bosatlas Netherlands augustus 2006 20 Bosatlas Netherlands september 2006 4 Bosatlas Netherlands oktober 2006 5 Bosatlas Netherlands november 2006 1 Bosatlas Netherlands december 2006 1 Bosatlas Netherlands januari 2007 7 Bosatlas Netherlands februari 2007 4 Bosatlas Netherlands maart 2007 3 Bosatlas Netherlands april 2007 2 Bosatlas Netherlands mei 2007 4 Bosatlas Netherlands juni 2007 2 Bosatlas Netherlands juli 2007 3 Bosatlas Netherlands augustus 2007 5 Bosatlas Netherlands september 2007 1 Bosatlas Netherlands oktober 2007 1 Bosatlas Netherlands november 2007 0 Bosatlas Netherlands december 2007 0 Bosatlas Netherlands januari 2008 1 Bosatlas Netherlands februari 2008 1 Bosatlas Netherlands maart 2008 0 Bosatlas Netherlands april 2008 0 Bosatlas Netherlands mei 2008 1 Bosatlas Netherlands juni 2008 0 Bosatlas Netherlands juli 2008 0 Bosatlas Netherlands augustus 2008 1 Bosatlas Netherlands september 2008 0 Bosatlas Netherlands oktober 2008 0 Bosatlas Netherlands november 2008 0 Bosatlas Netherlands december 2008 0 36 rijen zijn geselecteerd.
In this query the UPSERT ALL mode is used. It is one of the three possible modes: UPDATE, UPSERT and UPSERT ALL, where UPSERT is the default and this mode was used in all previous examples. In UPDATE mode it is not possible to create new rows as can be done with UPSERT. The difference between UPSERT and UPSERT ALL is that the latter creates new cells if the left hand side of the rule contains non-positional references. For example: if the left hand side of the rule contains the ANY keyword, in UPSERT mode it would look at all existing cells and find all matches. Using the UPSERT ALL mode, it would create new cells, one for each match of the non-positional dimension values.
More functions and features are available with the model clause, which really are worth the study, but I will only mention them here briefly. First of all there are the functions PRESENTV and PRESENTNNV, the predicate IS PRESENT and the keywords KEEP NAV and IGNORE NAV. In short you can distinguish between missing cells and existing cells with NULL values with these. Then there is the PREVIOUS function to be able to refer to the previous iteration value inside the UNTIL clause during iterations. And finally, I mention the clauses AUTOMATIC ORDER and SEQUENTIAL ORDER, which gives the possibility to Oracle to decide for itself in which order the rules should be evaluated. All clauses and functions mentioned in this paragraph are quite clearly explained in the documentation.
In the last part I will try to explain what practical use the model clause can have. I will show some solutions for previously unsolvable questions and for common problems that can be solved more efficiently than with previously known techniques. Frequent visitors of the OTN Forums will undoubtedly know which problems I am hinting at here. I will try to get this last part published in a magazine first. If it succeeds, I will point to the online version of course. If it does not succeed, then I will post the third part here. So I’ll have to ask for your patience on this one.
SQL script with all statements used
Rob, great stuff!! Thanks for posting your examples. As a newbie to the analytic scene, I am still learning a bit about the MODEL clause. Your notes help a bit.
ReplyDeleteAs mentioned in the OTN forums, I have been following the example from chapter 22 of the Data Warehousing Guide. For a very simple sample of data, try this:
PULSE_SQL > select 'Argentina' country, date '1999-01-01' year, 100.76 sales from dual;
COUNTRY YEAR SALES
--------- --------------- ----------
Argentina 01-JAN-99 100.76
PULSE_SQL > create table sales
2 as
3 select 'Argentina' country, date '1999-01-01' year, 100.76 sales from dual union all
4 select 'Argentina' country, date '2000-01-01' year, 145.02 sales from dual;
Table created.
PULSE_SQL > select * from sales;
COUNTRY YEAR SALES
--------- --------------- ----------
Argentina 01-JAN-99 100.76
Argentina 01-JAN-00 145.02
I want to eventually trend the next five years using REGR_SLOPE. The Warehouse Guide has a good example, but only for a specific year, not a dynamic year, and not a range of years. But for the purposes of learning, I would be happy to start with something basic like a sum of all previous years. The output would be like:
COUNTRY YEAR SALES
--------- --------------- ----------
Argentina 01-JAN-99 100.76
Argentina 01-JAN-00 145.02
Argentina 01-JAN-01 245.78
Argentina 01-JAN-02 390.80
Argentina 01-JAN-03 636.58
Argentina 01-JAN-04 1027.38
Argentina 01-JAN-05 1663.96
My end goal will be to compare the projected sales against real sales where sales data exist, and forecast sales data for the future. But like I said, I want to work up to that level of complexity slowly. =)
Whoops, I just realized that the sample output only has the sum of the previous two years. Either way is ok, for the meantime.
ReplyDeleteI came up with one solution, although probably not elegant. I union fake future data to get the future dates; since future sales are an unknown, they are all projected.
ReplyDeleteQuery for basic model with future dates (using same 2-row sales table above):
select country, year, sales current_sales, projected_sales
from
(
select country, year, sales from sys.sales
union
select distinct country, to_Date(to_char(year,'YYYY') + level||'-01-01','YYYY-MM-DD') year, 0 sales
from (select distinct country, first_value(year) over (partition by country order by year desc) year from sys.sales)
where level > 0
connect by level < 6
)
model
partition by (country)
dimension by (year)
measures (sales, CAST(NULL AS NUMBER) "PROJECTED_SALES")
ignore nav
rules
(
projected_sales[year] order by year = sum(sales)[year < cv()] + sum(projected_sales)[year < cv()]
)
/
However, it stops summing when the sales dimension runs out. I have not yet figured out a way to keep summing the project_sales. I have a REGR_SLOPE example that I could show, but I wanted to focus on what should be an easier example first.
Charles,
ReplyDeleteYou can get your required output by this query:
rwijk@ORA11G> select country
2 , year
3 , to_char(sales,'999990D00') sales
4 from sales
5 model
6 partition by (country)
7 dimension by (year)
8 measures (sales, max(year) over (partition by country) maxyear)
9 rules iterate(5)
10 ( sales[add_months(maxyear[date '1999-01-01'], 12 * (iteration_number+1))]
11 = sum(sales)[year between add_months(cv(),-24) and cv()]
12 -- replace the line above with "sum(sales)[any]" for a total sum
13 )
14 order by country
15 , year
16 /
COUNTRY YEAR SALES
--------- ------------------- ----------
Argentina 01-01-1999 00:00:00 100,76
Argentina 01-01-2000 00:00:00 145,02
Argentina 01-01-2001 00:00:00 245,78
Argentina 01-01-2002 00:00:00 390,80
Argentina 01-01-2003 00:00:00 636,58
Argentina 01-01-2004 00:00:00 1027,38
Argentina 01-01-2005 00:00:00 1663,96
7 rijen zijn geselecteerd.
Please notice that I've hardcoded the date '1999-01-01' here. Any occurring date value will do here. This is needed to get the maxyear value with year being a dimension.
I hope this helps.
Regards,
Rob.
Yes, that hit the nail on the head, thank you very much! Thank you for taking the time to help me learn the ropes.
ReplyDeleteFollowing the tutorial part 1, the CTAS could be:
ReplyDeletecreate table sales as
select book, month, country, amount
from ( select cast(null as varchar2(20)) as book
, cast(null as date) as month
, cast(null as varchar2(20)) as country
from dual
)
model return updated rows
dimension by ( book, month, country )
measures ( cast(null as number) as amount )
rules
( amount['The Da Vinci Code', to_date('2005-03-01','yyyy-mm-dd'), 'Netherlands'] = 5
, amount['The Da Vinci Code', to_date('2005-04-01','yyyy-mm-dd'), 'Netherlands'] = 8
, amount['The Da Vinci Code', to_date('2005-05-01','yyyy-mm-dd'), 'Netherlands'] = 3
, amount['The Da Vinci Code', to_date('2005-07-01','yyyy-mm-dd'), 'Netherlands'] = 2
, amount['The Da Vinci Code', to_date('2005-10-01','yyyy-mm-dd'), 'Netherlands'] = 1
, amount['The Da Vinci Code', to_date('2005-02-01','yyyy-mm-dd'), 'United Kingdom'] = 15
, amount['The Da Vinci Code', to_date('2005-03-01','yyyy-mm-dd'), 'United Kingdom'] = 33
, amount['The Da Vinci Code', to_date('2005-04-01','yyyy-mm-dd'), 'United Kingdom'] = 47
, amount['The Da Vinci Code', to_date('2005-05-01','yyyy-mm-dd'), 'United Kingdom'] = 44
, amount['The Da Vinci Code', to_date('2005-06-01','yyyy-mm-dd'), 'United Kingdom'] = 11
, amount['The Da Vinci Code', to_date('2005-08-01','yyyy-mm-dd'), 'United Kingdom'] = 2
, amount['The Da Vinci Code', to_date('2005-05-01','yyyy-mm-dd'), 'France'] = 2
, amount['The Da Vinci Code', to_date('2005-08-01','yyyy-mm-dd'), 'France'] = 3
, amount['The Da Vinci Code', to_date('2006-01-01','yyyy-mm-dd'), 'France'] = 4
, amount['Bosatlas', to_date('2005-01-01','yyyy-mm-dd'), 'Netherlands'] = 102
, amount['Bosatlas', to_date('2005-02-01','yyyy-mm-dd'), 'Netherlands'] = 55
, amount['Bosatlas', to_date('2005-03-01','yyyy-mm-dd'), 'Netherlands'] = 68
, amount['Bosatlas', to_date('2005-04-01','yyyy-mm-dd'), 'Netherlands'] = 42
, amount['Bosatlas', to_date('2005-05-01','yyyy-mm-dd'), 'Netherlands'] = 87
, amount['Bosatlas', to_date('2005-06-01','yyyy-mm-dd'), 'Netherlands'] = 40
, amount['Bosatlas', to_date('2005-07-01','yyyy-mm-dd'), 'Netherlands'] = 31
, amount['Bosatlas', to_date('2005-08-01','yyyy-mm-dd'), 'Netherlands'] = 26
, amount['Bosatlas', to_date('2005-09-01','yyyy-mm-dd'), 'Netherlands'] = 22
, amount['Bosatlas', to_date('2005-10-01','yyyy-mm-dd'), 'Netherlands'] = 23
, amount['Bosatlas', to_date('2005-11-01','yyyy-mm-dd'), 'Netherlands'] = 88
, amount['Bosatlas', to_date('2005-12-01','yyyy-mm-dd'), 'Netherlands'] = 143
, amount['Bosatlas', to_date('2006-01-01','yyyy-mm-dd'), 'Netherlands'] = 31
, amount['Bosatlas', to_date('2006-02-01','yyyy-mm-dd'), 'Netherlands'] = 18
, amount['Bosatlas', to_date('2006-03-01','yyyy-mm-dd'), 'Netherlands'] = 15
, amount['Bosatlas', to_date('2006-04-01','yyyy-mm-dd'), 'Netherlands'] = 11
, amount['Bosatlas', to_date('2006-05-01','yyyy-mm-dd'), 'Netherlands'] = 17
, amount['Bosatlas', to_date('2006-06-01','yyyy-mm-dd'), 'Netherlands'] = 9
, amount['Bosatlas', to_date('2006-07-01','yyyy-mm-dd'), 'Netherlands'] = 12
, amount['Bosatlas', to_date('2006-08-01','yyyy-mm-dd'), 'Netherlands'] = 20
, amount['Bosatlas', to_date('2006-09-01','yyyy-mm-dd'), 'Netherlands'] = 4
, amount['Bosatlas', to_date('2006-10-01','yyyy-mm-dd'), 'Netherlands'] = 5
, amount['Bosatlas', to_date('2006-11-01','yyyy-mm-dd'), 'Netherlands'] = 1
, amount['Bosatlas', to_date('2006-12-01','yyyy-mm-dd'), 'Netherlands'] = 1
)
/
Duke,
ReplyDeleteYes, that's possible as well. But it's not exactly easier to read in my opinion. And using the SQL Model Clause like this in a piece explaining the SQL Model Clause, may not be very inviting for people who come here without prior knowledge about the subject. But showing alternatives is always a good thing, so thanks for that.
Regards,
Rob.
For the multi-variate row generation, I'd agree it's a toss-up on aesthetics. However, for the simple cases, I think MODEL is easier to read; for example:
ReplyDeleteselect code, description from
( select cast(NULL as CHAR(1)) code, cast(NULL as VARCHAR2(50)) description from dual )
model return updated rows
dimension by (code)
measures ( CAST(NULL as VARCHAR2(50)) as description )
rules
( description['P'] = 'Leave With Pay (On Paid Leave of Absence)'
, description['T'] = 'Terminated'
, description['A'] = 'Active'
, description['L'] = 'Leave of Absence (On Unpaid Leave of Absence)'
, description['Q'] = 'Retired with Pay'
, description['R'] = 'Retired'
, description['S'] = 'Suspended'
, description['U'] = 'Terminated with Pay'
, description['V'] = 'Termination Pension Pay out'
, description['W'] = 'Short Work Break'
, description['X'] = 'Retire Pension-Administration'
, description['D'] = 'Deceased'
)
@Duke
ReplyDeleteselect dummy code, d description
from dual
model
dimension by (dummy)
measures (cast(null as varchar2(50)) as d)
rules
( d['P'] = 'Leave With Pay (On Paid Leave of Absence)'
, d['T'] = 'Terminated'
, d['A'] = 'Active'
, d['L'] = 'Leave of Absence (On Unpaid Leave of Absence)'
, d['Q'] = 'Retired with Pay'
, d['R'] = 'Retired'
, d['S'] = 'Suspended'
, d['U'] = 'Terminated with Pay'
, d['V'] = 'Termination Pension Pay out'
, d['W'] = 'Short Work Break'
, d['X'] = 'Retire Pension-Administration'
, d['D'] = 'Deceased'
)
/
tuinstoel
Hi Tuinstoel,
DeleteIt's better to use "return updated rows", or it results dummy row (code = X, dexcription is null) without description definition of code X (d['X']=...).
And this solution using dual table is only suitable for code with varchar2(1).
If code is length 2 or more, Duke's solution is better.
Arsene