CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloperora-00933
StephenT
StephenT

Reputation: 495

Dividing 2 SELECT statements - 'SQL command not properly ended' error

I'm getting the ORA-00933 error referenced in the subject line for the following statement:

 select
 (select count(name) as PLIs
 from (select
   a.name,
   avg(b.list_price) as list_price
 from 
   crm.prod_int a, crm.price_list_item b
 where 
   a.row_id = b.product_id
   and a.x_sales_code_3 <> '999'
   and a.status_cd not like 'EOL%'
   and a.status_cd not like 'Not%'
   and a.x_sap_material_code is not null
 group by a.name)
 where list_price = 0)
 /
 (select count(name) as PLIs
 from (select
   a.name,
   avg(b.list_price) as list_price
 from 
   crm.prod_int a, crm.price_list_item b
 where 
   a.row_id = b.product_id
   and a.x_sales_code_3 <> '999'
   and a.status_cd not like 'EOL%'
   and a.status_cd not like 'Not%'
   and a.x_sap_material_code is not null
 group by a.name))
 as result from dual;

I've tried removing the aliases as suggested solution in other posts but that didn't change the problem. Any ideas? Thanks.

Upvotes: 0

Views: 1210

Answers (3)

mustaccio
mustaccio

Reputation: 19011

This does not directly answer your question, but I think the query can be simplified:

select case PLIs when 0 then -1 else PLIs_noprice / PLIs end from (
 select 
  count(name) as PLIs,
  count(case list_price when 0 then 1 end) as PLIs_noprice
 from (
  .... your innermost subselect, up to "group by" goes here ...
 )
)

Somehow I can't paste your actual subselect code here, getting "Error submitting your post"... Not tested, as I don't have your tables.

Upvotes: 0

Andomar
Andomar

Reputation: 238296

Answer is wrong, see comment by @Ben

Sub-queries to not have to be named... only if they're directly referenced, i.e. if there's more than one column with the same name in the full query

Subqueries have to be named. Consider changing:

from (select
      ...
      group by a.name)

To:

from (select
      ...
      group by a.name) SubQueryAlias

Upvotes: 0

mustaccio
mustaccio

Reputation: 19011

If you're running this in SQLPlus, it is possible that it misinterprets the division operator in the first column for the statement terminator character. Other tools may also be susceptible. Try moving the division operator, e.g. where list_price = 0) \

Upvotes: 1

Related Questions