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 ??????????
NiceMan331Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helena Markováprogrammer-analystCommented:
Maybe I don't understand it (?):
in your cursor sum(PRICE) is presented as sum(PRICE) Gross for each unit.
NiceMan331Author Commented:
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')
Helena Markováprogrammer-analystCommented:
There can be 2 cursors:
1st is your original
and 2nd
Cursor c_ent_2 IS
select sum(PRICE) Gross
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');

You can open cursor when needed.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

NiceMan331Author Commented:
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
slightwv (䄆 Netminder) Commented:
You cannot select from a cursor.

What you can do is return the sum you want as part of every row or inside the cursor loop, add the fetched numbers.

Here is what I was talking about for every row:
select unit, gross, net, discount, sum(price) over() 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
order by sum(PRICE) desc 
)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Helena Markováprogrammer-analystCommented:
slightwv post is a very nice example
awking00Information Technology SpecialistCommented:
Since price is not a column or alias of the subquery, shouldn't the outside select be more like -
select unit, gross, net, discount, sum(gross) over () total_price from ...
NiceMan331Author Commented:
is "over" not supported by forms 6i ?
because the trigger stands beside it and not able to compile it
slightwv (䄆 Netminder) Commented:
>>, shouldn't the outside select be more like

Yes, and good catch!
slightwv (䄆 Netminder) Commented:
>>is "over" not supported by forms 6i ?

Probably not.

You can create a view in the database and reference it in your Form.
NiceMan331Author Commented:
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 ?
slightwv (䄆 Netminder) Commented:
>>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)
NiceMan331Author Commented:
it not require "over" ?
slightwv (䄆 Netminder) Commented:
It's up to you.  OVER or not, it's the CASE statement that is the important thing.  OVER just adds the window functionality.
awking00Information Technology SpecialistCommented:
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);
slightwv (䄆 Netminder) Commented:
I would suggest CASE over DECODE.  It is more universal and you can use IN...
awking00Information Technology SpecialistCommented:
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
slightwv (䄆 Netminder) Commented:
>>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...
awking00Information Technology SpecialistCommented:
I'm not sure the over is optional, since we have no data and expected results to deal with but the following demonstrates the difference of the two options:
      UNIT      PRICE   DISCPRIC
---------- ---------- ----------
         1        100         10
         2        200         20
         2        300         30
         3        400         40
         4        500         50
         4        600         60
         3        700         70
         1        800         80
Without over()
SQL> select unit, gross, net, discount,
  2  sum(gross) total_price,
  3  sum(case when unit in(2,4) then gross else 0 end) unit2_4_total_price from
  4  (select unit, sum(price) gross, sum(discpric) net, sum(price) - sum(discpric) discount
  5   from item
  6   group by unit)
  7  group by unit, gross, net, discount;

      UNIT      GROSS        NET   DISCOUNT TOTAL_PRICE UNIT2_4_TOTAL_PRICE
---------- ---------- ---------- ---------- ----------- -------------------
         1        900         90        810         900                   0
         2        500         50        450         500                 500
         3       1100        110        990        1100                   0
         4       1100        110        990        1100                1100
With over()
SQL> select unit, gross, net, discount,
  2  sum(gross) over() total_price,
  3  sum(decode(unit,2,gross,4,gross,0)) over() unit2_4_total_price from
  4  (select unit, sum(price) gross, sum(discpric) net, sum(price) - sum(discpric) discount
  5   from item
  6   group by unit);

      UNIT      GROSS        NET   DISCOUNT TOTAL_PRICE UNIT2_4_TOTAL_PRICE
---------- ---------- ---------- ---------- ----------- -------------------
         1        900         90        810        3600                1600
         2        500         50        450        3600                1600
         3       1100        110        990        3600                1600
         4       1100        110        990        3600                1600
slightwv (䄆 Netminder) Commented:
>>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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.