johnsorensen
IS-IT--Management
I'm trying to write a stored procedure to normalize an Oracle row that contains 12 columns, to 1 column of 12 rows so I can do some reports in CR. This is my first Oracle Stored Proc. What am I doing wrong?
I've created a Oracle temporary table as follows:
CREATE GLOBAL TEMPORARY TABLE GLCONSOL_NORMAL_TEMP (....column specification.)
The table which doesn't work with Crystal very well is like this:
COMPANY
FISCAL_YEAR
ACCOUNT
SUB_ACCOUNT
Then 12 debits and 12 credits, for each month
DB_AMOUNT_01
DB_AMOUNT_02
...
DB_AMOUNT_12
CR_AMOUNT_01
CR_AMOUNT_02
...
CR_AMOUNT_12
My stored procedure inserts 12 records into the temporary table as follows;
COMPANY
FISCAL_YEAR
ACCOUNT
SUB_ACCOUNT
MONTH
DB_AMOUNT
CR_AMOUNT
I'm not even sure this is the way you should go about doing this and returning the temporary table back to the REF CURSOR.
Package specification:
Create GLCONSOL_NORMALIZE_PK
as TYPE GLCONSOL_NORMALIZE_type IS REF CURSOR RETURN GLCONSOL_normal_temp%ROWTYPE;
END GLCONSOL_NORMALIZE_PK;
Stored Procedure specification:
(SELECT_CURSOR IN OUT GLCONSOL_NORMALIZE_PK.GLCONSOL_NORMALIZE_type,
IN_COMPANY IN GLCONSOL.COMPANY%TYPE,
IN_ACCOUNT IN GLCONSOL.ACCOUNT%TYPE,
IN_SUB_ACCOUNT IN GLCONSOL.SUB_ACCOUNT%TYPE,
IN_FISCAL_YEAR IN GLCONSOL.FISCAL_YEAR%TYPE)
AS
/* DECLARATIONS */
i binary_integer := 1;
j binary_integer;
v binary_integer;
GLCONSOL_REC GLCONSOL%ROWTYPE;
CURSOR C1 IS
SELECT * FROM GLCONSOL
WHERE COMPANY = IN_COMPANY AND
ACCOUNT = IN_ACCOUNT AND
SUB_ACCOUNT = IN_SUB_ACCOUNT AND
FISCAL_YEAR = IN_FISCAL_YEAR;
/* Begin the program */
BEGIN
open C1;
/* should only select one record */
FETCH C1 INTO GLCONSOL_REC;
/* temp table - created global temporary table called GLCONSOL_NORMAL_temp with:
company
fiscal_year
account
sub_account
db_amount
cr_amount
month
db_beg_bal
cr_beg_bal
*/
/* Create 12 rows for each GLCONSOL row read. */
/* db_amount first */
insert into glconsol_normal_temp (company,fiscal_year,account,sub_account,db_amount,cr_amount,month,db_beg_bal,cr_beg_bal) values (IN_COMPANY, IN_FISCAL_YEAR, IN_ACCOUNT, IN_SUB_ACCOUNT, glconsol.db_amount_01, glconsol.cr_amount_01, 1 ,glconsol.db_beg_bal,glconsol.cr_beg_bal);
insert into glconsol_normal_temp (company,fiscal_year,account,sub_account,db_amount,cr_amount,month,db_beg_bal,cr_beg_bal) values (IN_COMPANY, IN_FISCAL_YEAR, IN_ACCOUNT, IN_SUB_ACCOUNT, glconsol.db_amount_02, glconsol.cr_amount_02, 2 ,glconsol.db_beg_bal,glconsol.cr_beg_bal);
(... 3-11 ...)
insert into glconsol_normal_temp (company,fiscal_year,account,sub_account,db_amount,cr_amount,month,db_beg_bal,cr_beg_bal) values (IN_COMPANY, IN_FISCAL_YEAR, IN_ACCOUNT, IN_SUB_ACCOUNT, glconsol.db_amount_12, glconsol.cr_amount_12, 12 ,glconsol.db_beg_bal,glconsol.cr_beg_bal);
open select_cursor for select * from glconsol_normal_temp;
/* Now I need to return out of the procedure the temp rows. */
/* SELECT_CURSOR had the 12 rows. */
END GLCONSOL_NORMALIZE_SP;
Thanks to anyone who could help me on this.
I've created a Oracle temporary table as follows:
CREATE GLOBAL TEMPORARY TABLE GLCONSOL_NORMAL_TEMP (....column specification.)
The table which doesn't work with Crystal very well is like this:
COMPANY
FISCAL_YEAR
ACCOUNT
SUB_ACCOUNT
Then 12 debits and 12 credits, for each month
DB_AMOUNT_01
DB_AMOUNT_02
...
DB_AMOUNT_12
CR_AMOUNT_01
CR_AMOUNT_02
...
CR_AMOUNT_12
My stored procedure inserts 12 records into the temporary table as follows;
COMPANY
FISCAL_YEAR
ACCOUNT
SUB_ACCOUNT
MONTH
DB_AMOUNT
CR_AMOUNT
I'm not even sure this is the way you should go about doing this and returning the temporary table back to the REF CURSOR.
Package specification:
Create GLCONSOL_NORMALIZE_PK
as TYPE GLCONSOL_NORMALIZE_type IS REF CURSOR RETURN GLCONSOL_normal_temp%ROWTYPE;
END GLCONSOL_NORMALIZE_PK;
Stored Procedure specification:
(SELECT_CURSOR IN OUT GLCONSOL_NORMALIZE_PK.GLCONSOL_NORMALIZE_type,
IN_COMPANY IN GLCONSOL.COMPANY%TYPE,
IN_ACCOUNT IN GLCONSOL.ACCOUNT%TYPE,
IN_SUB_ACCOUNT IN GLCONSOL.SUB_ACCOUNT%TYPE,
IN_FISCAL_YEAR IN GLCONSOL.FISCAL_YEAR%TYPE)
AS
/* DECLARATIONS */
i binary_integer := 1;
j binary_integer;
v binary_integer;
GLCONSOL_REC GLCONSOL%ROWTYPE;
CURSOR C1 IS
SELECT * FROM GLCONSOL
WHERE COMPANY = IN_COMPANY AND
ACCOUNT = IN_ACCOUNT AND
SUB_ACCOUNT = IN_SUB_ACCOUNT AND
FISCAL_YEAR = IN_FISCAL_YEAR;
/* Begin the program */
BEGIN
open C1;
/* should only select one record */
FETCH C1 INTO GLCONSOL_REC;
/* temp table - created global temporary table called GLCONSOL_NORMAL_temp with:
company
fiscal_year
account
sub_account
db_amount
cr_amount
month
db_beg_bal
cr_beg_bal
*/
/* Create 12 rows for each GLCONSOL row read. */
/* db_amount first */
insert into glconsol_normal_temp (company,fiscal_year,account,sub_account,db_amount,cr_amount,month,db_beg_bal,cr_beg_bal) values (IN_COMPANY, IN_FISCAL_YEAR, IN_ACCOUNT, IN_SUB_ACCOUNT, glconsol.db_amount_01, glconsol.cr_amount_01, 1 ,glconsol.db_beg_bal,glconsol.cr_beg_bal);
insert into glconsol_normal_temp (company,fiscal_year,account,sub_account,db_amount,cr_amount,month,db_beg_bal,cr_beg_bal) values (IN_COMPANY, IN_FISCAL_YEAR, IN_ACCOUNT, IN_SUB_ACCOUNT, glconsol.db_amount_02, glconsol.cr_amount_02, 2 ,glconsol.db_beg_bal,glconsol.cr_beg_bal);
(... 3-11 ...)
insert into glconsol_normal_temp (company,fiscal_year,account,sub_account,db_amount,cr_amount,month,db_beg_bal,cr_beg_bal) values (IN_COMPANY, IN_FISCAL_YEAR, IN_ACCOUNT, IN_SUB_ACCOUNT, glconsol.db_amount_12, glconsol.cr_amount_12, 12 ,glconsol.db_beg_bal,glconsol.cr_beg_bal);
open select_cursor for select * from glconsol_normal_temp;
/* Now I need to return out of the procedure the temp rows. */
/* SELECT_CURSOR had the 12 rows. */
END GLCONSOL_NORMALIZE_SP;
Thanks to anyone who could help me on this.