Top Level Union Error With CTE

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',

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.

StackExchange Database Administrators Q#164071, answer score: 9