Re: Query Hint

ciccio wrote:
> Hi,
>
> I have a ORDERS table as follows:
>
> ID NUMBER(10)
> ORDER_DATE DATE
> DEPARTMENT NUMBER(10)
> LEVEL NUMBER(2)
>
> If I perform a query like this one:
>
> select department, level, count(*) as QUANTITY from orders
> where level in (select id from LEVELS)
> and (ORDER_DATE between to_date('20012007000000','DDMMYYYYHH24MISS') and
> to_date('21012007000000','DDMMYYYYHH24MISS'))
> group by DEPARTMENT, LEVEL
> /
>
> I have this (correct) answer:
>
> DEPARTMENT LEVEL QUANTITY
> ----------- ---------------- ----------
> 11 1 44
> 11 2 81
> 11 3 4
> 11 4 6
> 11 5 3
> 12 1 36
> 12 2 84
> 12 4 3
> 12 5 12
>
>
> I'd like to have it in another way, this one:
>
> DEPARTMENT LEVEL_1 LEVEL_2 LEVEL_3 LEVEL_4 LEVEL_5
> ---------- ------- ------- ------- ------- -------
> 11 44 81 4 6 3
> 12 36 84 0 10 12
>
> How can I write such a query?
>
> Thanks in advance

SQL> SELECT COUNT(*)
   2 FROM gv$reserved_words
   3 WHERE keyword IN ('ID', 'LEVEL');

   COUNT(*)

          2

SQL> Start by using a different name for your columns.

Then:
www.psoug.org
click on Morgan's Library
scroll down to CONNECT BY.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org