| Can You Help me Creating index for the Given Problem [message #57679] |
Mon, 30 June 2003 01:05  |
NEPAL
Messages: 8
Registered: March 2003
|
Junior Member |
|
|
Hello,
I have a problem of retrieveing data quickly with the following Procedure and if you have any idea to generate the data fastly, Please let me get your help..... I have copied the Procedure COde and some table structures too and if you need you can ask for more tables that are used here in the Procedure........
Yours,
Roshan
email me at jyapu@hotmail.com
=====================
Here is the Procedure
=====================
PROCEDURE Display_List IS
last_row INTEGER := 0;
last_col INTEGER := 0;
vEmployeeId VARCHAR2(6);
vPayrollItemCode VARCHAR2(2);
nAmount NUMBER(11,2);
vMonthYr VARCHAR2(10);
dEffectiveDate DATE;
CURSOR CurEmployee IS /* Cursor 1 */
SELECT E.EMPLOYEE_ID
FROM JANAK.EMPLOYEE_INFORMATION E, JANAK.EMPLOYEE_OFFICIAL_HISTORY V
WHERE NVL(CURRENTLY_ON_JOB,'N') = 'Y'
AND E.EMPLOYEE_ID = V.EMPLOYEE_ID
AND E.EMPLOYEE_ID = NVL(:CONTROL_BLOCK.EMPLOYEE_ID,E.EMPLOYEE_ID)
AND V.ORG_STRUCTURE_CODE IN
(SELECT ORG_STRUCTURE_CODE FROM JANAK.ORG_STRUCTURE_HIERARCHY
CONNECT BY PRIOR ORG_STRUCTURE_CODE = UPPER_ORG_STRUCTURE_CODE
START WITH ORG_STRUCTURE_CODE = NVL(:CONTROL_BLOCK.ORG_STRUCTURE_CODE, V.ORG_STRUCTURE_CODE)) AND
V.EFFECTIVE_DATE_FROM = (SELECT MAX(EFFECTIVE_DATE_FROM)
FROM JANAK.EMPLOYEE_OFFICIAL_HISTORY WHERE
EMPLOYEE_ID = E.EMPLOYEE_ID AND EFFECTIVE_DATE_FROM <= dEffectiveDate)
ORDER BY E.EMPLOYEE_ID;
CURSOR CurPayroll IS /* Cursor 2 */
SELECT DISTINCT P.PAYROLL_ITEM_CODE , P.PAYROLL_ITEM_TYPE,
P.SERIAL_NO FROM JANAK.PAYROLL_ITEM_CODE P,
JANAK.VW_MONTHLY_SALARY_HISTORY M
WHERE P.PAYROLL_ITEM_CODE = M.PAYROLL_ITEM_CODE
AND M.PAYROLL_MONTH = vMonthYr
ORDER BY P.PAYROLL_ITEM_TYPE , P.SERIAL_NO;
CURSOR CurSalary IS /* Cursor 3 */
SELECT AMOUNT FROM JANAK.VW_MONTHLY_SALARY_HISTORY
WHERE PAYROLL_MONTH = vMonthYr AND
EMPLOYEE_ID = vEmployeeId AND
PAYROLL_ITEM_CODE = vPayrollItemCode;
BEGIN
IF :GLOBAL.CalendarType = 'AD' THEN
vMonthYr := :CTRL_MONTH ||:CTRL_YEAR;
dEffectiveDate := LAST_DAY(TO_DATE(vMonthYr,'MONYYYY'));
ELSE
vMonthYr := :CTRL_MONTH ||:CTRL_YEAR;
dEffectiveDate := JANAK.CALENDAR.BSTOAD(SUBSTR(vMonthYr, 3,4) || '-' || SUBSTR(vMonthYr,1,2) || '-' || JANAK.CALENDAR.GET_DAYS_IN_BSMONTH(vMonthYr));
END IF;
HIDE_WINDOW('WINDOW2');
HIDE_VIEW('CAN_INITIAL');
SET_WINDOW_PROPERTY('WINDOW1', WINDOW_STATE, MAXIMIZE);
SHOW_VIEW('CAN_EMP');
SHOW_VIEW('CAN_SAL');
GO_BLOCK('EMPLOYEE_INFORMATION');
FIRST_RECORD;
FOR c IN CurEmployee LOOP
:EMPLOYEE_INFORMATION.EMPLOYEE_ID := c.EMPLOYEE_ID;
last_row := last_row + 1;
:EMPLOYEE_INFORMATION.EMPLOYEE_NAME := JANAK.PERSONNEL.EMPLOYEE_NAME(:EMPLOYEE_INFORMATION.EMPLOYEE_ID);
:EMPLOYEE_POSITION := JANAK.PERSONNEL.POSITION_NAME(JANAK.PERSONNEL.EMPLOYEE_STATUS(:EMPLOYEE_INFORMATION.EMPLOYEE_ID,'P',SYSDATE));
NEXT_RECORD;
END LOOP;
GO_BLOCK('MONTHLY_SALARY');
FIRST_RECORD;
FOR a IN CurPayroll LOOP
:MONTHLY_SALARY.PAYROLL_ITEM_CODE := a.PAYROLL_ITEM_CODE;
last_col := last_col + 1;
NEXT_RECORD;
END LOOP;
FOR rec_no IN 1..last_row LOOP
GO_BLOCK('EMPLOYEE_INFORMATION');
GO_RECORD(rec_no);
vEmployeeId := :EMPLOYEE_INFORMATION.EMPLOYEE_ID;
FOR col_no IN 1..last_col LOOP
GO_BLOCK('MONTHLY_SALARY');
GO_RECORD(col_no);
vPayrollITemCode := :PAYROLL_ITEM_CODE;
nAmount := 0;
OPEN CurSalary;
FETCH CurSalary INTO nAmount;
CLOSE CurSalary;
COPY(nAmount, 'AMOUNT' || TO_CHAR(col_no));
END LOOP;
END LOOP;
GO_BLOCK('EMPLOYEE_INFORMATION');
FIRST_RECORD;
GO_ITEM('BTN_BACK2');
END;
========================
Table structures are given below:
==============================
SQL> desc employee_information
Name Null? Type
------------------------------- -------- ----
EMPLOYEE_ID NOT NULL VARCHAR2(6)
FIRST_NAME VARCHAR2(15)
MIDDLE_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(20)
EMPLOYEE_NAME_NEPALI VARCHAR2(50)
DATE_OF_BIRTH DATE
PLACE_OF_BIRTH VARCHAR2(3)
GENDER CHAR(1)
MARITAL_STATUS CHAR(1)
RELIGION_CODE VARCHAR2(2)
NATIONALITY_CODE VARCHAR2(2)
PERMANENT_ADDRESS VARCHAR2(100)
PER_DISTRICT VARCHAR2(30)
PER_COUNTRY_CODE VARCHAR2(2)
TEMPORARY_ADDRESS VARCHAR2(100)
TEMP_DISTRICT VARCHAR2(30)
TEMP_COUNTRY_CODE VARCHAR2(2)
CONTACT_PHONE_NO VARCHAR2(30)
APPOINTED_ON DATE
APPOINTED_AGE NUMBER(2)
APPOINTED_BY VARCHAR2(40)
JOINED_ON DATE
CONTRACT_EXPIRES_ON DATE
PROBATION_END_DATE DATE
BANK_ACCOUNT_NO VARCHAR2(15)
NAGRIK_LAGANI_KOSH VARCHAR2(10)
PF_NO VARCHAR2(10)
CURRENTLY_ON_JOB CHAR(1)
CITIZENSHIP_NO VARCHAR2(20)
ISSUED_ON DATE
ISSUED_PLACE VARCHAR2(3)
HEIGHT VARCHAR2(5)
WEIGHT VARCHAR2(5)
COMPLEXION VARCHAR2(20)
EYE_COLOR VARCHAR2(10)
HAIR_COLOR VARCHAR2(10)
IDENTIFICATION_MARK VARCHAR2(20)
DESCRIPTION VARCHAR2(100)
(Primary key used in this table PK_EMP_ID)
SQL> desc JANAK.ORG_STRUCTURE_HIERARCHY
Name Null? Type
------------------------------- -------- ----
ORG_STRUCTURE_CODE NOT NULL VARCHAR2(3) PK_ORGH_CODE is Primary Key.
ORG_STRUCTURE_NAME VARCHAR2(40)
ORG_NAME_NEPALI VARCHAR2(50)
DESCRIPTION VARCHAR2(100)
HIERARCHY_CODE VARCHAR2(3)
UPPER_ORG_STRUCTURE_CODE VARCHAR2(3)
LOCATION_CODE VARCHAR2(3)
SQL> desc EMPLOYEE_OFFICIAL_HISTORY
Name Null? Type
------------------------------- -------- ----
EMPLOYEE_ID VARCHAR2(6)
HISTORY_CHANGE_TYPE CHAR(1)
POSITION_CODE VARCHAR2(3)
ORG_STRUCTURE_CODE VARCHAR2(3)
JOB_CODE VARCHAR2(3)
JOB_STATUS_CODE VARCHAR2(3)
CURRENT_STEP NUMBER(2)
SHIFT_CODE VARCHAR2(1)
EFFECTIVE_DATE_FROM DATE
EFFECTIVE_DATE_TO DATE
JOINED_DATE DATE
APPROVED_DATE DATE
APPROVED_BY VARCHAR2(6)
REMARKS VARCHAR2(200)
APPOINTED_TAG VARCHAR2(1)
PROMOTION_TAG VARCHAR2(1)
TRANSFER_TAG VARCHAR2(1)
DEPUTED_TAG VARCHAR2(1)
JOB_TAG VARCHAR2(1)
JOBSTATUS_TAG VARCHAR2(1)
SHIFT_TAG VARCHAR2(1)
PROMOTION_STATUS VARCHAR2(1)
DEMOTION_TAG VARCHAR2(1)
|
|
|
|
| SUBSTR PROBLEM [message #57710 is a reply to message #57679] |
Tue, 01 July 2003 08:59  |
NEPAL
Messages: 8
Registered: March 2003
|
Junior Member |
|
|
HELLO,
I HAVE A PROBLEM IN RETRIEVING DATA QUICKLY BECAUSE I AM USING SUBSTR IN THE PL/SQL.............. COULD YOU TELL ME HOW TO MINIMIZE THE DATA RETIRIEVING TIME?
YOURS,
ROSHAN
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
