tag:blogger.com,1999:blog-1271763227002553835.post7654009380651485629..comments2024-03-26T08:42:34.744+01:00Comments on Notes on Oracle: Analytic Function bug?Alex Nuijtenhttp://www.blogger.com/profile/06345615264010120428noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-1271763227002553835.post-34948475313701523962010-06-11T08:11:11.002+02:002010-06-11T08:11:11.002+02:00Thank you all for your comments.
Now it makes more...Thank you all for your comments.<br />Now it makes more sense. Thank you, Maxim. And I thought I had Analytic Function nailed.... ;)<br />Probably because I hardly use the Windowing Clause in my queries, this has been bugging me (no pun intended) for quite some time.<br />Never noticed the difference between "expr" and "value_expr" in the documentation before, thanks for pointing that out, Gary.<br /><br />And no, the moderators never gave any feedback.Alex Nuijtenhttps://www.blogger.com/profile/06345615264010120428noreply@blogger.comtag:blogger.com,1999:blog-1271763227002553835.post-40364733414300065422010-06-11T00:58:19.774+02:002010-06-11T00:58:19.774+02:00"I didn't use a Numeric or DATE data type..."I didn't use a Numeric or DATE data type in the ORDER BY expression - I used a VARCHAR2.... And this is in contradiction with the documentation quotes from above."<br /><br />Agree with Maxim. The value_expr it refers to isn't the expression in the ORDER BY. The Documentation (syntax diagram) labels that as the "expr". The label "value_expr" refers to the expression relating to the PRECEDING/FOLLOWING structure (which you don't use at all).SydOraclehttps://www.blogger.com/profile/08828771074492585943noreply@blogger.comtag:blogger.com,1999:blog-1271763227002553835.post-32870169148742498572010-06-10T22:45:13.403+02:002010-06-10T22:45:13.403+02:00Alex,
i believe, documentation is correct on this ...Alex,<br />i believe, documentation is correct on this point. In your example the value_expr is not present at all (i mean the default window), that's why the exception is not raised. In addition, the window limited by unbounded preceding and current row is exactly the same for physical and logical offsets - if the sortkey is unique. However, you may easily see the difference in case the sortkey is not unique - here, physical offsets return undeterministic results, logical offsets - always deterministic. For example:<br /><br />select e.*,<br />sum(sal) over(order by job) sum_default_window,<br />sum(sal) over(order by job rows between unbounded preceding and current row) physical_window,<br />sum(sal) over(order by job range between unbounded preceding and current row) logical_window<br />from emp e<br /><br />Here, you'll see, that default windowing clause yields the same results as with specified range between unbounded preceding and current row. By rows between - the rows with the same values for sort key ( for example scott and ford) become different, arbitrarily assigned values - the result is nondeterministic ( it may be scott with 3000 and may be ford with 3000 - both are in different window, but the assignment is random, because sort key lacks uniqueness). In case of logical offsets - both scott and ford always become 6000 - they are in the same window.<br />Sorry for this rather wordy comment ...<br /><br />Best regards<br /><br />MaximMaximhttps://www.blogger.com/profile/03678566399786170754noreply@blogger.comtag:blogger.com,1999:blog-1271763227002553835.post-15336469749562226642010-06-10T22:42:07.130+02:002010-06-10T22:42:07.130+02:00Did the moderators gave you any feedback why they ...Did the moderators gave you any feedback why they rejected your note ?Anonymousnoreply@blogger.com