AGGREGATE FUNCTION
Date: 9 Dec 2001 01:28:40 -0800
Message-ID: <1df2b71.0112090128.1e2d469b_at_posting.google.com>
Hi there,
I have developed an Account Management System in Oracle 8i and Developer/2000 (release 6.0). The schema contains the following tables with their respective data types and constraints:
PROJECT TABLE Name Null? TypeVARCHAR2(200) We have a single department with different sub sections of this main department. This database is basically catering all different projects which are being allotted to different sections. This means that each department is assigned different projects and the PROJECT and PAYMENT TABLES are actually keeping track/record of all the transactions being made at different dates regarding the project payment (which is quite evident from the table’s structure). The TIME_STAMP column is for keeping the track of the latest transaction of a project’s payment so that the SELECT query can give us the latest transaction of a project’s payment.
----------------------------------------- --------
----------------------------
PROJECT_CODE NOT NULL VARCHAR2(20) (PK) PROJECT_NAME VARCHAR2(80) REMARKS VARCHAR2(100) PROJECT_FEE NUMBER(20) SECTION_CODE VARCHAR2(20) SECTION_NAME VARCHAR2(80) PAYMENT TABLE Name Null? Type
----------------------------------------- --------
----------------------
PROJECT_CODE VARCHAR2(20) (FK) CHEQUE_NO VARCHAR2(60) (U) ISSUE_DATE DATE PROJECT_FEE NUMBER(20) RECEIVED_AMOUNT NUMBER(20) REMAINING_AMOUNT NUMBER(20) TIME_STAMP VARCHAR2(40) SECTION_CODE VARCHAR2(20) SECTION TABLE Name Null? Type
----------------------------------------- --------
--------------------
SECTION_CODE NOT NULL VARCHAR2(20) (PK) SECTION_NAME VARCHAR2(80) PARTICULARS VARCHAR2(100) ISSUE_AMOUNT NUMBER(20) EXPENSES TABLE Name Null? Type ----------------------------------------- -------- ---------------- SECTION_CODE VARCHAR2(20) (FK) ISSUE_DATE DATE ISSUE_AMOUNT NUMBER(20) AMOUNT_SPENT NUMBER(20) AMOUNT_ADJUSTABLE NUMBER(20) TIME_STAMP VARCHAR2(20) DETAILS
Similarly in case of SECTION and EXPENSES tables, we are keeping track of amount of money issued to each section and the transaction details for each section individually. The inclusion of TIME_STAMP column in EXPENSES table is for the same purpose as described earlier.
As far as the reporting for the individual and total projects, individual and total sections latest data is concerned, I have been able to write simple SELECT statements and getting the required data.
One report which I’m trying to design should show the performance of each section regarding the number of projects allotted to it plus the project fee and the received project fee to a section plus the amount of money issued to a section plus the amount spent which eventually gives us the Net Income = Total Income – Amount Spent.
For this purpose, I have created the following views which give us the above mentioned results/fields:
VIEW 1
Name Null? Typewhich should be getting the required fields from all the main tables and one should not create all these views. The main problem which I have faced in writing the SELECT statement is when I’m trying to find the sum of the received amounts for all projects which are assigned to a particular section.
----------------------------------------- -------- ---------------
SECTION_CODE VARCHAR2(20) TOTAL_RECEIVED NUMBER VIEW 2 Name Null? Type
----------------------------------------- --------
--------------------
SECTION_CODE VARCHAR2(20) TOTAL_SPENT NUMBER VIEW 3 Name Null? Type ----------------------------------------- -------- ------------- SECTION_CODE VARCHAR2(20) TOTAL_RECEIVED NUMBER TOTAL_SPENT NUMBER NET_INCOME NUMBER VIEW 4 Name Null? Type
----------------------------------------- -------- --------------
SECTION_CODE VARCHAR2(20) SECTION_NAME VARCHAR2(80) PROJECT_CODE VARCHAR2(20) PROJECT_NAME VARCHAR2(80) Personally, what I think that there should be one SELECT statement
Thanks in Advance
Best Regards
Hassan Ayub Received on Sun Dec 09 2001 - 10:28:40 CET