CODEWITHSUNDEEP
sqloracle-database
Jui_
Jui_

Reputation: 13

ORA-00936: missing expression, although there seems to be no missing expression (SQL)

I'm attempting to calculate the ratio of two sums, one of which has a where clause, so I decided to utilize subqueries:

select round(100.0*credit.luotot/all.kaikki) as CPROS
from 
(select sum(ordered.totalprice) as kaikki from ordered) all,
(select sum(ordered.totalprice) as luotot from ordered where ordered.paymentby = 'credit') credit

However, running this query throws

ORA-00936: missing expression

and I don't understand why.

Upvotes: 1

Views: 558

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

You cannot use ALL as an ALIAS that way. Change it to something else :

SELECT round(100.0*credit.luotot/total.kaikki) AS cpros 
FROM   ( 
              SELECT SUM(ORDERED.totalprice) AS kaikki 
              FROM   ordered) total, 
       ( 
              SELECT SUM(ordered.totalprice) AS luotot 
              FROM   ORDERED 
              WHERE  ordered.paymentby = 'credit') credit
/

ALL is a reserved word in Oracle. It is a comparison condition is used to compare a value to a list or subquery.

Also, you should make use of subquery factoring i.e. the WITH clause, rather than redefining the subquery multiple times. Read http://www.oracle-base.com/articles/misc/with-clause.php

Upvotes: 1

Mureinik
Mureinik

Reputation: 311778

all is a reserved word in . You should use a name that isn't, such as all_ordered:

select round(100.0*credit.luotot/all_ordered.kaikki) as CPROS
from 
(select sum(ordered.totalprice) as kaikki from ordered) all_ordered,
(select sum(ordered.totalprice) as luotot from ordered where ordered.paymentby = 'credit') credit

EDIT:
BTW, you can achieve the desired result with a case expression so you won't need to use subqueries:

SELECT  ROUND(100.0 * 
              SUM(totalprice) / 
              SUM(CASE paymentby 
                  WHEN 'credit' THEN totalprice 
                  ELSE 0 END) as CPROS
FROM    ordered

Upvotes: 3

Related Questions