ORA-00933:SQL命令未在带有联接的子查询中正确结束

Question

我上次使用Oracle SQL已经有一段时间了，希望有人能告诉我为什么我在上得到933

   SELECT TRIM(A.ACCOUNTNUMBER) AS INDBDebnmbr
, TRIM(A.VOUCHER) AS INinvoicenmbr
, A.DATE_ AS INinvoiceDate
, A.DUEDATE AS INinvoiceDueDate
, A.TXT AS INDescription
, A.EXCHANGECODE AS INCurrencyCode
, subq.AMOUNTMST AS INOriginalamount
, subq.SETTLEAMOUNTMST AS INpaidAmount
, subq.OPENAMOUNT AS INOpenAmount
FROM (
  SELECT DEBTRANS.VOUCHER AS VOUCHER, SUM(DEBTRANS.AMOUNTMST) AS AMOUNTMST
  , SUM(DEBTRANS.SETTLEAMOUNTMST) AS SETTLEAMOUNTMST
  , SUM(DEBTRANS.AMOUNTMST - DEBTRANS.SETTLEAMOUNTMST) AS OPENAMOUNT
  FROM XAL_SUPERVISOR.DEBTRANS DEBTRANS 
  WHERE DEBTRANS.OPEN = 1 AND
  DEBTRANS.TRANSTYPE <> 9 AND
  (DEBTRANS.AMOUNTMST - DEBTRANS.SETTLEAMOUNTMST) <> 0 AND
  DEBTRANS.DATASET = 'FIK'
  GROUP BY DEBTRANS.VOUCHER) subq INNER JOIN DEBTRANS A ON A.VOUCHER = subq.VOUCHER

在SQL Plus中提前感谢

Michael

Answer 1

您关于使用8i的评论解释了这一点。ANSI’92 Join语法直到9i才在Oracle中实现。

您需要修改您的查询：

       SELECT TRIM(A.ACCOUNTNUMBER) AS INDBDebnmbr
    , TRIM(A.VOUCHER) AS INinvoicenmbr
    , A.DATE_ AS INinvoiceDate
    , A.DUEDATE AS INinvoiceDueDate
    , A.TXT AS INDescription
    , A.EXCHANGECODE AS INCurrencyCode
    , subq.AMOUNTMST AS INOriginalamount
    , subq.SETTLEAMOUNTMST AS INpaidAmount
    , subq.OPENAMOUNT AS INOpenAmount
    FROM (
      SELECT DEBTRANS.VOUCHER AS VOUCHER, SUM(DEBTRANS.AMOUNTMST) AS AMOUNTMST
      , SUM(DEBTRANS.SETTLEAMOUNTMST) AS SETTLEAMOUNTMST
      , SUM(DEBTRANS.AMOUNTMST - DEBTRANS.SETTLEAMOUNTMST) AS OPENAMOUNT
      FROM XAL_SUPERVISOR.DEBTRANS DEBTRANS 
      WHERE DEBTRANS.OPEN = 1 AND
      DEBTRANS.TRANSTYPE <> 9 AND
      (DEBTRANS.AMOUNTMST - DEBTRANS.SETTLEAMOUNTMST) <> 0 AND
      DEBTRANS.DATASET = 'FIK'
      GROUP BY DEBTRANS.VOUCHER) subq,
      DEBTRANS A
 WHERE A.VOUCHER = subq.VOUCHER;

Answer 2

您要加入的DEBTRANS是什么，它是XAL_SUPERVISOR.DEBTRANS的另一个实例吗？如果是这样的话，请不要在子查询中使用DEBTRANS作为别名，这会令人困惑。将其更改为其他内容并重试，例如

 SELECT TRIM(A.ACCOUNTNUMBER) AS INDBDebnmbr
, TRIM(A.VOUCHER) AS INinvoicenmbr
, A.DATE_ AS INinvoiceDate
, A.DUEDATE AS INinvoiceDueDate
, A.TXT AS INDescription
, A.EXCHANGECODE AS INCurrencyCode
, subq.AMOUNTMST AS INOriginalamount
, subq.SETTLEAMOUNTMST AS INpaidAmount
, subq.OPENAMOUNT AS INOpenAmount
FROM (
  SELECT dt.VOUCHER AS VOUCHER
  , SUM(dt.AMOUNTMST) AS AMOUNTMST
  , SUM(dt.SETTLEAMOUNTMST) AS SETTLEAMOUNTMST
  , SUM(dt.AMOUNTMST - dt.SETTLEAMOUNTMST) AS OPENAMOUNT
  FROM XAL_SUPERVISOR.DEBTRANS dt
  WHERE dt.OPEN = 1 AND
  dt.TRANSTYPE <> 9 AND
  (dt.AMOUNTMST - dt.SETTLEAMOUNTMST) <> 0 AND
  dt.DATASET = 'FIK'
  GROUP BY dt.VOUCHER) subq INNER JOIN DEBTRANS A ON A.VOUCHER = subq.VOUCHER

Answer 3

代替的最后一行

A.VOUCHER=subq。凭证

使用

A.VOUCHER=subq。INinvoicenmbr