Re: Connect By query question

That is a nice site that is helpful.
But let's extend your example:
Assume there another table with sales figures for each of the cat_id - Amount in tbl_amount
So,
Select sum(amount) from tbl_amount
where cat_id in (select cat_id

    from category
    start with cat_id = 50000009
   connect by prior cat_id = parent_cat_i)

SALES DEPT 50000095        9,000
 - Inside Sales 50000117       5,000
 --- Product 50000119            1,000
 ----- monitors 50000503           200
 ----- printers 50000502             300
 ----- computers 50000504        200
 ----- Keyboards 50000505        300
 --- Service 50000120               500
-------- Dec - Feb                      100
-------- Mar - May                      200
-------- Jun - Aug                       100
-------- Sep - Nov                      100
 - Outside Sales 50000118     4,000

That works OK. But let's suppose that (you notice I broke down service into quarters) that I only
want the service total to be based on the current date so that in May the service total would
only be 200 not 500. Everything else remains the same. What would the SQL look like?:

This is a real problem for me. So far, there is chance I can use something like:

Select sum(CASE if {somecondition based on date and field} then amount else 0
END) from tbl_amount
where cat_id in (select cat_id

    from category
    start with cat_id = 50000009
   connect by prior cat_id = parent_cat_i)

Any suggestions?

Thanks,
Tim

"Sean Dolan" <nospam_at_nowhere.com> wrote in message news:9e11pt02sbf_at_enews2.newsguy.com...
> I figured it out with the help of:
> http://www.arsdigita.com/books/sql/tress.html
>
> The answer is Create an Index and use it:
>
> create index i$category$hint on category(UPPER(display), cat_id,
> parent_cat_id)
>
> select /*+ INDEX(category i$category$hint) */
> LPAD(' ', 2*(level-1), '-') || display CategoryDisplay, cat_id
> from category
> start with cat_id = 50000009
> connect by prior cat_id = parent_cat_i
>
> This works on the Enterprise Edition only as Function based indexes are
 not
> on the Standard Edition.
> (Use "select * from v$version" to find out)
>
>
>
> "Sean Dolan" <nospam_at_nowhere.com> wrote in message
> news:9e0u2h02n2v_at_enews2.newsguy.com...
> > Oracle 8.1.7:
> >
> > My current query and results:
> > select LPAD(' ', 2*(level-1), '-') || display CategoryDisplay,
 cat_id
> > from category
> > start with cat_id = 50000095
> > connect by prior cat_id = parent_cat_id;
> >
> > Results:
> > ---------------------------------
> > SALES DEPT 50000095
> > - Inside Sales 50000117
> > --- Product 50000119
> > ----- monitors 50000503
> > ----- printers 50000502
> > ----- computers 50000504
> > ----- Keyboards 50000505
> > --- Service 50000120
> > - Outside Sales 50000118
> > --- Product 50000121
> > --- Service 50000122
> > - Computer sales 50000461
> >
> > GOAL : I am trying to alphabetize each level of the tree so that it
 would
> > come out looking like:
> >
> > SALES DEPT 50000095
> > - Computer sales 50000461
> > - Inside Sales 50000117
> > --- Product 50000119
> > ----- computers 50000504
> > ----- Keyboards 50000505
> > ----- monitors 50000503
> > ----- printers 50000502
> > --- Service 50000120
> > - Outside Sales 50000118
> > --- Product 50000121
> > --- Service 50000122
> >
> >
> > Question : How do you order just the level within the tree ????
> >
> > Thanks,
> > Sean Dolan
> >
> >
> >
>
>
Received on Fri May 18 2001 - 08:18:08 CDT