3

I am attempting to write a CTE query that is probably the most advanced query for me. (Been working with SQL for 3 weeks). When I try to run this query, I get an error of

Msg 252, Level 16, State 1, Line 4 Recursive common table expression 'Sixer' does not contain a top-level UNION ALL operator.

So here is the skinny - I am including DDL (which is HUGE) and also stating that table fiver and table first look the same, but they are different as the fact that first could contain acctNum that do not exist in fiver and vice versa. Now here is DDL

Create Table First
(
    Doctor  varchar(100)
    ,AcctNum varchar(10)
    ,ChargedAmt decimal(10,2)
    ,DOS date
)
Insert Into First (Doctor, AcctNum, ChargedAmt, DOS) VALUES
('Doctor A',    '912224661',    '1329', '1/31/2016'),
('Doctor A',    '912221628',    '12203',    '1/31/2015'),
('Doctor A',    '912219195',    '1096', '5/14/2014'),
('Doctor A',    '912224987',    '4195.42',  '2/29/2016'),
('Doctor A',    '912219200',    '11442.03', '5/16/2014'),
('Doctor A',    '912217592',    '7223.77',  '12/26/2013'),
('Doctor A',    '912217652',    '2570.24',  '12/31/2013'),
('Doctor A',    '912221613',    '332.51',   '2/28/2015'),
('Doctor A',    '912218592',    '13087',    '3/31/2014'),
('Doctor B',    '912212830',    '260',  '7/28/2012'),
('Doctor B',    '912217148',    '216',  '10/25/2013'),
('Doctor B',    '912213308',    '225',  '8/31/2012'),
('Doctor B',    '912225658',    '1120', '5/18/2016'),
('Doctor B',    '912224030',    '1044.1',   '10/27/2015'),
('Doctor B',    '912224821',    '270',  '2/15/2016'),
('Doctor B',    '912215253',    '1833', '4/12/2013'),
('Doctor B',    '912215226',    '1943', '4/26/2013'),
('Doctor B',    '912224103',    '697.05',   '10/31/2015'),
('Doctor C',    '912226150',    '321.16',   '7/29/2016'),
('Doctor C',    '912226728',    '409.87',   '8/31/2016'),
('Doctor D',    '912227487',    '1579.39',  '11/28/2016'),
('Doctor D',    '912227536',    '689.03',   '11/28/2016'),
('Doctor E',    '912225574',    '252.45',   '4/22/2016'),
('Doctor E',    '912225190',    '443.13',   '3/25/2016'),
('Doctor F',    '912228020',    '5341', '1/31/2017'),
('Doctor F',    '912216621',    '2550', '8/30/2013'),
('Doctor F',    '912224493',    '5908.48',  '12/31/2015'),
('Doctor F',    '912210468',    '5863.19',  '1/31/2012'),
('Doctor F',    '912227606',    '4657', '12/16/2016'),
('Doctor F',    '912212275',    '12202.39', '5/31/2012'),
('Doctor F',    '912213649',    '3640', '10/31/2012'),
('Doctor F',    '912226417',    '2801', '8/12/2016'),
('Doctor F',    '912226633',    '452',  '8/24/2016'),
('Doctor G',    '912212694',    '0',    '7/18/2012'),
('Doctor G',    '912214793',    '11659',    '2/28/2013'),
('Doctor G',    '912224178',    '4749', '11/17/2015'),
('Doctor G',    '912219997',    '16501',    '8/20/2014'),
('Doctor G',    '912222180',    '15872',    '3/31/2015'),
('Doctor G',    '912223090',    '18101',    '7/14/2015'),
('Doctor G',    '912212692',    '8197', '7/18/2012'),
('Doctor G',    '912222048',    '0',    '6/26/2015'),
('Doctor G',    '912223539',    '2429', '8/31/2015'),
('Doctor H',    '912220020',    '12262',    '8/22/2014'),
('Doctor H',    '912223503',    '12377',    '8/31/2015'),
('Doctor H',    '912212365',    '43987.59', '6/25/2012'),
('Doctor H',    '912212363',    '615',  '6/29/2012'),
('Doctor H',    '912214260',    '2661', '12/31/2012'),
('Doctor H',    '912210695',    '199',  '1/28/2012'),
('Doctor H',    '912216444',    '4425', '8/22/2013'),
('Doctor H',    '912216446',    '3475', '8/22/2013'),
('Doctor H',    '912227667',    '12522',    '12/21/2016'),
('Doctor I',    '912216502',    '500',  '8/28/2013'),
('Doctor I',    '912222006',    '4379', '2/28/2015'),
('Doctor I',    '912212492',    '3532', '6/30/2012'),
('Doctor I',    '912212934',    '4625', '8/10/2012'),
('Doctor I',    '912213175',    '5802', '9/28/2012'),
('Doctor I',    '912210537',    '2968.79',  '1/31/2012'),
('Doctor I',    '912225766',    '4365', '5/27/2016'),
('Doctor I',    '912225768',    '4236', '5/27/2016'),
('Doctor I',    '912210536',    '4571', '1/27/2012')


Create Table Sec
(
    Doctor  varchar(100)
    ,PendingCharges decimal(10,2)
)
Insert Into SEC (doctor, pendingcharges) VALUES
('Doctor A',    '100.12'),
('Doctor C',    '200.1'),
('Doctor I',    '300.12')

Create Table Tree
(
    Doctor  varchar(100)
    ,InsuranceReviewAmt decimal(10,2)
)
Insert Into Tree (doctor, insurancereviewamt) VALUES
('Doctor F',    '8710.12    ')

Create Table Four
(
    Doctor  varchar(100)
    ,prevyrbills decimal(10,2)
    ,AcctNum varchar(10)
    ,DOS date
)
INSERT INTO four (doctor, prevyrbills, AcctNum, Dos) VALUES
('Doctor A',    '100.12',   '999111333',    '1/1/2016'),
('Doctor A',    '200.12',   '888111999',    '1/3/2016'),
('Doctor A',    '500.44',   '333888111',    '4/1/2016'),
('Doctor C',    '800.11',   '111888333',    '6/1/2016'),
('Doctor E',    '700.11',   '444333888',    '8/1/2016'),
('Doctor E',    '900.12',   '222331992',    '10/1/2016')

Create Table AllDocs
(
    Doctor varchar(100)
)
Insert Into AllDocs (Doctor) VALUES
('Doctor A'),('Doctor B'),('Doctor C'),
('Doctor D'),('Doctor E'),('Doctor F'),
('Doctor G'),('Doctor H'),('Doctor I'),
('Doctor J'),('Doctor K'),('Doctor L'),
('Doctor M'),('Doctor N'),('Doctor O'),
('Doctor P'),('Doctor Q'),('Doctor R')


Create Table Fiver
(
    Doctor  varchar(100)
    ,AcctNum varchar(10)
    ,ChargedAmt decimal(10,2)
    ,DOS date
)
Insert Into Fiver (doctor, acctnum, chargedamt, DOS) VALUES
('Doctor A',    '912224661',    '1329', '1/31/2016'),
('Doctor A',    '912221628',    '12203',    '1/31/2015'),
('Doctor A',    '912219195',    '1096', '5/14/2014'),
('Doctor A',    '912224987',    '4195.42',  '2/29/2016'),
('Doctor A',    '912219200',    '11442.03', '5/16/2014'),
('Doctor A',    '912217592',    '7223.77',  '12/26/2013'),
('Doctor A',    '912217652',    '2570.24',  '12/31/2013'),
('Doctor A',    '912221613',    '332.51',   '2/28/2015'),
('Doctor A',    '912218592',    '13087',    '3/31/2014'),
('Doctor B',    '912212830',    '260',  '7/28/2012'),
('Doctor B',    '912217148',    '216',  '10/25/2013'),
('Doctor B',    '912213308',    '225',  '8/31/2012'),
('Doctor B',    '912225658',    '1120', '5/18/2016'),
('Doctor B',    '912224030',    '1044.1',   '10/27/2015'),
('Doctor B',    '912224821',    '270',  '2/15/2016'),
('Doctor B',    '912215253',    '1833', '4/12/2013'),
('Doctor B',    '912215226',    '1943', '4/26/2013'),
('Doctor B',    '912224103',    '697.05',   '10/31/2015'),
('Doctor C',    '912226150',    '321.16',   '7/29/2016'),
('Doctor C',    '912226728',    '409.87',   '8/31/2016'),
('Doctor D',    '912227487',    '1579.39',  '11/28/2016'),
('Doctor D',    '912227536',    '689.03',   '11/28/2016'),
('Doctor E',    '912225574',    '252.45',   '4/22/2016'),
('Doctor E',    '912225190',    '443.13',   '3/25/2016'),
('Doctor F',    '912228020',    '5341', '1/31/2017'),
('Doctor F',    '912216621',    '2550', '8/30/2013'),
('Doctor F',    '912224493',    '5908.48',  '12/31/2015'),
('Doctor F',    '912210468',    '5863.19',  '1/31/2012'),
('Doctor F',    '912227606',    '4657', '12/16/2016'),
('Doctor F',    '912212275',    '12202.39', '5/31/2012'),
('Doctor F',    '912213649',    '3640', '10/31/2012'),
('Doctor F',    '912226417',    '2801', '8/12/2016'),
('Doctor F',    '912226633',    '452',  '8/24/2016'),
('Doctor G',    '912212694',    '0',    '7/18/2012'),
('Doctor G',    '912214793',    '11659',    '2/28/2013'),
('Doctor G',    '912224178',    '4749', '11/17/2015'),
('Doctor G',    '912219997',    '16501',    '8/20/2014'),
('Doctor G',    '912222180',    '15872',    '3/31/2015'),
('Doctor G',    '912223090',    '18101',    '7/14/2015'),
('Doctor G',    '912212692',    '8197', '7/18/2012'),
('Doctor G',    '912222048',    '0',    '6/26/2015'),
('Doctor G',    '912223539',    '2429', '8/31/2015'),
('Doctor H',    '912220020',    '12262',    '8/22/2014'),
('Doctor H',    '912223503',    '12377',    '8/31/2015'),
('Doctor H',    '912212365',    '43987.59', '6/25/2012'),
('Doctor H',    '912212363',    '615',  '6/29/2012'),
('Doctor H',    '912214260',    '2661', '12/31/2012'),
('Doctor H',    '912210695',    '199',  '1/28/2012'),
('Doctor H',    '912216444',    '4425', '8/22/2013'),
('Doctor H',    '912216446',    '3475', '8/22/2013'),
('Doctor H',    '912227667',    '12522',    '12/21/2016'),
('Doctor I',    '912216502',    '500',  '8/28/2013'),
('Doctor I',    '912222006',    '4379', '2/28/2015'),
('Doctor I',    '912212492',    '3532', '6/30/2012'),
('Doctor I',    '912212934',    '4625', '8/10/2012'),
('Doctor I',    '912213175',    '5802', '9/28/2012'),
('Doctor I',    '912210537',    '2968.79',  '1/31/2012'),
('Doctor I',    '912225766',    '4365', '5/27/2016'),
('Doctor I',    '912225768',    '4236', '5/27/2016'),
('Doctor I',    '912210536',    '4571', '1/27/2012')


Create Table Sixer
(
    Doctor  varchar(100)
    ,Practice   varchar(100)
    ,AnnRev decimal(10,2)
)
INSERT INTO Sixer (doctor, Practice, AnnRev) VALUES
('Doctor A',    'Practice A',   '150000'),
('Doctor B',    'Practice B',   '200000'),
('Doctor C',    'Practice C',   '150000'),
('Doctor D',    'Practice D',   '150000'),
('Doctor E',    'Practice E',   '200000'),
('Doctor F',    'Practice F',   '150000'),
('Doctor G',    'Practice G',   '75000'),
('Doctor H',    'Practice H',   '90000'),
('Doctor I',    'Practice I',   '80000')


Create Table Last
(
    Doctor  varchar(100)
    ,oIsAcctID  varchar(100)
    ,DOS date
)
Insert Into Last (Doctor, oIsAcctID, dos) VALUES
('Doctor A',    'NS12420924',   '10/10/2014'),
('Doctor A',    'NS12408229',   '8/16/2013'),
('Doctor A',    'NS12423200',   '12/29/2014'),
('Doctor A',    'NS12408173',   '8/15/2013'),
('Doctor A',    'NS12429761',   '10/6/2015'),
('Doctor A',    'NS12414659',   '4/10/2014'),
('Doctor A',    'NS12424319',   '2/9/2015'),
('Doctor A',    'NS12412810',   '2/14/2014'),
('Doctor A',    'NS12409574',   '10/7/2013'),
('Doctor B',    'NS12361225',   '3/3/2010'),
('Doctor B',    'NS12404381',   '4/1/2013'),
('Doctor B',    'NS12385336',   '9/15/2011'),
('Doctor B',    'NS12404310',   '3/28/2013'),
('Doctor B',    'NS12424614',   '2/20/2015'),
('Doctor B',    'NS12378790',   '5/2/2011'),
('Doctor B',    'NS12436716',   '9/12/2016'),
('Doctor B',    'NS12394012',   '4/30/2012'),
('Doctor B',    'NS12433703',   '4/20/2016'),
('Doctor C',    'NS12436322',   '8/22/2016'),
('Doctor C',    'NS12436322',   '8/23/2016'),
('Doctor C',    'NS12435005',   '6/21/2016'),
('Doctor D',    'NS12437886',   '11/15/2016'),
('Doctor D',    'NS12437804',   '11/10/2016'),
('Doctor E',    'NS12433080',   '4/21/2016'),
('Doctor E',    'NS12432817',   '3/7/2016'),
('Doctor E',    'NS12433080',   '3/18/2016'),
('Doctor F',    'NS12370205',   '10/4/2010'),
('Doctor F',    'NS12423205',   '12/29/2014'),
('Doctor F',    'NS12370705',   '10/14/2010'),
('Doctor F',    'NS12415295',   '4/28/2014'),
('Doctor F',    'NS12405405',   '5/7/2013'),
('Doctor F',    'NS12370754',   '10/14/2010'),
('Doctor F',    'NS12419878',   '9/10/2014'),
('Doctor F',    'NS12396650',   '7/25/2012'),
('Doctor F',    'NS12397679',   '8/21/2012'),
('Doctor G',    'NS12392440',   '3/5/2012'),
('Doctor G',    'NS12418439',   '7/30/2014'),
('Doctor G',    'NS12406864',   '6/28/2013'),
('Doctor G',    'NS12431461',   '12/21/2015'),
('Doctor G',    'NS12375850',   '2/25/2011'),
('Doctor G',    'NS12414269',   '3/31/2014'),
('Doctor G',    'NS12417709',   '7/14/2014'),
('Doctor G',    'NS12403820',   '3/14/2013'),
('Doctor G',    'NS12427115',   '6/5/2015')

And this is the query I wrote to try to extract my needed data that throws the error above

DECLARE @Startdate date = '20170101', 
        @Enddate   date = '20170131';

WITH Sixer As
(
    Select
    DISTINCT(Doctor) As Doctor
    ,Practice
    ,AnnRev
    FROM Sixer
)
,First As
(
    Select
    Doctor
    ,ChargedAmt
    FROM dbo.First
    WHERE DOS >= @Startdate
    AND DOS <  DATEADD(DAY,1,@Enddate)
)
,Sec As
(
    Select 
    Doctor
    ,PendingCharges
    FROM Sec
),
Tree As
(
    Select
    Doctor
    ,InsuranceReviewAmt
    FROM Tree
)
,Four As
(
    Select
    Doctor
    ,prevyrbills
    FROM Four
    WHERE CAST(DOS As DATE) BETWEEN CAST(DateAdd(yy, -1, @startdate) As Date) 
                                     AND CAST(DateAdd(yy, -1, @enddate) As Date)
)
,Fiver As
(
    Select 
    Doctor
    ,AcctNum
    ,DOS
    FROM Fiver
    WHERE DOS >= @Startdate
    AND DOS <  DATEADD(DAY,1,@Enddate)
)
,Last As
(
    Select
    Doctor
    ,oIsAcctID
    FROM Last
    WHERE DOS >= @Startdate
    AND DOS <  DATEADD(DAY,1,@Enddate)
)
Select 
DISTINCT(ad.[Doctor]) As [Doctor]
,[Practice Name] = sx.practice
,[Charged Amt] = ROUND(SUM(ISNULL(sx.ChargedAmt,0)),0)
,[Summation] = CAST(ROUND(SUM(ISNULL(sec.PendingCharges,0))+SUM(ISNULL(tr.InsuranceReviewAmt,0)),0) As INT)
,[ABC] = ROUND(SUM(ISNULL(fr.prevyrbills,0)),0)
,[DEF] = ISNULL(sx.AnnRev,0)
,[Count One] = COUNT(fv.AcctNum) 
,[Count Two] = COUNT(ls.oIsAcctID)
FROM AllDocs ad
LEFT JOIN Sixer sx
ON ad.[Doctor] = sx.[Doctor]
LEFT JOIN First fst
ON fst.Doctor = ad.Doctor
LEFT JOIN sec sec
ON sec.Doctor = ad.Doctor
LEFT JOIN Tree tr
ON tr.Doctor = ad.Doctor
LEFT JOIN Four fr
ON ad.Doctor = fr.Doctor
LEFT JOIN Fiver fv
ON ad.Doctor = fv.Doctor
LEFT JOIN Last ls
ON ls.Doctor = ad.Doctor
GROUP BY ad.[Doctor],sx.practice, sx.AnnRev
ORDER BY ad.[Doctor] ASC
  • Why the neg here? I am new and 1st post so I am not sure? – Yohan Greenburg Feb 12 '17 at 2:32
  • Not my down-vote but my guess is that "here's a massive wall of code, it gets this error message" is not always very well-received. – Aaron Bertrand Feb 12 '17 at 2:49
  • @AaronBertrand - ah thank you for letting me know. I will try to make a "cleaner" post next time. – Yohan Greenburg Feb 12 '17 at 2:51
  • @Yohan yeah, we didn't really need all the inserts. – ypercubeᵀᴹ Feb 12 '17 at 2:52
  • 1
    @ypercubeᵀᴹ - will do, thanks to both you and Aaron Bertrand for the great pointers! – Yohan Greenburg Feb 12 '17 at 2:57
7

The error is due to this CTE:

WITH Sixer As
(
    Select
    DISTINCT(Doctor) As Doctor
    ,Practice
    ,AnnRev
    FROM Sixer           -- the error occurs here
)

You have a table named Sixer and a cte named Sixer. So when the query parser reads the FROM Sixer, it considers the cte (and not the table). And this is not allowed - unless it's a recursive CTE (and thus the error).

Solution is simple: change the CTE name to something else:

WITH Six As                  -- CTE name changed
(
    SELECT DISTINCT
        Doctor
       ,Practice
       ,AnnRev
    FROM Sixer
)
---

---
LEFT JOIN Six sx             -- and used here
---

Another solution would be to reference (prefix) the table with the schema name (dbo.Sixer). This way you could keep the same name for the CTE:

WITH Sixer AS
(
    SELECT DISTINCT
        Doctor
       ,Practice
       ,AnnRev
    FROM dbo.Sixer           -- table name prefixed with schema
)
---                          -- no other change

Irrelevant to the error, I removed the parenthesis around Doctor. DISTINCT is not a function. It applies to the whole SELECT list.

  • Wow, I did not realize that was an error. As you can see I did this multiple times in my syntax. Thank you for taking the time to point out my issue. – Yohan Greenburg Feb 12 '17 at 2:44
  • 1
    @Yohan This would also be clearer if you use schema prefix on all object references (see this post). Looking at just the query and not the other code in the question, I really believed you were trying to construct a recursive CTE. – Aaron Bertrand Feb 12 '17 at 2:46
  • Yes, the first error was thrown. If you correct it, another will pop up. You could use something else, too. Reference tables with the schema name (dbo.Sixer) if you want to keep the same name for the CTE. – ypercubeᵀᴹ Feb 12 '17 at 2:46
  • for prefix should i only add the dbo or should I fully qualify with server.database.dbo.table? – Yohan Greenburg Feb 12 '17 at 2:50
  • 3
    @Yohan I would only go to database or server prefix if the query in fact does reference other databases / servers. – Aaron Bertrand Feb 12 '17 at 2:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.