ORA-00936: expression absente

Question

Can you help me know what maybe the error in my sql?ORA-00936: I'm having missing expressionerror but I cannot find the error.

   select p.nompr
from produit p
where p.idpr=(select o.idpr 
           from objet o,enchere e
           where o.idobj=e.idobj
           group by o.idpr
           having(count(*)=select Max(count (o1.idpr)) 
                          from objet o1,enchere e1 
                          where o1.idobj=e1.idobj
                          group by o1.idpr) );

I changed the code but some problem "ORA-00936: expression absente " — user3093583, Oct 09 '15 at 00:54
In future please explain the business rule you're trying to implement. You should not expect us to guess what you want by reverse engineering your code. — APC, Oct 09 '15 at 07:08

score 3 · Answer 1 · answered Oct 09 '15 at 01:14

3

I have reconstructed your query. I believe what you're trying to achieve here is to get p.nompr with the most number of occurences/count:

select p.nompr
from produit p
where p.idpr in(
   select idpr_alias from (SELECT count (o1.idpr) CNT, o1.idpr idpr_alias 
                                from objet o1,enchere e1 
                                 where o1.idobj=e1.idobj
                           group by o1.idpr)
    where CNT=(select max(count(o1.idpr)) from objet o1,enchere e1 
                      where o1.idobj=e1.idobj
                      group by o1.idpr))

answered Oct 09 '15 at 01:14

Vance

897
5
9

thans it work ,i don understand what the problem is ? – user3093583 Oct 09 '15 at 01:28
its the query in your HAVING clause that is causing you error. – brenners1302 Oct 09 '15 at 01:35
1

if you have noticed, I have used `IN` instead of `=` in your main condition. The most probable reason why you're getting an error before is that your subqueries that retrieves the `p.nompr` with `max` count could be returning more than one record. You can't compare a column to a query that returns more than one value with the `=` operator; use `IN` instead. You must also start the habit of using aliases on your columns. One more thing, if you're running a complex query, test your subqueries first before running the whole query, that will help you a lot when debugging :) – Vance Oct 09 '15 at 01:37

score -1 · Answer 2 · answered Oct 09 '15 at 01:03

-1

Does this fragment:

select Max(count (o1.idpr)) 
                          from objet o1,enchere e1 
                          where o1.idobj=e1.idobj
                          group by o1.idpr

run by itself? I suspect you can't use max() and count() together like that.

answered Oct 09 '15 at 01:03

Turophile

3,367
1
13
21

you can use max and count together. You can try running this query if you have the omnipresent emp table ;) `select max(count(deptno)) from emp group by deptno` – Vance Oct 09 '15 at 01:09
when I run only this fragment it wok, i don understand what the problem is ? – user3093583 Oct 09 '15 at 01:27
Ok, that's not the problem then. I thought it was worth checking. I don't have an oracle db handy. – Turophile Oct 09 '15 at 03:24

ORA-00936: expression absente

2 Answers2