Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

WITH Clause help 1

Status
Not open for further replies.
gtb12314

gtb12314

Programmer
Joined
Jan 7, 2008
Messages
41
Location
US
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!
 
Sort by date Sort by votes
lrsuser is the schema name and v_associatelist is the name of the view.
 
Upvote 0 Downvote
I have never used "WITH -- AS" before. What are you trying to accomplish?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sqlPower
  • Locked
  • Question Question
Help with Integer to Char to Datetime stamp
Replies
2
Views
1K
SkipVought
S
TheLazyPig
  • Locked
  • Question Question
Merge Into with multiple execution
Replies
1
Views
1K
carp
carp
Andrzejek
  • Locked
  • Question Question
Get part of a string with SQL
Replies
2
Views
1K
Andrzejek
Andrzejek
Tsmtbo2011
  • Locked
  • Question Question
Help Needed!! Micros WS6 610
Replies
1
Views
428
carp
carp
sa0309
  • Locked
  • Question Question
Case expression in the WHERE clause of a delete statment
Replies
2
Views
473
carp
carp

Part and Inventory Search

Sponsor

Back
Top