tag:blogger.com,1999:blog-1271763227002553835.post1104860229495832213..comments2024-03-26T08:42:34.744+01:00Comments on Notes on Oracle: Making up Data with Partition Outer JoinAlex Nuijtenhttp://www.blogger.com/profile/06345615264010120428noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-1271763227002553835.post-13265667878240513802012-12-04T17:30:15.376+01:002012-12-04T17:30:15.376+01:00No, you're not wrong - you're absolutely r...No, you're not wrong - you're absolutely right. It is just as Rob pointed out in the first comment, instead of resetting the results in the table I made the mistake of re-running the INSERT statements too often. Alex Nuijtenhttps://www.blogger.com/profile/06345615264010120428noreply@blogger.comtag:blogger.com,1999:blog-1271763227002553835.post-53941800210921635162012-12-04T16:46:51.792+01:002012-12-04T16:46:51.792+01:00I am not able to see the correct Sum(sales) for ?...I am not able to see the correct Sum(sales) for ?for 14 done it should be 200 not 600 ,Please correct me if i am wrong Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1271763227002553835.post-90893230650590712522012-10-09T13:20:37.251+02:002012-10-09T13:20:37.251+02:00Even though there is the line "partition by&q...Even though there is the line "partition by" in the statement, this doesn't qualify as analytic functions. This functionality can only be used with an OUTER JOIN (hence the name partition outer join).<br />Analytic functions only occur in the SELECT clause of the statement and are real functions that return values.Alex Nuijtenhttps://www.blogger.com/profile/06345615264010120428noreply@blogger.comtag:blogger.com,1999:blog-1271763227002553835.post-83192430812752769242012-10-09T13:00:36.299+02:002012-10-09T13:00:36.299+02:00Isn't this analytic sql? Calling it an outer ...Isn't this analytic sql? Calling it an outer join is a slightly confusing nomenclature, well, for me anyway.<br />But thanks - an easy-to-follow illustration of its use.<br />JAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1271763227002553835.post-85642050582123726022011-06-08T23:27:17.284+02:002011-06-08T23:27:17.284+02:00I have been looking for this information too much ...I have been looking for this information too much time ago, I think this is an interesting topic, and also <br />that it is really helpful, thank you so much for posting this, it has been really interesting, just keep posting, things like this one.viagra onlinehttp://www.iservepharmacy.com/noreply@blogger.comtag:blogger.com,1999:blog-1271763227002553835.post-58391355377236123522009-07-07T19:37:31.403+02:002009-07-07T19:37:31.403+02:00Hi Alex,
Just for fun, another way of doing this ...Hi Alex,<br /><br />Just for fun, another way of doing this partition outer join using the model clause:<br /><br />SQL> select *<br /> 2 from test_table1<br /> 3 group by status<br /> 4 , manager<br /> 5 model<br /> 6 dimension by (status,manager)<br /> 7 measures (sum(sales) s)<br /> 8 rules upsert all<br /> 9 ( s['done',any] = nvl(s[cv(),cv()],0)<br /> 10 , s['in process',any] = nvl(s[cv(),cv()],0)<br /> 11 )<br /> 12 order by manager<br /> 13 , status<br /> 14 /<br /><br />STATUS MANAGER S<br />--------------- ---------- ----------<br />done 14 200<br />in process 14 100<br />done 15 0<br />in process 15 50<br />done 16 50<br />in process 16 0<br /><br />6 rows selected.<br /><br />Oh, and maybe you should end your script with "drop table test_table1", because the number of rows in test_table1 keeps increasing each run ;-)<br /><br />Groet,<br />Rob.Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.com