SQL-不支持联接表达式 [英] SQL - Join Expression Not Supported

问题描述

我是新手，正在使用Access并尝试更改此查询:

I am new using Access and trying to change this query:

SELECT 
   DateDiff("d", [Invoice]![TxnDate],[ReceivePaymentLine]![TxnDate]) AS ActualPaymentDays, 
   IIF ([ActualPaymentDays] < 90, 0.10, IIF ([ActualPaymentDays] < 120, 0.09, IID ([ActualPaymentDays] , 365, 0.05, 0))) AS PayPerValue
FROM 
   ReceivePaymentLine 
INNER JOIN 
   Invoice ON ReceivePaymentLine.AppliedToTxnTxnID = Invoice.TxnID

我制作"ActualPaymentDaysRate"表:

I make "ActualPaymentDaysRate" table:

ActualPaymentDay1
PayPerValue1
ActualPaymentDay2
PayPerValue2
ActualPaymentDay3
PayPerValue3

然后将上面的查询更改为以下查询:

And I change the query above to be this query:

SELECT 
    DateDiff("d",[Invoice]![TxnDate],[ReceivePaymentLine]![TxnDate]) AS ActualPaymentDays, 
    IIF ([ActualPaymentDays] < [ActualPaymentDaysRate.ActualPaymentDay1],[ActualPaymentDaysRate.PayPerValue1], IIF ([ActualPaymentDays] < [ActualPaymentDaysRate.ActualPaymentDay2], [ActualPaymentDaysRate.PayPerValue2], IIF ([ActualPaymentDays] < [ActualPaymentDaysRate.ActualPaymentDay3],[ActualPaymentDaysRate.PayPerValue3], 0))) AS PayPerValue
FROM 
    ActualPaymentDaysRate, ReceivePaymentLine 
INNER JOIN 
    Invoice ON ReceivePaymentLine.AppliedToTxnTxnID = Invoice.TxnID

它显示错误不支持JOIN表达式".

It shows error "JOIN expression not supported".

推荐答案

您做错了的是同时使用 ANSI SQL-89 语法和 ANSI SQL-92 语法参加

What you are doing wrong is using both ANSI SQL-89 Syntax and ANSI SQL-92 Syntax for JOIN

INNER JOIN的旧语法是 ANSI SQL-89 ，其中您要指定两个表名，用逗号分隔，这是您在查询中所做的，像这样

The old syntax for INNER JOIN which is ANSI SQL-89 is in which you specify both table names seprated by comma which you done in your query like this

SELECT <column list>
FROM ActualPaymentDaysRate, ReceivePaymentLine 

在 ANSI SQL-92 语法中，您使用JOIN关键字并使用ON指定条件，就像

The ANSI SQL-92 syntax is in which you use JOIN keyword and specify condition using ON which you have done like

 INNER JOIN Invoice ON ReceivePaymentLine.AppliedToTxnTxnID = Invoice.TxnID

但是您不能在同一查询中同时使用它们. 在查询中，您有三个表，因此使用三个INNER JOIN链接表.

BUT you cannot use them at same time in same query. In your query you have three tables and hence use three INNER JOIN to link the tables.

不建议使用ANSI SQL-89语法，因为如果您忘记了连接条件，不会产生错误，而是在返回所有行的表之间应用笛卡尔积.

Using ANSI SQL-89 syntax is not recommended becuse if you forget about joining condition , no error is generated , instead a cartesian product is applied between the tables returning all the rows.

SELECT <column list>
FROM ActualPaymentDaysRate, ReceivePaymentLine 
--  WHERE <condition> excluded , NOT a INNER JOIN anymore but still no error

但是，如果您忘记了SQL-92语法中的条件，则会立即生成错误.

But if you forget condition in SQL-92 syntax , immediately error is generated.

这篇关于SQL-不支持联接表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！