tag:blogger.com,1999:blog-7395977411859619892.post6110140306572983137..comments2024-01-30T09:21:06.579+01:00Comments on About Oracle: SQL Model Clause tutorial, part twoRob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-7395977411859619892.post-81086247255819297382013-07-16T11:04:33.755+02:002013-07-16T11:04:33.755+02:00Hi Tuinstoel,
It's better to use "retur...Hi Tuinstoel, <br /><br />It'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']=...).<br /><br />And this solution using dual table is only suitable for code with varchar2(1).<br />If code is length 2 or more, Duke's solution is better.<br /><br />Arsene<br />Zhxiang Xiehttps://www.blogger.com/profile/07116477332368380591noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-17608224793920814142009-08-04T23:36:28.798+02:002009-08-04T23:36:28.798+02:00@Duke
select dummy code, d description
from dual...@Duke<br /><br />select dummy code, d description <br />from dual <br />model <br />dimension by (dummy) <br />measures (cast(null as varchar2(50)) as d)<br />rules<br />( d['P'] = 'Leave With Pay (On Paid Leave of Absence)'<br />, d['T'] = 'Terminated'<br />, d['A'] = 'Active'<br />, d['L'] = 'Leave of Absence (On Unpaid Leave of Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-40179044740424931592009-07-23T15:32:50.555+02:002009-07-23T15:32:50.555+02:00For the multi-variate row generation, I'd agre...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:<br /><br />select code, description from <br />( select cast(NULL as CHAR(1)) code, cast(NULL as VARCHAR2(50)) description from dual )<br />model return updated rows <br />dimension by (code) <br />measures ( CAST(NULL as VARCHAR2(50))DukeGanotehttps://www.blogger.com/profile/01714681520904502051noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-17191437133941861942009-06-15T20:30:54.085+02:002009-06-15T20:30:54.085+02:00Duke,
Yes, that's possible as well. But it...Duke,<br /><br />Yes, 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.<br /><br />Regards,<br />Rob.Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-35788586591114540402009-06-15T01:35:05.085+02:002009-06-15T01:35:05.085+02:00Following the tutorial part 1, the CTAS could be:
...Following the tutorial part 1, the CTAS could be:<br /><br />create table sales as<br />select book, month, country, amount<br /> from ( select cast(null as varchar2(20)) as book<br /> , cast(null as date) as month<br /> , cast(null as varchar2(20)) as country<br /> from dual<br /> )<br />model return updated rows<br />dimension by ( book, month,DukeGanotehttps://www.blogger.com/profile/01714681520904502051noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-78394985970935143382007-11-02T19:59:00.000+01:002007-11-02T19:59:00.000+01:00Yes, that hit the nail on the head, thank you very...Yes, that hit the nail on the head, thank you very much! Thank you for taking the time to help me learn the ropes.Charles Schultzhttps://www.blogger.com/profile/07973399674184183130noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-51597473925176289542007-11-02T13:31:00.000+01:002007-11-02T13:31:00.000+01:00Charles,You can get your required output by this q...Charles,<BR/><BR/>You can get your required output by this query:<BR/><BR/><B>rwijk@ORA11G> select country<BR/> 2 , year<BR/> 3 , to_char(sales,'999990D00') sales<BR/> 4 from sales<BR/> 5 model<BR/> 6 partition by (country)<BR/> 7 dimension by (year)<BR/> 8 measures (sales, max(year) over (partition by country) maxyear)<BR/> 9 rules iterateRob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-89288001517284990502007-11-01T20:05:00.000+01:002007-11-01T20:05:00.000+01:00I came up with one solution, although probably not...I 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.<BR/><BR/>Query for basic model with future dates (using same 2-row sales table above):<BR/>select country, year, sales current_sales, projected_sales<BR/>from<BR/>(<BR/>select country, year, sales from sys.sales<BR/>union<BR/>Charles Schultzhttps://www.blogger.com/profile/07973399674184183130noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-33167709985231185822007-11-01T16:05:00.000+01:002007-11-01T16:05:00.000+01:00Whoops, I just realized that the sample output onl...Whoops, I just realized that the sample output only has the sum of the previous two years. Either way is ok, for the meantime.Charles Schultzhttps://www.blogger.com/profile/07973399674184183130noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-37808005354431015792007-11-01T16:03:00.000+01:002007-11-01T16:03:00.000+01:00Rob, great stuff!! Thanks for posting your example...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.<BR/><BR/>As 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:<BR/><B><BR/>PULSE_SQL > select 'Argentina' country, date '1999-01-01' Charles Schultzhttps://www.blogger.com/profile/07973399674184183130noreply@blogger.com