Re: query trigger

Originally posted by Drukqs
> Hello
>
> I have here a query to show the computers that are sold most. Now
> I like to
> use this into a form. I use oracle developper form builder 6i.
> Here is the query:
>
> SELECT pcnr, sum(number) AS n
> FROM orders
> GROUP BY pcnr
> ORDER BY n desc;
>
> This works. But if I put it in a when-button-pressed trigger I get an
> error.
> This is the query in the trigger:
>
> SELECT pcnr, sum(number) AS n
> into :testblock.pc, :testblock.number
> FROM orders
> GROUP BY pcnr
> ORDER BY n desc;
>
> When I push the button I get this error:
> FRM-40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception
> ORA-00904
>
> Can someone give me some advice?
> Thanks

Forms always lags behind with improvements to SQL. The ability to refer to column aliases in the ORDER BY clause is not supported by your version of Forms. Do this instead:

SELECT pcnr, sum(number) AS n
into :testblock.pc, :testblock.number
FROM orders
GROUP BY pcnr
ORDER BY sum(number) desc;

Your next problem will be that you are sure to raise a TOO_MANY_ROWS exception, since INTO can only be used for single-row queries, whereas the ORDER BY clause suggests you expect to get more than 1 row back!

--
Posted via http://dbforums.com