sql - Vague “Syntax error in join operation”-Access 2003

Question

一段时间以来，我使用了这个SQL块，该块已经工作了一个月:

SELECT 
    F.[BRANCH] AS REGION
    , F.[ACCOUNT]
    , F.[EFFECTIVE]
    , F.[POLICY]
    , F.[DESC2]
    , F.[DESC4]
    , F.[GROSS]
    , F.[COMM]
    , F.[GST ON COMM]
    , F.[ENTRYDATE]
    , I.[TYPE] 
FROM (
    (SELECT [POLP - Detailed Debtors Import].* 
     FROM [POLP - Detailed Debtors Import]  
     WHERE [POLP - Detailed Debtors Import].[EFFECTIVE] <= DateValue("30/05/2012")  
        AND [POLP - Detailed Debtors Import].[GROSS] >= 0  
        AND [POLP - Detailed Debtors Import].[DESC4] IN ('RNWL', 'NEWB', 'EP')  
        AND [POLP - Detailed Debtors Import].[DUEYR] = 2012) AS F 
        INNER JOIN (SELECT IPM_ACCTXREF.* FROM IPM_ACCTXREF  
                        WHERE IPM_ACCTXREF.TYPE 
                            IN (SELECT [Evolve - Account Types].[Account Types] 
                                FROM [Evolve - Account Types])
                        ) AS I ON F.[ACCOUNT] = I.[ACCOUNT]
    ) 
    LEFT JOIN (SELECT  [POLP - Detailed Debtors Import].* 
                  FROM [POLP - Detailed Debtors Import]  
                  WHERE [POLP - Detailed Debtors Import].[DESC4] IN ('CS', 'CANC', 'JL')
                  ) AS S ON F.[POLICY] = S.[POLICY] 
WHERE S.[POLICY] IS NULL

然后，我想对DateValue参数进行更改(就像将5更改为6一样简单)。

然后的问题是，我收到了重复三次的“JOIN操作中的语法错误”。单击它们中的每个，查询将显示结果表，但带有“#Name？”填充在每个字段中。

不知道这里发生了什么...

Answer 1

您可能应该重写查询，使其显式有关所需字段，而不仅仅是使用*。

SELECT F.REGION,
       F.ACCOUNT,
       F.EFFECTIVE,
       F.POLICY,
       F.DESC2,
       F.DESC4,
       F.GROSS,
       F.COMM,
       F.[GST ON COMM],
       F.ENTRYDATE,
       I.[TYPE]
FROM   ( (SELECT BRANCH AS REGION,
               ACCOUNT,
               EFFECTIVE,
               POLICY,
               DESC2,
               DESC4,
               GROSS,
               COMM,
               [GST ON COMM],
               ENTRYDATE
        FROM   [POLP - Detailed Debtors Import]
        WHERE  EFFECTIVE<=Datevalue("30/05/2012")
               AND GROSS>=0
               AND DESC4 IN ('RNWL', 'NEWB', 'EP')
               AND DUEYR=2012) AS F
         INNER JOIN (SELECT [TYPE],
                            ACCOUNT
                     FROM   IPM_ACCTXREF
                     WHERE  [TYPE] IN (SELECT [Account Types]
                                       FROM   [Evolve - Account Types])) AS I
           ON F.ACCOUNT=I.ACCOUNT )
       LEFT JOIN (SELECT POLICY
                  FROM   [POLP - Detailed Debtors Import]
                  WHERE  DESC4 IN ('CS', 'CANC', 'JL')) AS S
         ON F.POLICY=S.POLICY
WHERE  S.POLICY IS NULL 

我对 LEFT JOIN的目的有点迷惑，因为 S中的任何数据实际上都没有在主要 SELECT语句中使用。

您还使用名称 TYPE作为字段名，这是一个保留字。