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

Question

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.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

In SQL Plus Thanks in advance,

Michael

is there more to this query? i've just tried similar one and it works fine. — Ruslan, Jun 15 '11 at 13:23
Is it possible that the `OPEN` in `WHERE DEBTRANS.OPEN ...` is a keyword? — ypercubeᵀᴹ, Jun 15 '11 at 13:46
don't think so, SELECT subq.AMOUNTMST AS INOriginalamount , subq.SETTLEAMOUNTMST AS INpaidAmount , subq.OPENAMOUNT AS INOpenAmount FROM ( SELECT DEBTRANS.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 / runs fine — Mike Dole, Jun 15 '11 at 13:53
This is why it's important to always specify the environment in questions - Oracle version, OS and version, etc. — DCookie, Jun 15 '11 at 14:51

DCookie · Accepted Answer · 2011-06-15T14:55:30.167

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;

Thanks DCookie, almost there.. still one 933, could it be because of 'DEBTRANS A WHERE A.VOUCHER = subq.VOUCHER'? — Mike Dole, Jun 15 '11 at 14:51

Ruslan · Answer 2 · 2011-06-15T13:55:06.310

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

score 0 · Answer 3 · answered Jun 15 '11 at 13:25

0

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

answered Jun 15 '11 at 13:25

Lost in Alabama

1,653
10
16

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

3 Answers3

Linked