What could be best possible way to avoid the intermediary tables

Question

There are set of sql queries which I want to convert into procedure without intermediary tables creation and dropping. I have included the sample queries below. what could be best possible way for doing that? Since I am new to procedures can advice me how to do that.

Can we use cursor in procedures for doing that? In the last line of the query im joining 2 intermediary tables. instead of that Can we join two cursor? If so how can we do that? Is there any way to do that. Please suggest me.

CREATE TABLE A AS SELECT ID_LOAN FROM master_copy WHERE ZERO_BAL_CODE IN (1);

CREATE TABLE B AS SELECT master_copy.ID_LOAN,LOAN_AGE,master_copy.vintage,DELINQ_STATUS,ZERO_BAL_CODE,master_copy.ACTUAL_LOSS,current_upb FROM master_copy INNER JOIN A ON master_copy.ID_LOAN= A.ID_LOAN;

CREATE TABLE prepaidData AS SELECT ID_LOAN,max(to_number(DELINQ_STATUS)) as DELINQ_STATUS,max(loan_age) AS LOAN_AGE,max(ZERO_BAL_CODE) as ZERO_BAL_CODE,max(vintage) as vintage, min(ACTUAL_LOSS) as actual_loss,MIN(NULLIF(current_upb,0)) as current_upb FROM B group by id_loan; alter table prepaiddata add loan_type varchar2(255) default 'prepaid';

drop table a; drop table b;

CREATE TABLE A AS SELECT ID_LOAN FROM master_copy WHERE ZERO_BAL_CODE IN (3);

CREATE TABLE B AS SELECT master_copy.ID_LOAN,LOAN_AGE,master_copy.vintage,DELINQ_STATUS,ZERO_BAL_CODE,master_copy.ACTUAL_LOSS,current_upb FROM master_copy INNER JOIN A ON master_copy.ID_LOAN= A.ID_LOAN;

CREATE TABLE DEFAULT_FORECLOSURE AS SELECT ID_LOAN,max(to_number(DELINQ_STATUS)) as DELINQ_STATUS,max(loan_age) AS LOAN_AGE,max(ZERO_BAL_CODE) as ZERO_BAL_CODE,max(vintage) as vintage, min(ACTUAL_LOSS) as actual_loss,MIN(NULLIF(current_upb,0)) as current_upb FROM B group by id_loan; alter table DEFAULT_FORECLOSURE add loan_type varchar2(255) default 'default_foreclosure';

drop table a; drop table b;

create table aa_loan_type as(select * from prepaiddata union select * from DEFAULT_FORECLOSURE);

Answer 1

The best way to avoid intermediate tables is to use inline views. Your queries can be rewritten into this one SQL statement:

--aa_loan_type
--
--prepaidData
SELECT ID_LOAN,max(to_number(DELINQ_STATUS)) as DELINQ_STATUS,max(loan_age) AS LOAN_AGE,max(ZERO_BAL_CODE) as ZERO_BAL_CODE,max(vintage) as vintage, min(ACTUAL_LOSS) as actual_loss,MIN(NULLIF(current_upb,0)) as current_upb, 'PREPAID' LOAN_TYPE
FROM
(
    --B
    SELECT
        master_copy.ID_LOAN,LOAN_AGE,master_copy.vintage,DELINQ_STATUS,ZERO_BAL_CODE,master_copy.ACTUAL_LOSS,current_upb
    FROM master_copy
    INNER JOIN
    (
        --A
        SELECT ID_LOAN FROM master_copy WHERE ZERO_BAL_CODE IN (1)
    ) A
    ON master_copy.ID_LOAN= A.ID_LOAN;
) B
GROUP BY ID_LOAN
union
--DEFAULT_FORECLOSURE
SELECT ID_LOAN,max(to_number(DELINQ_STATUS)) as DELINQ_STATUS,max(loan_age) AS LOAN_AGE,max(ZERO_BAL_CODE) as ZERO_BAL_CODE,max(vintage) as vintage, min(ACTUAL_LOSS) as actual_loss,MIN(NULLIF(current_upb,0)) as current_upb, 'default_foreclosure' loan_type
FROM 
(
    --B
    SELECT master_copy.ID_LOAN,LOAN_AGE,master_copy.vintage,DELINQ_STATUS,ZERO_BAL_CODE,master_copy.ACTUAL_LOSS,current_upb
    FROM master_copy
    INNER JOIN
    (
        --A
        SELECT ID_LOAN FROM master_copy WHERE ZERO_BAL_CODE IN (3)
    ) A
        ON master_copy.ID_LOAN= A.ID_LOAN
) B
group by id_loan;

When built correctly, one large SQL statement is often much better than multiple small SQL statements. The overall code will be simpler (less objects, easier to debug in an IDE) and potentially much faster (no need to write data, gives the optimizer more chances to do something clever).

Building large SQL "correctly" is subjective, but it boils down to treating each inline view as a miniature program:

1. Keep each inline view simple, combine them in simple steps, and repeat.
2. Use good names and comments for each inline view. You'll probably want something better than "A" and "B".
3. Use Allman style bracket matching, with parentheses on each line. Inline views are important and deserve extra whitespace and aligned parentheses. Except for beginner developers, we don't need to align keywords like SELECT and FROM. We need to align important boundaries, like the parentheses of each inline view. This will help you debug by quickly highlighting and running code in an IDE.
4. Keep the subquery interfaces simple by using inline views instead of correlated subqueries or common table expressions. The beauty of inline views is their simplicity - relational data in, relational data out.