CODEWITHSUNDEEP
sqloracle-databaseplsql
Roy Wu
Roy Wu

Reputation: 71

Oracle Iterate query result without insert

I have the following table like below

enter image description here

And the result I expected below, depend on the date diff (ex. 2020/3/27 - 2020/3/20 =7) and extend the result. Does it possible without use insert to the internal table and extend the table by the original. Because of the privilege limitation.

enter image description here

Upvotes: 0

Views: 95

Answers (2)

XING
XING

Reputation: 9886

Does it possible without use insert to the internal table and extend the table by the original. Because of the privilege limitation.

Yes it possible using Level in SQL statement. See below

SELECT
  SYSDATE - level
 ,'XXX' Name
 ,10  QTY
 ,level Date_diff
from
  dual
connect by
  level < 8
order by
  1;

EDIT: As per sample data given, the query is modified as

With       
      tab1 as  (  --Implementing Logic    
                  Select 
                        v_date - (level-1) v_date ,
                         name,
                         qty,
                        (data_diff+1) - level col
                  from tab            
                  connect by level <= data_diff                      
                )
   -- Selecting records
   select distinct * 
   from tab1 
   order by name,col desc ;

Demo:

With Tab(v_date,name,qty,data_diff)
        as ( -- Table Data
             select to_date('20-Mar-2020','DD-Mon-YYYY'),'A',10, 7 from dual
             UNION 
             select to_date('29-Mar-2020','DD-Mon-YYYY'),'B',12, 3 from dual
             UNION ALL
             select to_date('17-Mar-2020','DD-Mon-YYYY'),'C',30, 4 from dual
            ),    
      tab1 as  (      
                  Select 
                        v_date - (level-1) v_date ,
                         name,
                         qty,
                        (data_diff+1) - level col
                  from tab            
                  connect by level <= data_diff                      
                )

   select distinct * 
   from tab1 
   order by name,col desc ;

Result:

enter image description here

Upvotes: 2

psaraj12
psaraj12

Reputation: 5072

You can use the below

          WITH data
             AS (SELECT To_date('20-MAR-2020', 'DD-MON-YYYY') date1,
                        'xxx'                                 name,
                        10                                    qty,
                        7                                     date_diff
                 FROM   dual)
        SELECT date1 + LEVEL-1,
               name,
               qty,
               date_diff - LEVEL+1
        FROM   data
        CONNECT BY LEVEL < date_diff + 1

Upvotes: 0

Related Questions