SQL Server 2008：关键字 'AS' 附近的语法不正确

Question

我对继承的程序有问题。我需要调整查询，但是NOG查询不再起作用。

这是我得到的查询（不要判断我，这不是我的查询:)）

SELECT     
   SUM(DATEDIFF(day, verz1.BeginDatumAW, verz1.EindDatumAW) + 1) AS AantalDagen,    
   verz1.CodeBedrijf, verz1.CodeAfdeling, verz1.CodeSectie,
   (SELECT TOP (1) RTRIM(OmschrijvingSectie) AS Expr1
    FROM PEBSectie
    WHERE (CodeBusiness = verz1.CodeBusiness) AND (CodeAfdeling = '00')) AS BSNS,
   (SELECT TOP (1) RTRIM(OmschrijvingSectie) AS Expr1
    FROM PEBSectie AS PEBSectie_1
    WHERE (CodeBusiness = verz1.CodeBusiness) AND (CodeAfdeling = verz1.CodeAfdeling) AND (CodeGroep = '00')) AS AFD,
   (SELECT TOP (1) RTRIM(OmschrijvingSectie) AS Expr1
    FROM PEBSectie AS PEBSectie_1
    WHERE (CodeBusiness = verz1.CodeBusiness) AND (CodeAfdeling = verz1.CodeAfdeling) AND (CodeGroep = verz1.CodeGroep) AND (CodeSectie = '00')) AS GRP,
   (SELECT TOP (1) RTRIM(OmschrijvingSectie) AS Expr1
    FROM PEBSectie AS PEBSectie_1
    WHERE (CodeBusiness = verz1.CodeBusiness) AND (CodeAfdeling = verz1.CodeAfdeling) AND (CodeGroep = verz1.CodeGroep) AND (CodeSectie = verz1.CodeSectie)) AS SEC
FROM         
    Verzorging AS verz1 
LEFT OUTER JOIN
    AangepastWerk AS aw1 ON verz1.VerzorgingId = aw1.VerzorgingId
WHERE     
    (verz1.EindDatumAW <= @EndDate) AND 
    (verz1.BeginDatumAW >= @StartDate) AND 
    (aw1.Verwijderd = 0) AND               <------------ First statement added
    (aw1.NietErkend = 0 OR aw1.NietErkend IS NULL) AND 
    (DATEDIFF(day, verz1.BeginDatumAW, verz1.EindDatumAW) > 0))
GROUP BY 
     verz1.CodeBusiness, verz1.CodeBedrijf, verz1.CodeAfdeling, verz1.CodeGroep, 
     verz1.CodeSectie, verz1.TypeAWOfWO, verz1.TypeOngeval
HAVING      
     (verz1.TypeAWOfWO = 'A') AND (verz1.TypeOngeval = 'AO') OR
     (verz1.TypeAWOfWO = 'Arbeidsongeval') OR
     (verz1.TypeAWOfWO = 'arbeidsongeval')
UNION ALL
SELECT     SUM(DATEDIFF(day, @StartDate, verz1.EindDatumAW) + 1) AS AantalDagen, verz1.CodeBedrijf, verz1.CodeAfdeling, verz1.CodeSectie,
                      (SELECT     TOP (1) RTRIM(OmschrijvingSectie) AS Expr1
                        FROM          PEBSectie AS PEBSectie_3
                        WHERE      (CodeBusiness = verz1.CodeBusiness) AND (CodeAfdeling = '00')) AS BSNS,
                      (SELECT     TOP (1) RTRIM(OmschrijvingSectie) AS Expr1
                        FROM          PEBSectie AS PEBSectie_1
                        WHERE      (CodeBusiness = verz1.CodeBusiness) AND (CodeAfdeling = verz1.CodeAfdeling) AND (CodeGroep = '00')) AS AFD,
                      (SELECT     TOP (1) RTRIM(OmschrijvingSectie) AS Expr1
                        FROM          PEBSectie AS PEBSectie_1
                        WHERE      (CodeBusiness = verz1.CodeBusiness) AND (CodeAfdeling = verz1.CodeAfdeling) AND (CodeGroep = verz1.CodeGroep) AND 
                                               (CodeSectie = '00')) AS GRP,
                      (SELECT     TOP (1) RTRIM(OmschrijvingSectie) AS Expr1
                        FROM          PEBSectie AS PEBSectie_1
                        WHERE      (CodeBusiness = verz1.CodeBusiness) AND (CodeAfdeling = verz1.CodeAfdeling) AND (CodeGroep = verz1.CodeGroep) AND 
                                               (CodeSectie = verz1.CodeSectie)) AS SEC
FROM         Verzorging AS verz1 LEFT OUTER JOIN
                  AangepastWerk AS aw1 ON verz1.VerzorgingId = aw1.VerzorgingId
WHERE     (verz1.EindDatumAW <= @EndDate) 
AND (verz1.EindDatumAW >= @StartDate) 
AND (verz1.BeginDatumAW < @StartDate) 
AND (aw1.Verwijderd = 0)                   <------------ Second statement added
AND (aw1.NietErkend = 0 OR aw1.NietErkend IS NULL) 
AND (DATEDIFF(day, verz1.BeginDatumAW, verz1.EindDatumAW) > 0)
GROUP BY verz1.CodeBusiness, verz1.CodeBedrijf, verz1.CodeAfdeling, verz1.CodeGroep, verz1.CodeSectie, verz1.TypeAWOfWO, verz1.TypeOngeval
HAVING      (verz1.TypeAWOfWO = 'A') AND (verz1.TypeOngeval = 'AO') OR
                  (verz1.TypeAWOfWO = 'Arbeidsongeval') OR
                  (verz1.TypeAWOfWO = 'arbeidsongeval')

更改：在两个语句中，我添加了以下行：

 AND (aw1.Verwijderd = 0)

添加了此内容后，我一直在以下子征服中遇到问题（对于每个子征值，我都会遇到相同的错误）。

(SELECT     TOP (1) RTRIM(OmschrijvingSectie) AS Expr1
                        FROM          PEBSectie AS PEBSectie_3
                        WHERE      (CodeBusiness = verz1.CodeBusiness) AND (CodeAfdeling = '00')) AS BSNS,

我遇到的错误是：

msg 156，第15级，状态1，第28行
关键字" AS"。

附近的错误语法

我似乎无法弄清楚我做了什么/出了什么问题。我希望一双新鲜的眼睛可能会看到我错过的东西。

NOTE ：仅在 Union All

之后发生错误

我感谢您的所有耐心

与善意

编辑：如果可能：应避免对查询的重大修改。

Answer 1

大家，

感谢你们一直投资于我的问题。一双新鲜的眼睛（甚至一双非IT眼睛；））突然发现了这个问题。第一个查询中有一个括号太多（where语句）

 (DATEDIFF(day, verz1.BeginDatumAW, verz1.EindDatumAW) > 0)) 

最后一个支架不应该在那里。我对错误的错误感到盲目，并且正在寻找第二个查询中的问题。旧查询仍然设法产生有效结果（即使使用无效的语法）仍然困扰着我。查询本身将被重写以避免进一步的问题。

thnx再次在您的所有时间

与善意

Answer 2

您不能为选择表查询别名..

(SELECT TOP (1) RTRIM(OmschrijvingSectie) AS Expr1
 FROM PEBSectie AS PEBSectie_1
 WHERE (CodeBusiness = verz1.CodeBusiness) AND 
       (CodeAfdeling =verz1.CodeAfdeling) AND 
       (CodeGroep = verz1.CodeGroep) AND                                     
       (CodeSectie = verz1.CodeSectie)) **AS SEC** 

在所有表格上都是错误的。