Ora - 00933 Confusion with Inner Join and "As"

ORA - 00933 confusion with inner join and as

Just remove the AS keyword

select 
   P.carrier_id, 
   O.order_id, 
   O.aircraft_id, 
   O.quantity
from 
   orderline O
inner join purchaseorder P
   on O.order_id = P.carrier_id;

ORACLE sql error - ORA-00933 - executing join operation - DBeaver

You have to remove AS when defining alias of table/view:

SELECT l.id AS id
FROM s.process l 
JOIN s.item r ON l.id = r.id;

getting error in oracle 009933 sql command not properly ended

remove as from alias

select o.MFR, o.PRODUCT, c.company, p.PRICE 
from ORDERS  o 
inner join customers  c on c.CUST_NUM = o.CUST
inner join PRODUCTS  p On p.MFR_ID = o.MFR 
where o.CUST in ('2111','2112','2105','2119') and o.AMOUNT < '3000' 
order by c.company DESC;

oracle does not supports in the FROM it support as on select column

ORA-00933: SQL command not properly ended in oracle database

Table aliases in Oracle don't have AS (columns can, but don't have to).

Therefore:

No :   ) as temp 
Yes:   ) temp

---

As of ORA-00942: temp isn't accessible in a subquery. But, if you use it (the temp) as a CTE or an inline view (so that it is a source readable by the whole query), then it should be OK. Something like this:

with temp as
  (select rating, avg(age) as avgage
   from sailors
   group by rating
  )
select *
from temp a
where a.avgage = (select min(b.avgage)
                  from temp b
                 );

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

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 wrong with my oracle select query below?

Oracle does not allow as in table level alias. It's allowed for column and subquery, that's why you don't get the error for the second example.

Fix it by removing as like below:

select * from cisa xxxx 

command not properly ended with multiple subqueries

Like this:

SELECT s_fname
FROM (SELECT s_fname from student) s_n
WHERE s_fname like 'Youss%';

Or try it this way:

with (select s_fname from student) as s_n
select s_fname from s_n
where s_fname like 'Youss%';

That said, other than as an exercise, there's no practical reason to use a subquery here. Better just to say this:

select s_fname 
from student 
where s_fname like 'Youss%';

---

Related Topics