| Multiple rows into on "TEMP" table? [message #680651] |
Fri, 29 May 2020 13:34  |
 |
RM33
Messages: 9
Registered: December 2013
Location: New York City
|
Junior Member |
|
|
I am using Oracle 11g.
I have a complex report with a lot of calculations and functions. The code returns only 1 row per job ID. Now management wants the same report for multiple job IDs.
I was wondering if I can create a looping structure that loops around the above. Loop thru each job ID. Capture the single row it returns, put it into a "TEMP" table. Then use a ref cursor to print the report using Crystal Reports. The reason I say temp table because management will not create a real table for this.
I did not feel comfortable putting real code here. So the below is a SIMPLE example.
I want to pass several IDs. I want to loop thru the IDs and each single row returned is put into a temp table.
How do I crate a temp table to store each row returned.
EXAMPLE:
CREATE OR REPLACE PROCEDURE sp_Report(pi_ID IN VARCHAR2, pi_Month IN VARCHAR2, po_Report OUT SYS_REFCURSOR) AS
v_AMOUNT_A NUMBER := 0;
v_AMOUNT_B NUMBER := 0;
v_AMOUNT_C NUMBER := 0;
BEGIN
v_AMOUNT_A := Function_A(pi_ID, pi_Month);
v_AMOUNT_B := Function_B(pi_ID, pi_Month);
v_AMOUNT_C := Function_C(pi_ID, pi_Month);
OPEN po_Report FOR
SELECT v_AMOUNT_A,
v_AMOUNT_B,
v_AMOUNT_C
FROM Dual;
END sp_Report;
|
|
|
|
|
|
| Re: Multiple rows into on "TEMP" table? [message #681073 is a reply to message #680652] |
Wed, 10 June 2020 13:50  |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
This is one of the few cases where a Cartesian job is very helpful. For example if you wanted to produce the same report for for 5 different pople the following pseudo code would work
WITH
Emps
AS
(SELECT 'ABC' Emp_cd FROM DUAL
UNION ALL
SELECT 'DEF' Emp_cd FROM DUAL
UNION ALL
SELECT 'GHI' Emp_cd FROM DUAL
UNION ALL
SELECT 'JKL' Emp_cd FROM DUAL
UNION ALL
SELECT 'MNO' Emp_cd FROM DUAL)
SELECT B.Emp_cd,
A.Col1,
A.Col2,
A.Col3,
A.Col4
FROM Emps B, Report_file A
ORDER BY b.emp_cA.Seq_num; |
|
|
|
] 
Search
Help
Members
Register
Login
Home
