Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Group wise total of Block in oracle form

Posted on 2014-01-01
10
Medium Priority
?
963 Views
Last Modified: 2014-01-02
i have a block with these column
Code: [Select all] [Show/ hide]

srno,    fin-year     Ename     All_Amount        C_amount
1        2012-2013     A                  50                10
2        2012-2013     B                  30                10
3        2012-2013     C                  10                20
=========================================================
Total                             90                40
============================================================
srno,    fin-year     Ename     All_Amount        C_amount
1        2013-2014     d              20                  5
2        2013-2014     F              30                 10
========================================================
                                 50                 15


i want to total block fin_year wise as i mention above how i can handled in block.
how i can add summary?


Thanks in Advance
Hina mansoor
0
Comment
Question by:hinamansoor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 20

Accepted Solution

by:
flow01 earned 2000 total points
ID: 39749703
I don't know how to interrupt the lines of a block with something else.
So my solution would be to get the results without oracle forms knowing it's a detail or summery line
If it's a read only block I suggest to  create a view that to gives you the results.
If it's an already existing block change the name of the table to the name of the view
and the format of the srno to varchar2

It could be a view like
create or replace yourview
as
select * from
(
select  to_char(srno) srno,    fin_year,     Ename,     All_Amount,        C_amount
from yourtable
union
select 'Total' , fin_year,  null, sum(All_Amount) , sum(c_amount)
from yourtable
group by fin_year
)
order by fin_year
, case srno
  when 'Total' then 1
  else 0
  end
/
What database version you are using?
0
 

Author Comment

by:hinamansoor
ID: 39749752
Sir database version is oracle database 10.1.0 i tried your above code its giving me error

ERROR at line 1:
ORA-00922: missing or invalid option



Thanks
Hina mansoor
0
 
LVL 20

Expert Comment

by:flow01
ID: 39750034
Sorry , I did only test the select itself
change

create or replace yourview
by
create or replace view yourview
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:hinamansoor
ID: 39750109
Sir view is create and i called it also on canvas but there is no group total simple data is showing as we normally table called on canvas. attaching result picture. Sir what would be the next step after called view on canvas.



Thanks
Hina mansoor
result-picture.PNG
0
 
LVL 20

Expert Comment

by:flow01
ID: 39750250
Do it again (calling on canvas)  (i assume you use the wizard)  choose layout style tabular in stead of form (that results in all columns of a row on 1 line) and choose records displayed at 15 or 20 so there will be more then 1 row visible on the screen.

Otherwise use the layour editor to place the columns on a row and change the block-property 'number of records displayed' to the value you want.
0
 

Author Comment

by:hinamansoor
ID: 39750301
Yes it working fine for current year it showing me a TOTAL my question is to you Sir if i would put some more data for 2013-2014 it will give me TOTAL  under the 2012-2013 TOTAL or not. Attaching snap.


Once again Thank you very for helping me a lot.


Thanks
Hina mansoor
result-total.PNG
0
 
LVL 20

Expert Comment

by:flow01
ID: 39750312
Yes , because of the order by in the query.
This is the way i tested it: (based on your example  data)

with yourtable
as
(
select 1 srno,       '2012-2013' fin_year,     'A' ename     ,             50 all_amount,               10  c_amount from dual union
select 2 srno,       '2012-2013' fin_year,     'B' ename     ,             30 all_amount,               10  c_amount from dual union
select 3 srno,       '2012-2013' fin_year,     'C' ename     ,             10 all_amount,               20  c_amount from dual union
select 1 srno,       '2013-2014' fin_year,     'd' ename     ,         20     all_amount,             5     c_amount from dual union
select 2 srno,       '2013-2014' fin_year,     'F' ename     ,         30     all_amount,            10     c_amount from dual
)
select * from
(
select  to_char(srno) srno,    fin_year,     Ename,     All_Amount,        C_amount
from yourtable
union
select 'Total' , fin_year,  null, sum(All_Amount) , sum(c_amount)
from yourtable
group by fin_year
)
order by fin_year
, case srno
  when 'Total' then 1
  else 0
  end
/
0
 

Author Comment

by:hinamansoor
ID: 39750906
gr8 logic Sir how would i implement this code in oracle form in tabular



Thanks
0
 
LVL 20

Expert Comment

by:flow01
ID: 39751856
You don't have to. You have already created the form using  the view.
To test 2013-2014  either  add the records of 2013-2014 in your test database.
Or replace the view you already made with the testquery

create or replace view yourview as
with ... (same code as testquery)
0
 

Author Comment

by:hinamansoor
ID: 39751971
Sir you are really gr8, it worked Thanks you very much Sir




Thanks
Hina mansoor
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question