Home » SQL & PL/SQL » SQL & PL/SQL » Multiple rows into on "TEMP" table?
Multiple rows into on "TEMP" table? [message #680651] Fri, 29 May 2020 13:34 Go to next message
RM33
Messages: 11
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 #680652 is a reply to message #680651] Fri, 29 May 2020 13:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

In the end, feedback to your topics if you want further help.

Re: Multiple rows into on "TEMP" table? [message #681073 is a reply to message #680652] Wed, 10 June 2020 13:50 Go to previous message
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;
Previous Topic: Simple UPDATE - should be easy but just can't get it!
Next Topic: Time Portion of Date/Time Value Being Stripped When Passed to Stored Procedure (3 merged)
Goto Forum:
  


Current Time: Fri Mar 29 08:52:09 CDT 2024