WITH Clause help 1

[gtb12314]

Hi,

Here is my query, when I run this query in TOAD I am getting error as "missing select keyword". Please suggest.

WITH eelist AS
(
SELECT al.process_level,al.deptname,al.department,al.emp_status,al.fte_total,al.employee
FROM lrsuser.v_associatelist al
WHERE
al.group_name='G:ACTIVE'AND al.emp_status IN
('CF','E9','EF','N9','NF','CA','EA','NA','CB','EB','NB')
)

Thank you!

 

[Ascalonian]

What is "lruser.v_associatelist"?

 

[gtb12314]

lrsuser is the schema name and v_associatelist is the name of the view.

 

[Ascalonian]

I have never used "WITH -- AS" before. What are you trying to accomplish?

 

[cmmrfrds]

When you make a "With" you need to at least do a select since it is an Inline view otherwise what is the point of doing it.


('CF','E9','EF','N9','NF','CA','EA','NA','CB','EB','NB')
)
Select * from eelist

 

[cmmrfrds]

To help illustrate the advantages of the "With Clause" I am including an example of a query I wrote. The advantage is to isolate the inline views separately from the main SQL and do some preprocessing of the data to create an easy to read SQL statement. You can even nest the inline views as this example does. Notice the main SQL statement is only a view lines at the end.

/* Create a generalized Pool of encounters going back 24 months */
/* Create 12 month windows of encouters (pools) */
/* Slid the monthly 12 windows forward for each Accounting Period */
/* Treat the Charges from the TDL the same as encounters */
With EPool AS
(
SELECT
PE.CONTACT_DATE,
PE.PAT_ENC_CSN_ID
FROM CLARITY.PAT_ENC PE
WHERE PE.contact_date > last_day(add_months(sysdate, -25))
AND APPT_STATUS_C IN (2, 6)
),
Encounters AS
(
SELECT
To_Char(add_months(sysdate, -12),'YYYY') || To_Char(add_months(sysdate, -12),'MM')
as Post_Period,
PAT_ENC_CSN_ID,
NULL AS CHARGE_AMT,
Count(Distinct PAT_ENC_CSN_ID)
AS CSN_COUNT
FROM EPool
WHERE contact_date between last_day(add_months(sysdate, -25)) + 1
and last_day(add_months(sysdate, -12))
Group by
To_Char(add_months(sysdate, -12),'YYYY') || To_Char(add_months(sysdate, -12),'MM'),
PAT_ENC_CSN_ID
UNION ALL
SELECT
To_Char(add_months(sysdate, -11),'YYYY') || To_Char(add_months(sysdate, -11),'MM')
as Post_Period,
PAT_ENC_CSN_ID,
NULL AS CHARGE_AMT,
Count(Distinct PAT_ENC_CSN_ID)
AS CSN_COUNT
FROM EPool
WHERE contact_date between last_day(add_months(sysdate, -24)) + 1
and last_day(add_months(sysdate, -11))
Group by
To_Char(add_months(sysdate, -11),'YYYY') || To_Char(add_months(sysdate, -11),'MM'),
PAT_ENC_CSN_ID
UNION ALL
SELECT
To_Char(add_months(sysdate, -10),'YYYY') || To_Char(add_months(sysdate, -10),'MM')
as Post_Period,
PAT_ENC_CSN_ID,
NULL AS CHARGE_AMT,
Count(Distinct PAT_ENC_CSN_ID)
AS CSN_COUNT
FROM EPool
WHERE contact_date between last_day(add_months(sysdate, -23)) + 1
and last_day(add_months(sysdate, -10))
Group by
To_Char(add_months(sysdate, -10),'YYYY') || To_Char(add_months(sysdate, -10),'MM'),
PAT_ENC_CSN_ID
UNION ALL
SELECT
To_Char(add_months(sysdate, -9),'YYYY') || To_Char(add_months(sysdate, -9),'MM')
as Post_Period,
PAT_ENC_CSN_ID,
NULL AS CHARGE_AMT,
Count(Distinct PAT_ENC_CSN_ID)
AS CSN_COUNT
FROM EPool
WHERE contact_date between last_day(add_months(sysdate, -22)) + 1
and last_day(add_months(sysdate, -9))
Group by
To_Char(add_months(sysdate, -9),'YYYY') || To_Char(add_months(sysdate, -9),'MM'),
PAT_ENC_CSN_ID
UNION ALL
SELECT
To_Char(add_months(sysdate, -8),'YYYY') || To_Char(add_months(sysdate, -8),'MM')
as Post_Period,
PAT_ENC_CSN_ID,
NULL AS CHARGE_AMT,
Count(Distinct PAT_ENC_CSN_ID)
AS CSN_COUNT
FROM EPool
WHERE contact_date between last_day(add_months(sysdate, -21)) + 1
and last_day(add_months(sysdate, -8))
Group by
To_Char(add_months(sysdate, -8),'YYYY') || To_Char(add_months(sysdate, -8),'MM'),
PAT_ENC_CSN_ID
UNION ALL
SELECT
To_Char(add_months(sysdate, -7),'YYYY') || To_Char(add_months(sysdate, -7),'MM')
as Post_Period,
PAT_ENC_CSN_ID,
NULL AS CHARGE_AMT,
Count(Distinct PAT_ENC_CSN_ID)
AS CSN_COUNT
FROM EPool
WHERE contact_date between last_day(add_months(sysdate, -20)) + 1
and last_day(add_months(sysdate, -7))
Group by
To_Char(add_months(sysdate, -7),'YYYY') || To_Char(add_months(sysdate, -7),'MM'),
PAT_ENC_CSN_ID
UNION ALL
SELECT
To_Char(add_months(sysdate, -6),'YYYY') || To_Char(add_months(sysdate, -6),'MM')
as Post_Period,
PAT_ENC_CSN_ID,
NULL AS CHARGE_AMT,
Count(Distinct PAT_ENC_CSN_ID)
AS CSN_COUNT
FROM EPool
WHERE contact_date between last_day(add_months(sysdate, -19)) + 1
and last_day(add_months(sysdate, -6))
Group by
To_Char(add_months(sysdate, -6),'YYYY') || To_Char(add_months(sysdate, -6),'MM'),
PAT_ENC_CSN_ID
UNION ALL
SELECT
To_Char(add_months(sysdate, -5),'YYYY') || To_Char(add_months(sysdate, -5),'MM')
as Post_Period,
PAT_ENC_CSN_ID,
NULL AS CHARGE_AMT,
Count(Distinct PAT_ENC_CSN_ID)
AS CSN_COUNT
FROM EPool
WHERE contact_date between last_day(add_months(sysdate, -18)) + 1
and last_day(add_months(sysdate, -5))
Group by
To_Char(add_months(sysdate, -5),'YYYY') || To_Char(add_months(sysdate, -5),'MM'),
PAT_ENC_CSN_ID
UNION ALL
SELECT
To_Char(add_months(sysdate, -4),'YYYY') || To_Char(add_months(sysdate, -4),'MM')
as Post_Period,
PAT_ENC_CSN_ID,
NULL AS CHARGE_AMT,
Count(Distinct PAT_ENC_CSN_ID)
AS CSN_COUNT
FROM EPool
WHERE contact_date between last_day(add_months(sysdate, -17)) + 1
and last_day(add_months(sysdate, -4))
Group by
To_Char(add_months(sysdate, -4),'YYYY') || To_Char(add_months(sysdate, -4),'MM'),
PAT_ENC_CSN_ID
UNION ALL
SELECT
To_Char(add_months(sysdate, -3),'YYYY') || To_Char(add_months(sysdate, -3),'MM')
as Post_Period,
PAT_ENC_CSN_ID,
NULL AS CHARGE_AMT,
Count(Distinct PAT_ENC_CSN_ID)
AS CSN_COUNT
FROM EPool
WHERE contact_date between last_day(add_months(sysdate, -16)) + 1
and last_day(add_months(sysdate, -3))
Group by
To_Char(add_months(sysdate, -3),'YYYY') || To_Char(add_months(sysdate, -3),'MM'),
PAT_ENC_CSN_ID
UNION ALL
SELECT
To_Char(add_months(sysdate, -2),'YYYY') || To_Char(add_months(sysdate, -2),'MM')
as Post_Period,
PAT_ENC_CSN_ID,
NULL AS CHARGE_AMT,
Count(Distinct PAT_ENC_CSN_ID)
AS CSN_COUNT
FROM EPool
WHERE contact_date between last_day(add_months(sysdate, -15)) + 1
and last_day(add_months(sysdate, -2))
Group by
To_Char(add_months(sysdate, -2),'YYYY') || To_Char(add_months(sysdate, -2),'MM'),
PAT_ENC_CSN_ID
UNION ALL
SELECT
To_Char(add_months(sysdate, -1),'YYYY') || To_Char(add_months(sysdate, -1),'MM')
as Post_Period,
PAT_ENC_CSN_ID,
NULL AS CHARGE_AMT,
Count(Distinct PAT_ENC_CSN_ID)
AS CSN_COUNT
FROM EPool
WHERE contact_date between last_day(add_months(sysdate, -14)) + 1
and last_day(add_months(sysdate, -1))
Group by
To_Char(add_months(sysdate, -1),'YYYY') || To_Char(add_months(sysdate, -1),'MM'),
PAT_ENC_CSN_ID
) -- end of inline views
/*---------------------------------------------------------*/
Select
Encounters.Post_period,
Encounters.pat_enc_csn_id,
Sum(Encounters.csn_count) as Csn_Count
From Encounters
Group By
Encounters.Post_period,
Encounters.pat_enc_csn_id