-
Filling gaps in data
Hi
Can somebody gently expalin me this.I am breaking my head and loosing my remaining hair over this :-)
http://download-west.oracle.com/docs...6/analysis.htm
The part which is troubling me is filling gaps in data.And the subquery factoring using with clause.
regards
Hrishy
-
You mean how analytic functions work?
-
Oh sorry, I see.
Well all they're saying there is that sometimes your data doesn't contain every possible value of every dimension -- as if your store went a whole day without selling any baked beans, for example. If you wanted to report "0" sales for baked beans for that day then you need to return a row that doesn't actually exist in your fact table.
I must say though that the method used in the docs seems rather odd ... there are easier ways than that.
-
Hi Slim
Beleive it or not i was expecting you to come out with reply.Reason is i was working on a similar problem and did remember that there was a way out.I was impressed by the answer for this question
http://forums.dbasupport.com/forums/...ad.php?t=48063
was working on it (and you did beat me for the answer).
My doubt is with the partition by clause for filling or making up for the missing data and the with clause.I am not able to understand it.
regards
Hrishy
-
The WITH clause is known as "subquery factoring", and allows re-use of a result set within a query -- a typical example is where you want to find all the customers whose individual total sales are more than the average of all customers' indivisual total sales, in which case a common factor to the two expressions to be compared might be ...
Code:
Select cust#, sum(sales) from sales group by cust#
Anyway, have a search of the docs and AskTom for subquery factoring.
This partitioned outer join syntax perplexes me, though. None of the tools that I have used would be able to generate such syntax (hand-coding aside) so it has limited value to me at least. Unless it seems like a problem solver for you, I wouldn't be too worried about it.
-
Hi Dave
I am kind of interested in that syntax of partition join with the with syntax.
The thing that caught my attention most is the fact to generate missing values (or making up values using the with clause) with sub query factoring but the examples in the documentation are not so good.
http://forums.dbasupport.com/forums...ead.php?t=48063
Just wundering if we can rewrite your solution using the with clause.
regards
Hrishy
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|