SQL Server Management Studio：关键字'LEFT'附近的语法不正确

Question

我想通过加入来自3个表的信息编写一个复杂的查询。不过，我得到以下错误：

不正确的语法“左”

这里的关键字附近是我的查询：

SELECT 
    DailyOnhand.[Country Code], 
    DailyOnhand.Item, 
    DailyOnhand.[DTP USD], 
    DailyOnhand.[OH Value USD OP], 
    DailyOnhand.[OH Quantity], 
    DailyOnhand.[Onhand Type], 
    DailyOnhand.[FE SSO], 
    DailyOnhand.[Newest Job Number], 
    SO.[SO Number], 
FROM 
    [hce.com].[DATAMART].[dbo].DailyOnhand 
WHERE 
    DailyOnhand.[Pole] IN ('EU') 
LEFT JOIN 
    [hce.com].[DATAMART].[dbo].AllSO SO ON AllPO.Item = SO.[item] 
             AND AllPO.[Newest Job Number] = SO.[Job Number] 
             AND AllPO.[FE SSO] = SO.[FE SSO] 

UNION ALL 

SELECT 
    PO.[Receiving Country Code] as [Country Code], 
    '' AS Item, 
    '' AS [DTP USD], 
    SUM(PO.[Total Price OP USD]) AS [OH Value USD OP], 
    '' AS [OH Quantity], 
    'GIT' AS [Onhand Type], 
    '' AS [FE SSO], 
    '' AS [FE Name], 
    '' AS [Newest Job Number], 
    '' AS [SO Number], 
FROM 
    [hce.com].[DATAMART].[dbo].AllPO as PO 
WHERE 
    (1=1) 
    AND PO.[Linked SO Org] LIKE ('[ARUM][0-9][0-9]') 
    AND PO.[Receiving Org] LIKE ('[R][0-9][0-9]') 
    AND ((PO.[Receiving Org] NOT IN ('R34') AND CAST((GetDate()-PO.[Creation Date]) AS INT)<30) 
    OR (PO.[Receiving Org] IN ('R34') AND CAST((GetDate()-PO.[Creation Date]) AS INT)<45)) 
    AND PO.[DW CLOSED CODE] = 'Open' 
    AND PO.[Linked SO Intransit Qty]>0 
GROUP BY 
    PO.[Receiving Org], PO.[Receiving Country Name], PO.[Receiving Country Code] 

而且，如果我的地方移到以下部分否则像最后FROM子句的后面，我得到的错误

T他多部分组成的标识符不能开往 “SO.SO号”， “AllPO.Item”， “AllPO.Newest作业号”， “AllPO.FE SSO”

LEFT JOIN 
    [hce.com].[DATAMART].[dbo].AllSO SO ON AllPO.Item = SO.[item] 
             AND AllPO.[Newest Job Number] = SO.[Job Number] 
             AND AllPO.[FE SSO] = SO.[FE SSO] 

Answer 1

的LEFT JOIN走后FROM。正如其他人已经注意到尾随的逗号已被删除。

像下面一样。

SELECT 
     DailyOnhand.[Country Code], 
     DailyOnhand.Item, 
     DailyOnhand.[DTP USD], 
     DailyOnhand.[OH Value USD OP], 
     DailyOnhand.[OH Quantity], 
     DailyOnhand.[Onhand Type], 
     DailyOnhand.[FE SSO], 
     DailyOnhand.[Newest Job Number], 
     SO.[SO Number] 
    FROM 
     [hce.com].[DATAMART].[dbo].DailyOnhand LEFT JOIN 
     [hce.com].[DATAMART].[dbo].AllSO SO ON AllPO.Item = SO.[item] 
              AND AllPO.[Newest Job Number] = SO.[Job Number] 
              AND AllPO.[FE SSO] = SO.[FE SSO] 
    WHERE 
     DailyOnhand.[Pole] IN ('EU') 

UNION ALL 

SELECT 
    PO.[Receiving Country Code] as [Country Code], 
    '' AS Item, 
    '' AS [DTP USD], 
    SUM(PO.[Total Price OP USD]) AS [OH Value USD OP], 
    '' AS [OH Quantity], 
    'GIT' AS [Onhand Type], 
    '' AS [FE SSO], 
    '' AS [FE Name], 
    '' AS [Newest Job Number], 
    '' AS [SO Number] 
FROM 
    [hce.com].[DATAMART].[dbo].AllPO as PO 
WHERE 
    (1=1) 
    AND PO.[Linked SO Org] LIKE ('[ARUM][0-9][0-9]') 
    AND PO.[Receiving Org] LIKE ('[R][0-9][0-9]') 
    AND ((PO.[Receiving Org] NOT IN ('R34') AND CAST((GetDate()-PO.[Creation Date]) AS INT)<30) 
    OR (PO.[Receiving Org] IN ('R34') AND CAST((GetDate()-PO.[Creation Date]) AS INT)<45)) 
    AND PO.[DW CLOSED CODE] = 'Open' 
    AND PO.[Linked SO Intransit Qty]>0 
GROUP BY 
    PO.[Receiving Org], PO.[Receiving Country Name], PO.[Receiving Country Code] 

Answer 2

以下是对您的查询的一些更正。有多个语法问题。

SELECT 
DailyOnhand.[Country Code], 
DailyOnhand.Item, 
DailyOnhand.[DTP USD], 
DailyOnhand.[OH Value USD OP], 
DailyOnhand.[OH Quantity], 
DailyOnhand.[Onhand Type], 
DailyOnhand.[FE SSO], 
DailyOnhand.[Newest Job Number], 
SO.[SO Number]              --removed comma 

FROM [hce.com].[DATAMART].[dbo].DailyOnhand 


LEFT JOIN [hce.com].[DATAMART].[dbo].AllSO SO 
        ON AllPO.Item = SO.[item] 
        AND AllPO.[Newest Job Number] = SO.[Job Number] 
        AND AllPO.[FE SSO] = SO.[FE SSO] 
WHERE DailyOnhand.[Pole] In ('EU')         --moved this 

UNION ALL 
    SELECT 
    PO.[Receiving Country Code] as [Country Code], 
    '' AS Item, 
    '' AS [DTP USD], 
    SUM(PO.[Total Price OP USD]) AS [OH Value USD OP], 
    '' AS [OH Quantity], 
    'GIT' AS [Onhand Type], 
    '' AS [FE SSO], 
    '' AS [FE Name], 
    '' AS [Newest Job Number], 
    '' AS [SO Number]            --removed comma 

    FROM [hce.com].[DATAMART].[dbo].AllPO as PO 

    WHERE 
    (1=1)               --this is pointless 
    AND PO.[Linked SO Org] LIKE ('[ARUM][0-9][0-9]') 
    AND PO.[Receiving Org] LIKE ('[R][0-9][0-9]') 
    AND ((PO.[Receiving Org] NOT IN ('R34') AND CAST((GetDate()-PO.[Creation Date]) AS INT)<30) --No need for IN since you use a single value 
    OR (PO.[Receiving Org] IN ('R34') AND CAST((GetDate()-PO.[Creation Date]) AS INT)<45)) --No need for IN since you use a single value 
    AND PO.[DW CLOSED CODE] = 'Open' 
    AND PO.[Linked SO Intransit Qty]>0 

    GROUP BY PO.[Receiving Org],PO.[Receiving Country Name],PO.[Receiving Country Code] --you probably only need [Receiving Country Code]