Link to home
Start Free TrialLog in
Avatar of NiceMan331
NiceMan331

asked on

Select Sum Items Of A Cursor

hi
if i have in the form , cursor declared like this
Cursor c_ent IS
select unit,sum(PRICE) Gross,sum(DISCPRIC) Net,sum(PRICE) - sum(DISCPRIC) Discount 
from cr_gnditem 
where 
TO_date(dob, 'dd-mm-yyyy') >= to_date(:BASIC_BLOCK.D_from, 'dd-mm-yyyy')
and TO_date(dob, 'dd-mm-yyyy') <= to_date(:BASIC_BLOCK.D_to, 'dd-mm-yyyy')
group by unit
order by sum(PRICE) desc ;

Open in new window


then in the body of the trigger , i want to use for example sum(sum(price)) ,
should any way to bring it instead of repeating the code
 select sum(price) from ??????????
Avatar of Helena Marková
Helena Marková
Flag of Slovakia image

Maybe I don't understand it (?):
in your cursor sum(PRICE) is presented as sum(PRICE) Gross for each unit.
Avatar of NiceMan331
NiceMan331

ASKER

the cursor present sup(price) for each unit , correct ?
now i want to use sum(price) for all units , like this
select sum(PRICE)
from cr_gnditem
where
TO_date(dob, 'dd-mm-yyyy') >= to_date(:BASIC_BLOCK.D_from, 'dd-mm-yyyy')
and TO_date(dob, 'dd-mm-yyyy') <= to_date(:BASIC_BLOCK.D_to, 'dd-mm-yyyy')
SOLUTION
Avatar of Helena Marková
Helena Marková
Flag of Slovakia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
i thought to reduce time of calculation , if it is possible not to repeat the code
if it is possible to use sum from the same 1st cursor
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
slightwv post is a very nice example
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
is "over" not supported by forms 6i ?
because the trigger stands beside it and not able to compile it
>>, shouldn't the outside select be more like

Yes, and good catch!
>>is "over" not supported by forms 6i ?

Probably not.

You can create a view in the database and reference it in your Form.
yes , the code works well
one thing if you never mind
in the first row , how i can add also  sum(gross) for (unit in(2,4)) for example ?
>>how i can add also  sum(gross) for (unit in(2,4)) for example ?

Case statement?

sum(case when unit in (2,4) then gross end)
it not require "over" ?
It's up to you.  OVER or not, it's the CASE statement that is the important thing.  OVER just adds the window functionality.
If you want that as an additional column, try this -
select unit, gross, net, discount,
sum(gross) over() total_price,
sum(decode(unit,2,gross,4,gross,0)) over() unit2_4_total_price from
(select unit, sum(price) gross, sum(discpric) net, sum(price) - sum(discpric) discount
 from cr_gnditem
 where to_date(dob,'dd-mm-yyyy') >= to_date(:basic_block.d_from,'dd-mm-yyyy')
   and to_date(dob,'dd-mm-yyyy') <= to_date(:basic_block.d_to,'dd-mm-yyyy')
 group by unit);
I would suggest CASE over DECODE.  It is more universal and you can use IN...
If case is preferred over decode (although I do like decode), then
sum(case when unit in(2,4) then gross else 0 end) over() unit2_4_total_price
>>If case is preferred over decode (although I do like decode), then

Pretty much what I already posted?  

The OVER is optional and was already explained.  It was the CASE piece that was important...
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>I'm not sure the over is optional,

Maybe "optional" was a poor choice of words.  Sure, to get the SUM for the entire resultset it is required.

To show how to get a SUM for specific units, it wasn't necessary to show it.

Again, the CASE was what was important not the complete SUM syntax.

In other words, I just showed the minimal necessary syntax.

I see very little difference between

... sum(case when unit in (2,4) then gross end) ...
and
... sum(case when unit in (2,4) then gross end) over() ...

as far as teaching how to sum based on specific values in another column.

When asked about the OVER, I responded.  I just didn't paste the entire SQL.  I didn't think it necessary to do so.