Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Dave.

Asked: June 05, 2005 - 12:03 pm UTC

Last updated: August 30, 2006 - 8:45 am UTC

Version: 9.2.0.6

Viewed 1000+ times

You Asked

Tom,

Got the following table:

CREATE TABLE TESTTABLE
(
RECID NUMBER(10),
MINUTES NUMBER(10),
RECDATE DATE,
DEPT NUMBER(10)
);

This table contains incident records. Column RECID is a unique
identifier, Column Minutes are the total minutes how long an incident
took, RECDATE is the date the incident took place, dept is the
department that handled the incident.

What I want is to see is a list, grouped by how long an incident
took place (in hours), where the RECDATE is between today and 6
days ago, and where the DEPT is 25.

For that, I wrote the following query :

SELECT V.CAT AS CATEGORY
,V.SOM AS NUMBER_OF_RECORDS
,TO_CHAR(V.SOM / V.TOT * 100, '990.0') AS PERCENTAGE
,TO_CHAR(V.CUM / V.TOT * 100, '990.0') AS CUMULATIVE
FROM (SELECT U.CAT CAT
,U.SOM SOM
,U.PRV CUM
,MAX(U.PRV) OVER() AS TOT
FROM (SELECT T.CAT CAT
,T.INC SOM
,SUM(T.INC) OVER(ORDER BY T.CAT ROWS UNBOUNDED PRECEDING)
AS PRV
FROM (SELECT S.CAT CAT
,COUNT(S.INC) INC
FROM (SELECT CASE
WHEN I.MINUTES / 60 < 0
THEN 'HOURS < 04.'
WHEN I.MINUTES / 60 < 4
THEN '04 < HOURS < 08'
WHEN I.MINUTES / 60 < 8
THEN '08 < HOURS < 16'
WHEN I.MINUTES / 60 < 16
THEN '16 < HOURS < 24'
WHEN I.MINUTES / 60 < 24
THEN '24 < HOURS < 40'
ELSE '40 < HOURS'
END CAT
,I.RECID INC
FROM TESTTABLE I
WHERE TRUNC(I.RECDATE)
BETWEEN TRUNC(( SYSDATE
- 7), 'DAY')
AND TRUNC((SYSDATE), 'DAY')
AND DEPT = 25) S
GROUP BY S.CAT) T) U) V;


The query gave me the following output:

CATEGORY NUMBER_OF_RECORDS PERCENTAGE CUMULATIVE
04 < HOURS < 08 30 90.9 90.9
16 < HOURS < 24 1 3.0 93.9
40 < HOURS 2 6.1 100.0

The result is correct but what I really want is :

CATEGORY NUMBER_OF_RECORDS PERCENTAGE CUMULATIVE

HOURS < 04 0 0 0
04 < HOURS < 08 30 90.9 90.9
08 < HOURS < 16 0 0 90.9
16 < HOURS < 24 1 3.0 93.9
24 < HOURS < 40 0 0 93.9
40 < HOURS 2 6.1 100.0

I want to see all the categories, even if there are no records
in a category.

Any idea what to do?

Thanks in advance,

Dave


and Tom said...

using this technique:

ops$tkyte@ORA9IR2> with categories
2 as
3 (
4 SELECT CASE
5 WHEN l = 1 THEN 'HOURS < 04.'
6 WHEN l = 2 then '04 < HOURS < 08'
7 WHEN l = 3 THEN '08 < HOURS < 16'
8 WHEN l = 4 tHEN '16 < HOURS < 24'
9 WHEN l = 5 THEN '24 < HOURS < 40'
10 ELSE '40 < HOURS'
11 END CAT
12 from (select level l from dual connect by level <= 6)
13 )
14 select * from categories
15 /

CAT
---------------
HOURS < 04.
04 < HOURS < 08
08 < HOURS < 16
16 < HOURS < 24
24 < HOURS < 40
40 < HOURS

6 rows selected.


Now, just outer join to that:


with categories
as
(
SELECT CASE
WHEN l = 1 THEN 'HOURS < 04.'
WHEN l = 2 then '04 < HOURS < 08'
WHEN l = 3 THEN '08 < HOURS < 16'
WHEN l = 4 tHEN '16 < HOURS < 24'
WHEN l = 5 THEN '24 < HOURS < 40'
ELSE '40 < HOURS'
END CAT
from (select level l from dual connect by level <= 6)
)
select * from categories left outer join (YOUR_QUERY_HERE) q on (categories.cat = q.category)
/


Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Helena Marková, June 06, 2005 - 5:24 am UTC


Thanks!

Dave, June 06, 2005 - 7:41 am UTC

This was the answer I was looking for... It works great!





Short and sweet - but where did it come from?

Glenn Feiner, June 06, 2005 - 10:10 am UTC

The only place I can see the use of the "with" clause is for materilized views. Is this the same clause? I'm tryig to understand your thought process and problem solving stragedy, how did you think to use this for this problem?

Tom Kyte
June 06, 2005 - 11:02 am UTC

why do you think they are only used in materialized views?

they are part of SQL and very useful for writing any query.

understanding with clause

Parag Jayant Patankar, June 07, 2005 - 9:46 am UTC

Hi Tom,

I am trying to understand with clause in SQL. I know with clause can be used for recursive and factoring reasons.But I am having very little practical knowledge of using it. Can you explained me your answer to question

ops$tkyte@ORA9IR2> with categories
2 as
3 (
4 SELECT CASE
5 WHEN l = 1 THEN 'HOURS < 04.'
6 WHEN l = 2 then '04 < HOURS < 08'
7 WHEN l = 3 THEN '08 < HOURS < 16'
8 WHEN l = 4 tHEN '16 < HOURS < 24'
9 WHEN l = 5 THEN '24 < HOURS < 40'
10 ELSE '40 < HOURS'
11 END CAT
12 from (select level l from dual connect by level <= 6)
13 )
14 select * from categories
15 /

Q1. more about concept of with clause and why you have choosen to use in this SQL then may be another available options ?

Q2. with "with clause" can I give any name or only table name i.e. table name categories ?

Q3. How you have created categories table and how many rows it is having it, is it more than 6 ?

regards & thanks
pjp

Tom Kyte
June 07, 2005 - 12:47 pm UTC

q1) think of it like a subroutine, it is a way of writing "modular sql". I used it over an inline view for that reason in this case.

with q1
as
( nasty query ),
q2
as
( another query ),
q3
as
( yet another )
select *
from q1, q2, q3
where ....


rather then

select *
from (nasty query ),
(another one),
(yet another )
where ....


q2) with <YOUR_NAME_HERE> as (query), <YOUR_OTHER_NAME_HERE> as (query)...

q3) it is precisely 6 in this example.

with clause

Jon, June 07, 2005 - 10:51 am UTC

Does this help understand "with"?

[pre]
with sub as
(select * from v$session)
select * from sub
[/pre]

is the same as:
[pre]
select sub.*
from (select * from v$session) sub
[/pre]

I get the same query plan in my db too.

Tom Kyte
June 07, 2005 - 12:59 pm UTC

but you might not get the same plan....

WITH CARTESIAN

Duke Ganote, August 05, 2005 - 2:45 pm UTC

This question got me to re-thinking a problem I'd solved (reluctantly) procedurally back in 2003. The issue: I have to display sales for every month and product, with zero value for any month/product combination with no sales. The WITH clause helped me extract the in-line view so I solved in pretty quickly this time:

create table MONTHS as
select level AS MONTH from dual connect by level <= 12
/
create table PRODUCTS as
select chr(64+level) AS PRODUCT from dual connect by level <= 5
/
create table SALES ( MONTH number, PRODUCT varchar2(4), AMT number
constraint SALES_PK ( month, product ) )
/
insert into SALES values ( 6, 'B', 750 )
/
insert into SALES values ( 8, 'D', 900 )
/
with CARTESIAN as
(
select MONTH, PRODUCT
from MONTHS cross join PRODUCTS
)
select C.PRODUCT, C.MONTH, COALESCE(S.AMT,0) SALES_AMT
from CARTESIAN C left join SALES S
on ( C.product = S.product
and C.month = S.month )
order by C.product, C.month
/

PROD MONTH SALES_AMT
---- ---------- ----------
A 1 0
A 2 0
A 3 0
A 4 0
A 5 0
A 6 0
A 7 0
A 8 0
A 9 0
A 10 0
A 11 0
A 12 0
B 1 0
B 2 0
B 3 0
B 4 0
B 5 0
B 6 750
B 7 0
B 8 0
B 9 0
B 10 0
B 11 0
B 12 0
C 1 0
C 2 0
C 3 0
C 4 0
C 5 0
C 6 0
C 7 0
C 8 0
C 9 0
C 10 0
C 11 0
C 12 0
D 1 0
D 2 0
D 3 0
D 4 0
D 5 0
D 6 0
D 7 0
D 8 900
D 9 0
D 10 0
D 11 0
D 12 0
E 1 0
E 2 0
E 3 0
E 4 0
E 5 0
E 6 0
E 7 0
E 8 0
E 9 0
E 10 0
E 11 0
E 12 0

60 rows selected.

partitioned outer join

A reader, August 30, 2005 - 9:53 am UTC 

just as i read this in the docu a few days before. with oracle 10.1 there is a new feature called "partitioned outer join" for creation of dense data (documentation can be found in the datawarehousing guide, chapter 21: sql for analysis and reporting, data densification for reporting).


syntax:
SELECT .....
FROM table_reference
PARTITION BY (expr [, expr ]... )
RIGHT OUTER JOIN table_reference

SELECT .....
FROM table_reference
LEFT OUTER JOIN table_reference
PARTITION BY {expr [,expr ]...)


quick and dirty example:
show me customer salaries per month

TEST @ test101 SQL>desc sales
 Name                                                  Null?    Typ
 ----------------------------------------------------- -------- ------------------------------------
 CUSTOMER_ID                                                    NUMBER
 SALARY_DATE                                                    DATE
 AMOUNT                                                         NUMBER


TEST @ test101 SQL>select * from sales;

CUSTOMER_ID SALARY_D     AMOUNT
----------- -------- ----------
          1 17.01.05      12500
          1 13.05.05       7600
          2 04.03.05       9900
          2 22.04.05       3000
          1 22.01.05        500
          1 05.03.05        100

6 Zeilen ausgewählt.

Abgelaufen: 00:00:00.01

TEST @ test101 SQL>edit
file afiedt.buf wurde geschrieben

  1  select customer_id, last_day (months.first) last_monthday, sum (nvl (amount, 0)) amount
  2  from sales
  3  partition by (customer_id)
  4  right outer join (select     add_months (trunc (sysdate, 'YYYY'), level - 1) first
  5                    from       dual
  6                    connect by level <= 6
  7                   ) months
  8  on (trunc (salary_date, 'MM') = months.first)
  9* group by customer_id, months.first
TEST @ test101 SQL>/

CUSTOMER_ID LAST_MON     AMOUNT
----------- -------- ----------
          1 31.01.05      13000
          1 28.02.05          0
          1 31.03.05        100
          1 30.04.05          0
          1 31.05.05       7600
          1 30.06.05          0
          2 31.01.05          0
          2 28.02.05          0
          2 31.03.05       9900
          2 30.04.05       3000
          2 31.05.05          0
          2 30.06.05          0

12 Zeilen ausgewählt.

Abgelaufen: 00:00:00.02


well done, oracle! :)

cannot use WITH

A reader, August 30, 2006 - 5:04 am UTC

Hi

I am running 8.1.7.4 and I have a similar problem as the first post.

I have this query to find out log switches per hour in the last 7 days (number of days ca change, 7 or 30)

select to_char(FIRST_TIME, 'YYYY-MM-DD HH24') DIA_HORA,
to_char(FIRST_TIME, 'DAY') DIA_SEMANA,
count(*)
from v$log_history
where FIRST_TIME between trunc(sysdate)-7 and trunc(sysdate)
group by to_char(FIRST_TIME, 'YYYY-MM-DD HH24'), to_char(FIRST_TIME, 'DAY');

This shows something like

2006-08-29 09 TUESDAY 1
2006-08-29 11 TUESDAY 1
2006-08-29 14 TUESDAY 1
2006-08-29 16 TUESDAY 1
2006-08-29 17 TUESDAY 2
.....

What i need is

2006-08-29 09 TUESDAY 1
2006-08-29 10 TUESDAY 0
2006-08-29 11 TUESDAY 1
2006-08-29 12 TUESDAY 0
2006-08-29 13 TUESDAY 0
2006-08-29 14 TUESDAY 1
2006-08-29 15 TUESDAY 0
2006-08-29 16 TUESDAY 1
2006-08-29 17 TUESDAY 2

I cannot use the WITH since that works from 9i upwards, what can I do in 8.1.7?

Cheers

Tom Kyte
August 30, 2006 - 8:45 am UTC

you can always use inline views.

instead of

with q
as
(select ...)
select * from q;

you can:


select * from (select ...)


actually, ctl-f for "understanding with clause" on this page, we already discussed it.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.