Sql-Server

CTE 的頂級聯合錯誤

  • February 12, 2017

我正在嘗試編寫一個 CTE 查詢，這對我來說可能是最高級的查詢。（使用 SQL 已經 3 週了）。當我嘗試執行此查詢時，出現錯誤

消息 252，級別 16，狀態 1，第 4 行遞歸公用表表達式“Sixer”不包含頂級 UNION ALL 運算符。

所以這裡是瘦的 - 我包括 DDL（這是巨大的）並且還指出 table Fiver 和 table first 看起來相同，但它們是不同的，因為 first 可能包含在 Fiver 中不存在的 acctNum ，反之亦然。現在這裡是 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')

這是我寫的查詢，試圖提取我需要的數據，拋出上面的錯誤

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

錯誤是由於此 CTE：

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

您有一個名為的表Sixer和一個名為的 cte Sixer。因此，當查詢解析器讀取 時FROM Sixer，它會考慮 cte（而不是表）。這是不允許的 - 除非它是遞歸 CTE（因此是錯誤）。

解決方案很簡單：將 CTE 名稱更改為其他名稱：

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

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

另一種解決方案是使用模式名稱 ( ) 引用（前綴）表dbo.Sixer。這樣您就可以為 CTE 保留相同的名稱：

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

與錯誤無關，我刪除了Doctor. DISTINCT不是函式。它適用於整個SELECT列表。

引用自：https://dba.stackexchange.com/questions/164071

相關問答

Sql-Server

根據來自 1 列的值選擇行是否相同？

  • September 15, 2020
檢視問答
Sql-Server

加快查詢速度的方法

  • May 12, 2020
檢視問答
Sql-Server

在更新語句中使用 SUM()

  • June 27, 2017
檢視問答
Sql-Server

執行 DELETE 儲存過程，但發現 WHILE 循環中的性能隨著迭代次數的增加而下降

  • March 23, 2022
檢視問答
Sql-Server

對象“SPROC”、數據庫“DATABASE”、模式“dbo”的 EXECUTE 權限被拒絕

  • March 10, 2022
檢視問答
Sql-Server

檢測兩個時間段是否重疊

  • February 25, 2022
檢視問答