CODEWITHSUNDEEP
sql
Pisoi Flo
Pisoi Flo

Reputation: 75

Stored procedure with multiple views

Can I create a stored procedure for the following situation? If not what is the best approach?

I am starting from the following table:

ccrcode   accountno
-------------------
1          100
1          110
1          120
2          100
2          200
2          210
3          100
3          300
3          320
4          400
4          410
4          420
5          500
5          510
5          520
6          410
6          600
6          620
7          700
7          710
7          720
8          800
8          810
8          720
9          900
9          910
9          920
10         1000
10         1010
10         1020

I am running the following queries:

create view step1
as
   select 
       A.ccrcode, A.accountno, 
       dense_rank() over (order by accountno) as V1
   from 
       all$ A

create view step2
as
   select 
       A.ccrcode, A.accountno, A.V1, B.V2
   from 
       step1 A
   join 
       (select ccrcode, min(V1) V2 
        from step1 
        group by ccrcode) B on B.ccrcode = A.ccrcode

create view step3
as
   select 
       A.ccrcode, A.accountno, A.V2 , B.V3
   from step2 A
   join (select accountno, min(V2) V3 
         from step2  
         group by accountno) B on B.accountno = A.accountno

create view step4
as
   select 
       A.ccrcode, A.accountno, A.V3, B.V4
   from step3 A
   join (select ccrcode, min(V3) V4 
         from step3 
         group by ccrcode) B on B.ccrcode = A.ccrcode

I would like to generate the results in step4 table whiteout creating all the other tables.

Upvotes: 1

Views: 200

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270683

You don't need views. Just use subqueries or CTEs:

with step1 as (
      select A.ccrcode, A.accountno, dense_rank() over (order by accountno) as V1
      from all$ A
     ),
     step2 as
     (select A.ccrcode, A.accountno, A.V1, B.V2
      from step1 A join
           (select ccrcode, min(V1) V2 from step1 group by ccrcode) B
           on B.ccrcode = A.ccrcode
     ),
     step3 as
     (select A.ccrcode, A.accountno, A.V2 , B.V3
      from step2 A join
           (select accountno, min(V2) V3 from step2 group by accountno) B
           on B.accountno = A.accountno
     ),
     step4 as
     (select A.ccrcode, A.accountno, A.V3, B.V4
      from step3 A join
           (select ccrcode, min(V3) V4 from step3 group by ccrcode) B
           on B.ccrcode = A.ccrcode
     )
select *
from step4;

Upvotes: 2

Related Questions