WITH clause

[oradba101]

Greetings,

I an using a WITH clause within an SQL-SELECT statement. Is it possible to nest WITH clauses within the same query?

Regards,


William Chadbourne
Programmer/Analyst

 

[LKBrwnDBA]

Not nested, but you can define several WITH subqueries for the same overall query:

WITH q1 AS (SELECT SYSDATE FROM DUAL)
   , q2 AS (SELECT * FROM MY_TABLE)
...etc...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[cmmrfrds]

I am not sure what you mean by nesting, but here is an example of a With clause I considered nesting from an query I wrote.

/* predefine the inline view in the With Clause */
/* With Clause Example */
With Rankem AS
(
SELECT
ADM.MV_NAME,
ADM.LAST_DATE,
ADM.TOTAL_TIME,
DENSE_RANK() OVER (PARTITION BY ADM.MV_NAME ORDER BY ADM.LAST_DATE DESC NULLS LAST) RANK
FROM CLARITY.ADM_MV_INFO ADM
),
Moreem AS
/* the 2nd with clause uses the first (nesting) */
(
SELECT
MV_NAME,
MAX(CASE WHEN RANK = 1 THEN TOTAL_TIME ELSE 0 END) AS LAST_TIME,
MAX(CASE WHEN RANK = 1 THEN LAST_DATE ELSE NULL END) AS LAST_DATE,
MAX(CASE WHEN RANK = 2 THEN TOTAL_TIME ELSE 0 END) AS PREVIOUS_TIME,
MAX(CASE WHEN RANK = 2 THEN LAST_DATE ELSE NULL END) AS PREVIOUS_DATE
FROM Rankem
GROUP BY MV_NAME
)
/* Select statement that uses the inline wiew */
Select
MV_NAME,
LAST_DATE,
LAST_TIME,
PREVIOUS_TIME,
PREVIOUS_DATE,
Case when PREVIOUS_TIME > LAST_TIME then 0 else
trunc(((LAST_TIME - PREVIOUS_TIME) / 60),2) end
AS RUN_MINUTES
from Moreem
Where LAST_DATE is not null
Order By MV_NAME



Original Statement with inline views.


SELECT
MV_NAME,
LAST_DATE,
LAST_TIME,
PREVIOUS_TIME,
PREVIOUS_DATE,
Case when PREVIOUS_TIME > LAST_TIME then 0 else
trunc(((LAST_TIME - PREVIOUS_TIME) / 60),2) end
AS RUN_MINUTES
FROM
(
SELECT
MV_NAME,
MAX(CASE WHEN RANK = 1 THEN TOTAL_TIME ELSE 0 END) AS LAST_TIME,
MAX(CASE WHEN RANK = 1 THEN LAST_DATE ELSE NULL END) AS LAST_DATE,
MAX(CASE WHEN RANK = 2 THEN TOTAL_TIME ELSE 0 END) AS PREVIOUS_TIME,
MAX(CASE WHEN RANK = 2 THEN LAST_DATE ELSE NULL END) AS PREVIOUS_DATE
FROM
(
SELECT
ADM.MV_NAME,
ADM.LAST_DATE,
ADM.TOTAL_TIME,
DENSE_RANK() OVER (PARTITION BY ADM.MV_NAME ORDER BY ADM.LAST_DATE DESC NULLS LAST) RANK
FROM CLARITY.ADM_MV_INFO ADM
)
GROUP BY MV_NAME
)
Where LAST_DATE is not null
Order By MV_NAME