How-To Create an INNER JOIN and LEFT JOIN in Oracle 8i

Question

I am trying to create a Oracle 8i QUERY with a combination of Inner JOIns with multiple conditions as well as Left JOin with multiple conditions. However, I keep getting SQL Command not properly ended. My experience is primarily with T-SQl so apologies in advance if this is obvious..

SELECT TRANS.CUSTOMER_NUMBER AS "AR CUST", CUST.CUSTOMER_NUMBER AS 
"CUSTNUM", CUSTA.FVE_NUMBER AS "FVE"
FROM FINANCE.AR_TRANSACTIONS  TRANS
JOIN FINANCE.CUSTOMERS CUST
ON (TRANS.CUSTOMER_NUM = CUST.CUSTOMER_NUM
AND TRANS.COMPANY_NUM = CUST.COMPANY_NUM)
LEFT JOIN FINANCE.CUSTOMER_ADDRESSES CUSTA
ON (TRANS.FVE_NUMBER = CUSTA.FVE_NUMBER
AND TRANS.COMANY_NUM = CUSTA.CUSTOMER_NUM )
WHERE ROWNUM < 5;

Possible duplicate of [ORA-00933: SQL command not properly ended in subquery with join](https://stackoverflow.com/questions/6358057/ora-00933-sql-command-not-properly-ended-in-subquery-with-join) — xQbert, Dec 14 '18 at 17:35

xQbert · Accepted Answer · 2018-12-14T17:36:33.563

8i doesn't support the ANSI '92 join syntax left, right, inner, full outer, cross join syntax you have to use the (+) notation...

something like...

SELECT TRANS.CUSTOMER_NUMBER AS "AR CUST"
     , CUST.CUSTOMER_NUMBER AS "CUSTNUM"
     , CUSTA.FVE_NUMBER AS "FVE"
FROM FINANCE.AR_TRANSACTIONS  TRANS
   , FINANCE.CUSTOMERS CUST
   , FINANCE.CUSTOMER_ADDRESSES CUSTA
WHERE TRANS.CUSTOMER_NUM = CUST.CUSTOMER_NUM
  AND TRANS.COMPANY_NUM = CUST.COMPANY_NUM
  AND TRANS.FVE_NUMBER = CUSTA.FVE_NUMBER(+)
  AND TRANS.COMANY_NUM = CUSTA.CUSTOMER_NUM(+)
  AND ROWNUM < 5

How-To Create an INNER JOIN and LEFT JOIN in Oracle 8i

1 Answers1