DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Filling gaps in data

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You mean how analytic functions work?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    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

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    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
  •  


Click Here to Expand Forum to Full Width