[URGENT] Dynamic row and column report & DBMS
331957Aug 23 2005 — edited Aug 25 2005Hi all,
I need to produce a dynamic report for row and column, the method i used is to spool the data using DBMS_OUTPUT.PUTLINE.pls help me !!
I want to generate the dynamic week (eg 52 weeks ina year dynamically) and i have my own set of calendar to define the week.
T_CAL_WEEK (YEAR,MONTH,START_DATETIME,END_DATETIME,WEEK)
Sample data
==========
2005, 1, 20041231 210001,20050108 210000, 1
2005, 1, 20050108 210001,20050115 210000, 2
: : : : :
2005, 12, 20051224 210001,20051231 210000, 52
the expected output for the heading:
------------------------------------------------------------------------------
Week no : 1 2 3 ................................ 52
-----------------------------------------------------------------------------
I've tried a very stupid solution to place all those 52 values into 52 variables in the loop and concate all 52 variable values to obtain the above output.
Beside i have the query to get the data which is based on the week no.
EMPLOYEE_DETAIL(EMP_ID,ITEM,AMOUT,BILL_DATE)
Sample data (already sorted by date by week range)
=========
Detail for week no 1
1001, BASIC SALARY, 1000, 20050105 130000
1001, OT , 750,20050106 123000
1001, ADJUSTMENT, -25,20050106 145500
Detail for week no 2
1001, BASIC SALARY, 1000,20050109 145500
1001, OT , 300,20050112 125500
1001, LOAN DEDUCT, 50,20050112 140000
1001, ADJUSTMENT, -40,20050112 150000
Total salary := BASIC SALARY+OT-LOAN DEDUCT+(ADJUSTMENT);
Income Tax := total salary * 0.0025;
EPF := total salary * 0.03;
*** ALL the formula based on week no *****
Report output:
==========
Week no : | 1 | 2 | ................................ 52
------------------------------------------------------------------------------------------------------------------
Total Salary :| 1725 | 1210 |
Income tax :| 4.30 | 3.05 |
EPF :| 51.75 | 36.30 |
Anyone can help to provide me the better way to get this kind of output??
without assigning up to 52 variables just to get the heading... and how to get the record based on the dynamic week no?
My other concern is i may hit the the overloaded buffer size error, even set the dbms.enable(1000000) and also the line size > 255.
This is very urgently need help. thanks