Home » SQL & PL/SQL » SQL & PL/SQL » Sql Loop (Sql navigator)
Sql Loop [message #607058] Sun, 02 February 2014 01:57 Go to next message
majlinda.kastrati
Messages: 2
Registered: February 2014
Location: Watford
Junior Member
Dear all,

Could anyone help me with the issue below.

I have a table table_1, which has an ID_No,Amount,date and there is only one row for this ID in table_1.

I want to make a loop for a column amount, based on column No, to add such number of rows.

Example: there is a payment plan for 12 months. The amount is 1200, and based on No=12, in the second row, amount will be subtracted by 100 and continuously, until it will be zero. Also Column date will be month=(month-1).

ID_No No Date Amount
999 12 1/1/2014 1200
999 11 2/1/2014 1100
999 10 3/1/2014 1000
999 9 4/1/2014 900
999 8 5/1/2014 800
999 7 6/1/2014 700
999 6 7/1/2014 600
999 5 8/1/2014 500
999 4 9/1/2014 400
999 3 10/1/2014 300
999 2 11/1/2014 200
999 1 12/1/2014 100

Thank you!
Re: Sql Loop [message #607059 is a reply to message #607058] Sun, 02 February 2014 02:06 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

Your description of table_1 does not match the rows you have provided. You need to give better information: the CREATE TABLE statement that creates table_1, and the INSERT statements that create those rows. And please use [code] tags from now on.
Re: Sql Loop [message #607064 is a reply to message #607058] Sun, 02 February 2014 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Assuming you have only one row per ID in your table and you want to generate No rows decreasing amount by 100 (computed by amount/No, i.e. 1200/12) and increasing Date by 1 month then:
SQL> with
  2    data as ( 
  3      select 999 id, 12 no, to_date('1/1/2014','MM/DD/YYYY') dt, 1200 amount
  4      from dual
  5    )
  6  select id, 
  7         no-row_number() over (partition by id order by column_value)+1 no,
  8         add_months(dt, column_value-1) dt,
  9         amount-(amount/no*(column_value-1)) amount
 10  from data,
 11       table(cast(multiset(select level from dual connect by level <= no+1)
 12             as sys.odciNumberList))
 13  /
        ID         NO DT              AMOUNT
---------- ---------- ----------- ----------
       999         12 01-JAN-2014       1200
       999         11 01-FEB-2014       1100
       999         10 01-MAR-2014       1000
       999          9 01-APR-2014        900
       999          8 01-MAY-2014        800
       999          7 01-JUN-2014        700
       999          6 01-JUL-2014        600
       999          5 01-AUG-2014        500
       999          4 01-SEP-2014        400
       999          3 01-OCT-2014        300
       999          2 01-NOV-2014        200
       999          1 01-DEC-2014        100
       999          0 01-JAN-2015          0

(I added the line 0 from your output to satisfy the condition "until it will be zero".)

[Updated on: Sun, 02 February 2014 04:00]

Report message to a moderator

Re: Sql Loop [message #607338 is a reply to message #607064] Wed, 05 February 2014 00:56 Go to previous messageGo to next message
bluefred
Messages: 16
Registered: June 2012
Location: Qatar
Junior Member
DT should decrease by 1 month at a time, so in Michel's answer change add_months(dt, column_value-1) to add_months(dt, -column_value+1)
or you can use Oracle Model Clause like the following:

SELECT ID,NO,DT,AMOUNT
FROM (
SELECT 999 ID, 12 NO, TO_DATE('1/1/2014','MM-DD-YYYY') DT, 1200 AMOUNT
FROM DUAL
UNION
SELECT 888 ID, 12 NO, TO_DATE('1/1/2014','MM-DD-YYYY') DT, 1200 AMOUNT
FROM DUAL
)
MODEL 
  PARTITION BY (ID)
  DIMENSION BY (NO) 
  MEASURES (DT,AMOUNT)
  RULES (  
  DT[FOR NO FROM 11 TO 1 DECREMENT 1]=ADD_MONTHS(DT[CV()+1],-1),
  AMOUNT[FOR NO FROM 11 TO 1 DECREMENT 1]=AMOUNT[CV()+1]-100
  );

[Updated on: Wed, 05 February 2014 00:57]

Report message to a moderator

Re: Sql Loop [message #607341 is a reply to message #607338] Wed, 05 February 2014 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
DT should decrease by 1 month at a time,


It is not what OP's output shows and his explanations are not clear so my "assuming" prelude.
Also note that "12" is not a constant but a variable given by "No" column (quote: "based on No=12").
You have to modify your query (hint: use ITERATE). Wink

Re: Sql Loop [message #607343 is a reply to message #607341] Wed, 05 February 2014 01:18 Go to previous messageGo to next message
bluefred
Messages: 16
Registered: June 2012
Location: Qatar
Junior Member
Quote:

It is not what OP's output shows and his explanations are not clear so my "assuming" prelude.


right that's what the output says, but in the requirement it says:

Quote:

Also Column date will be month=(month-1).


I assumed 12 to be a constant since we only have 12 months a year 'ceteris paribus'
Quote:

Also note that "12" is not a constant but a variable given by "No" column (quote: "based on No=12").


Otherwise, Model Clause with ITIRATE rule should be the right answer.

[Updated on: Wed, 05 February 2014 02:22]

Report message to a moderator

Re: Sql Loop [message #607372 is a reply to message #607343] Wed, 05 February 2014 03:29 Go to previous messageGo to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
is this a solution ?
select add_months(trunc(sysdate,'year'),level-1) month, 1200-100*(level-1) amount
from dual
connect by level<=12

MONTH	AMOUNT

01.01.2014	1200
01.02.2014	1100
01.03.2014	1000
01.04.2014	900
01.05.2014	800
01.06.2014	700
01.07.2014	600
01.08.2014	500
01.09.2014	400
01.10.2014	300
01.11.2014	200
01.12.2014	100


Re: Sql Loop [message #607374 is a reply to message #607372] Wed, 05 February 2014 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The values are not fixed but taken from a table and there can be several rows (ID) in this table.
If you take all these into account and use the same way than you query you will end with mine (or the better one below) or the like:
SQL> break on id skip 1 dup
SQL> with
  2    data as (
  3      select 999 id, 12 no, to_date('1/1/2014','MM/DD/YYYY') dt, 1200 amount from dual
  4      union all
  5      select 111 id, 5 no, to_date('3/1/2014','MM/DD/YYYY') dt, 200 amount from dual
  6    )
  7  select id,
  8         no-column_value+1 no,
  9         add_months(dt, column_value-1) dt,
 10         amount-(amount/no*(column_value-1)) amount
 11  from data,
 12       table(cast(multiset(select level from dual connect by level <= no+1)
 13             as sys.odciNumberList))
 14  /
        ID         NO DT              AMOUNT
---------- ---------- ----------- ----------
       999         12 01-JAN-2014       1200
       999         11 01-FEB-2014       1100
       999         10 01-MAR-2014       1000
       999          9 01-APR-2014        900
       999          8 01-MAY-2014        800
       999          7 01-JUN-2014        700
       999          6 01-JUL-2014        600
       999          5 01-AUG-2014        500
       999          4 01-SEP-2014        400
       999          3 01-OCT-2014        300
       999          2 01-NOV-2014        200
       999          1 01-DEC-2014        100
       999          0 01-JAN-2015          0

       111          5 01-MAR-2014        200
       111          4 01-APR-2014        160
       111          3 01-MAY-2014        120
       111          2 01-JUN-2014         80
       111          1 01-JUL-2014         40
       111          0 01-AUG-2014          0

19 rows selected.



Re: Sql Loop [message #607377 is a reply to message #607374] Wed, 05 February 2014 05:30 Go to previous messageGo to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
You right . Ended with the same method. Only no is dynamic.

select a.id, 
add_months(xmonth,column_value) month, 
amount-(amount/(months_between(trunc(sysdate,'year')+365,xmonth))*column_value) amount
from  
(
    select 
    100*level id, 
    1200-level*100 amount, 
    add_months(trunc(sysdate,'year'),level) xmonth,
    months_between(trunc(sysdate,'year')+365,add_months(trunc(sysdate,'year'),level) ) n_months
    from dual
    connect by level<=10
) a,table(cast(multiset(select level from dual connect by level <= n_months)
             as sys.odciNumberList))
order by id, month

ID	MONTH	AMOUNT

100	01.03.2014	1000
100	01.04.2014	900
100	01.05.2014	800
100	01.06.2014	700
100	01.07.2014	600
100	01.08.2014	500
100	01.09.2014	400
100	01.10.2014	300
100	01.11.2014	200
100	01.12.2014	100
100	01.01.2015	0
200	01.04.2014	900
200	01.05.2014	800
200	01.06.2014	700
200	01.07.2014	600
200	01.08.2014	500
200	01.09.2014	400
200	01.10.2014	300
200	01.11.2014	200
200	01.12.2014	100
200	01.01.2015	0
300	01.05.2014	800
300	01.06.2014	700
300	01.07.2014	600
300	01.08.2014	500
300	01.09.2014	400
300	01.10.2014	300
300	01.11.2014	200
300	01.12.2014	100
300	01.01.2015	0
..........................
Re: Sql Loop [message #607409 is a reply to message #607064] Wed, 05 February 2014 13:41 Go to previous messageGo to next message
majlinda.kastrati
Messages: 2
Registered: February 2014
Location: Watford
Junior Member
Thank you very much, it was very helpful.

I would be thankful if you could tell me a similar case like this.

The payment will be done quarterly basis.

N=12/3=4, number of rows will be 4
Date that will be: 01/03/2014, 01/06/2014, 01/09/2014 and 01/12/2014
Amount=amount-300 until maturity, until 0.

Also case when n=2, eaxh 6 months.


Thanks!
Re: Sql Loop [message #607410 is a reply to message #607409] Wed, 05 February 2014 14:00 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is just a tiny difference with the previous problem.
As you can see the solutions do not rely on constant just on the values that are in the table columns.
Read again what have been posted. Try to understand the queries. Clarify the requirements.

[Updated on: Wed, 05 February 2014 14:01]

Report message to a moderator

Previous Topic: Load .csv file with dynamic content into oracle tables
Next Topic: The Subqury Returns So Many Rows
Goto Forum:
  


Current Time: Thu Mar 28 08:50:04 CDT 2024