SQL Server 2008：FROM子句语法错误

My code returns a syntax error on L42 (the from clause). I've looked and looked and to me, the syntax looks correct for the FROM.

我的代码在L42(from子句)上返回语法错误。我看了看,对我来说,语法对于FROM来说是正确的。

The top part of the code creates a temp table 'subproducts'. Then I want to pull data from DB tables & pull relevant details from the new temp table.

代码的顶部创建临时表“子产品”。然后我想从数据库表中提取数据并从新临时表中提取相关详细信息。

Any ideas?

drop table ##subproducts;

SELECT 
  F1.[OrderNo]
  ,F1.[OrderSeqNo]
  ,F1.[OrderLineNo]
  ,sum(F1.[LineCost]) as LineCost
  ,sum(F1.[NetCost]) as NetCost
  ,sum(F1.[OrderNet]) as OrderNet
INTO 
   ##subproducts
FROM 
   [UFPData].[dbo].[SalesStats] F1
WHERE 
   F1.MainProd = 'S'
GROUP BY 
   F1.OrderNo, F1.OrderSeqNo, F1.OrderLineNo;

SELECT * 
FROM SalesStats F1
LEFT OUTER JOIN ##subproducts F2 ON F1.OrderNo = F2.OrderNo 
                                 AND F1.OrderSeqNo = F2.Orderseqno 
                                 AND F1.OrderLineNo = F2.OrderLineNo
WHERE YEAR(InvDate) = 2015
  AND MONTH(InvDate) = 5
  AND CustNo = 100382
  AND MainProd = 'Y';

SELECT 
    ContractInfo.CONTRNO,
    ContractInfo.CONTRDESC,
    repcode, 
    RepName,
    PriceAgreement,
    ordercycle, 
    SalesStats.CustNo,
    Customer.CustName,
    SalesStats.InvDate, 
    SalesStats.InvoiceNo, 
    salesstats.ProdCode,
    Price as ContractPrice,
    subproduct.LineCost as LineCost,
    subproduct.NetCost as NetCost,
    subproduct.OrderNet as OrderNet,
    contractinfo.NETCOST as BidPrice,
    contractinfo.NETCOST* SUM(quantity) as BidCost,
FROM 
    UFPData.dbo.SalesStats 
RIGHT OUTER JOIN
    ##subproducts ON UFPData.dbo.SalesStats.ProdCode = ##subproducts.ProdCode,
    UFPData.dbo.SalesStats 
LEFT OUTER JOIN 
    UFPData.dbo.Customer ON UFPData.dbo.SalesStats.CustNo = UFPData.dbo.Customer.CustNo, UFPData.dbo.SalesStats 
LEFT OUTER JOIN
    Pricing.dbo.InvManCen ON UFPData.dbo.SalesStats.ProdCode = Pricing.dbo.invmancen.UFPCODE, UFPData.dbo.SalesStats 
RIGHT OUTER JOIN
    UFPData.dbo.ContractInfo ON UFPData.dbo.Customer.CustNo = UFPData.dbo.ContractInfo.CUSTNO 
                             AND UFPData.dbo.salesstats.prodcode = UFPData.dbo.contractinfo.prodcode,
WHERE
    invdate BETWEEN '2015-05-01' and '2015-05-31'
    AND TeamCode IN ('tm1','tm2','tm3')
    AND ContractInfo.CONTRNO IN ('1500','1502','1503','1504','1505','1506','701','702','703','705','141',
'712',  '713',  '714',  '715',  '716',  '717',  '718',  '719',  '730',  '731',  '732') or CONTRNO between '3000' and '3049')
--and left(ordercycle,1) <> 'c'
GROUP BY
    ContractInfo.CONTRNO,
    repcode, 
    RepName,
    SalesStats.CustNo,
    Customer.CustName, 
    SalesStats.InvDate,
    salesstats.ProdCode,
    Price,
    contractinfo.NETCOST,
    SalesStats.InvoiceNo,
    InvManCen.PS98,
    ContractInfo.CONTRDESC,
    ordercycle,
    PriceAgreement,

case
when invdate >   ('20' + LEFT(ENDDATE,2)+ '-'+ SUBSTRING(cast(ENDDATE as varchar(6)),3,2)+ '-'+ SUBSTRING(cast(ENDDATE as varchar(6)),5,2)) then 'Expired' else 'Live'  end;

Many thanks in advance

提前谢谢了

2 个解决方案

#1

This doesn't look right to me:

这对我来说不合适:

FROM 
UFPData.dbo.SalesStats right outer join ##subproducts on UFPData.dbo.SalesStats.ProdCode = ##subproducts.ProdCode,
UFPData.dbo.SalesStats left outer join UFPData.dbo.Customer on UFPData.dbo.SalesStats.CustNo = UFPData.dbo.Customer.CustNo,
UFPData.dbo.SalesStats left outer join Pricing.dbo.InvManCen on UFPData.dbo.SalesStats.ProdCode = Pricing.dbo.invmancen.UFPCODE,
UFPData.dbo.SalesStats right outer join UFPData.dbo.ContractInfo on UFPData.dbo.Customer.CustNo = UFPData.dbo.ContractInfo.CUSTNO and UFPData.dbo.salesstats.prodcode = UFPData.dbo.contractinfo.prodcode,

Usually the format is something like this:

通常格式是这样的:

FROM
  TableName
  INNER JOIN SomeOtherTable...
  INNER JOIN AnotherTable...

Note that the first table is mentioned once, not before every join. You also seem to have errant commas after each join, which isn't syntactically correct. I think you want this:

请注意,第一个表被提及一次,而不是在每次连接之前。在每次连接后,您似乎也有错误的逗号,这在语法上是不正确的。我想你想要这个:

FROM 
UFPData.dbo.SalesStats
right outer join ##subproducts on UFPData.dbo.SalesStats.ProdCode = ##subproducts.ProdCode
left outer join UFPData.dbo.Customer on UFPData.dbo.SalesStats.CustNo = UFPData.dbo.Customer.CustNo
left outer join Pricing.dbo.InvManCen on UFPData.dbo.SalesStats.ProdCode = Pricing.dbo.invmancen.UFPCODE
right outer join UFPData.dbo.ContractInfo on UFPData.dbo.Customer.CustNo = UFPData.dbo.ContractInfo.CUSTNO and UFPData.dbo.salesstats.prodcode = UFPData.dbo.contractinfo.prodcode

There could very well be more syntax errors throughout your query. I recommend formatting the code a little more cleanly to more easily find them. For example, contrast the above with something like this:

整个查询中可能会出现更多语法错误。我建议更清晰地格式化代码,以便更容易找到它们。例如,将上述内容与以下内容进行对比:

FROM 
    UFPData.dbo.SalesStats
    RIGHT OUTER JOIN ##subproducts
        ON UFPData.dbo.SalesStats.ProdCode = ##subproducts.ProdCode
    LEFT OUTER JOIN UFPData.dbo.Customer
        ON UFPData.dbo.SalesStats.CustNo = UFPData.dbo.Customer.CustNo
    LEFT OUTER JOIN Pricing.dbo.InvManCen
        ON UFPData.dbo.SalesStats.ProdCode = Pricing.dbo.invmancen.UFPCODE
    RIGHT OUTER JOIN UFPData.dbo.ContractInfo
        ON UFPData.dbo.Customer.CustNo = UFPData.dbo.ContractInfo.CUSTNO
        AND UFPData.dbo.salesstats.prodcode = UFPData.dbo.contractinfo.prodcode

It's a small change, and often a matter of personal preference, but well formatted code brings about a number of advantages. Less horizontal scrolling makes it easier to see the relevant code in a single glance, line breaks for specific keywords and clauses makes it easier to spot errant commas, etc.

这是一个很小的变化,通常是个人偏好的问题,但格式良好的代码带来了许多优势。较少的水平滚动使得更容易一目了然地查看相关代码,特定关键字和子句的换行符可以更容易地发现错误的逗号等。

#2

Here's what I think should be valid syntax for what you've provided, please try it and compare it to the original as there were multiple errors:

以下是我认为应该是您提供的有效语法,请尝试将其与原始文件进行比较,因为存在多个错误:

DROP TABLE ##subproducts;

SELECT  F1.[OrderNo] ,
        F1.[OrderSeqNo] ,
        F1.[OrderLineNo] ,
        SUM(F1.[LineCost]) AS LineCost ,
        SUM(F1.[NetCost]) AS NetCost ,
        SUM(F1.[OrderNet]) AS OrderNet
INTO    ##subproducts
FROM    [UFPData].[dbo].[SalesStats] F1
WHERE   F1.MainProd = 'S'
GROUP BY F1.OrderNo ,
        F1.OrderSeqNo ,
        F1.OrderLineNo;

SELECT  *
FROM    SalesStats F1
        LEFT OUTER JOIN ##subproducts F2 ON F1.OrderNo = F2.OrderNo
                                            AND F1.OrderSeqNo = F2.Orderseqno
                                            AND F1.OrderLineNo = F2.OrderLineNo
WHERE   YEAR(InvDate) = 2015
        AND MONTH(InvDate) = 5
        AND CustNo = 100382
        AND MainProd = 'Y';

SELECT  ContractInfo.CONTRNO ,
        ContractInfo.CONTRDESC ,
        repcode ,
        RepName ,
        PriceAgreement ,
        ordercycle ,
        SalesStats.CustNo ,
        Customer.CustName ,
        SalesStats.InvDate ,
        SalesStats.InvoiceNo ,
        SalesStats.ProdCode ,
        Price AS ContractPrice ,
        subproduct.LineCost AS LineCost ,
        subproduct.NetCost AS NetCost ,
        subproduct.OrderNet AS OrderNet ,
        ContractInfo.NetCost AS BidPrice ,
        ContractInfo.NetCost * SUM(quantity) AS BidCost
FROM    UFPData.dbo.SalesStats
        RIGHT OUTER JOIN ##subproducts ON UFPData.dbo.SalesStats.ProdCode = ##subproducts.ProdCode
        LEFT OUTER JOIN UFPData.dbo.Customer ON UFPData.dbo.SalesStats.CustNo = UFPData.dbo.Customer.CustNo
        LEFT OUTER JOIN Pricing.dbo.InvManCen ON UFPData.dbo.SalesStats.ProdCode = Pricing.dbo.invmancen.UFPCODE
        RIGHT OUTER JOIN UFPData.dbo.ContractInfo ON UFPData.dbo.Customer.CustNo = UFPData.dbo.ContractInfo.CUSTNO
                                                     AND UFPData.dbo.salesstats.prodcode = UFPData.dbo.contractinfo.prodcode
WHERE   invdate BETWEEN '2015-05-01' AND '2015-05-31'
        AND TeamCode IN ( 'tm1', 'tm2', 'tm3' )
        AND (ContractInfo.CONTRNO IN ( '1500', '1502', '1503', '1504', '1505',
                                      '1506', '701', '702', '703', '705',
                                      '141', '712', '713', '714', '715', '716',
                                      '717', '718', '719', '730', '731', '732' )
        OR CONTRNO BETWEEN '3000' AND '3049')
GROUP BY ContractInfo.CONTRNO ,
        repcode ,
        RepName ,
        SalesStats.CustNo ,
        Customer.CustName ,
        SalesStats.InvDate ,
        SalesStats.ProdCode ,
        Price ,
        ContractInfo.NetCost ,
        SalesStats.InvoiceNo ,
        InvManCen.PS98 ,
        ContractInfo.CONTRDESC ,
        ordercycle ,
        PriceAgreement ,
        CASE WHEN invdate > ( '20' + LEFT(ENDDATE, 2) + '-'
                              + SUBSTRING(CAST(ENDDATE AS VARCHAR(6)), 3, 2)
                              + '-' + SUBSTRING(CAST(ENDDATE AS VARCHAR(6)), 5,
                                                2) ) THEN 'Expired'
             ELSE 'Live'
        END;