ORA-00933: SQL command not properly ended in subquery with join

It's been a while for me since the last time I did Oracle SQL, hope someone can tell me why I get a 933 on:

   SELECT TRIM(A.ACCOUNTNUMBER) AS INDBDebnmbr
, TRIM(A.VOUCHER) AS INinvoicenmbr
, A.DATE_ AS INinvoiceDate
, A.DUEDATE AS INinvoiceDueDate
, A.TXT AS INDescription
, A.EXCH开发者_如何学JAVAANGECODE 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

In SQL Plus Thanks in advance,

Michael

Your comment about using 8i explains it. The ANSI '92 Join syntax was not implemented in Oracle until 9i.

You will need to modify your query:

       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;

What is the DEBTRANS you're joining to, is it another instance of XAL_SUPERVISOR.DEBTRANS? If so, please don't use DEBTRANS as an alias in the subquery, it is confusing. Change it to something else and try again, e.g.

 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

Instead of the last line of

INNER JOIN DEBTRANS A ON A.VOUCHER = subq.VOUCHER

use

INNER JOIN DEBTRANS A ON A.VOUCHER = subq.INinvoicenmbr