Home » SQL & PL/SQL » SQL & PL/SQL » Query to create data instead of storing it (12.1.0.1.0)
Query to create data instead of storing it [message #685912] Mon, 25 April 2022 05:09 Go to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi All,



I have the following case:
- Each year a certain amount that presents the loans given by an organization for this year. So there is loan amount for Year 1 (LoanYear1), loan amount for year 2 (LoanYear2)…etc.
create table test_dsv_loanYear 
  (
    LoanYear number(2) primary key,
    Amount number(15,3) not null 
  );

insert all 
  into TEST_DSV_LOANYEAR(loanyear, amount) values (1, 120)
  into TEST_DSV_LOANYEAR(loanyear, amount) values (2, 100)
  into TEST_DSV_LOANYEAR(loanyear, amount) values (3, 150)
  into TEST_DSV_LOANYEAR(loanyear, amount) values (4, 175)
  into TEST_DSV_LOANYEAR(loanyear, amount) values (5, 200)
  into TEST_DSV_LOANYEAR(loanyear, amount) values (6, 200)
  into TEST_DSV_LOANYEAR(loanyear, amount) values (7, 200)
  into TEST_DSV_LOANYEAR(loanyear, amount) values (8, 250)
  into TEST_DSV_LOANYEAR(loanyear, amount) values (9, 250)
  into TEST_DSV_LOANYEAR(loanyear, amount) values (10, 300)
select * from dual;
- There is a pattern for loan withdrawals over 5 years. So all loans are expected to start withdrawal by 12% on year 2, then 30% on year 3 ..etc.
create table test_dsv_loanCon_Param
  (
    ParamerterID number(3) primary key,
    ParameterName varchar2(30),
    ParameterValue Number(20)
  );

insert all 
  into test_dsv_loanCon_Param(ParamerterID, ParameterName, ParameterValue) values(1, 'Y1 Withdrawal %', 0)
  into test_dsv_loanCon_Param(ParamerterID, ParameterName, ParameterValue) values(2, 'Y2 Withdrawal %', 12)
  into test_dsv_loanCon_Param(ParamerterID, ParameterName, ParameterValue) values(3, 'Y3 Withdrawal %', 30)
  into test_dsv_loanCon_Param(ParamerterID, ParameterName, ParameterValue) values(4, 'Y4 Withdrawal %', 43)
  into test_dsv_loanCon_Param(ParamerterID, ParameterName, ParameterValue) values(5, 'Y5 Withdrawal %', 15)
select * from dual;
- So in order to get the expected withdrawals for each year the following table has been created containing:
o Year_Num: the future year number
o LoanYear: which loan year amount is given in this year.
o LoanYearCount: the number of years this loan has been withdrawn.
create table test_dsv_YearLoanData
  (
    ser number primary key,
    Year_Num number(2),
  	LoanYear number(2),
    LoanYearCount number(2)
  );
Insert all 
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(1,1,1,1)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(2,2,2,1)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(3,2,1,2)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(4,3,3,1)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(5,3,2,2)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(6,3,1,3)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(7,4,4,1)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(8,4,3,2)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(9,4,2,3)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(10,4,1,4)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(11,5,5,1)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(12,5,4,2)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(13,5,3,3)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(14,5,2,4)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(15,5,1,5)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(16,6,5,2)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(17,6,4,3)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(18,6,3,4)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(19,6,2,5)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(20,6,1,6)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(21,7,5,3)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(22,7,4,4)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(23,7,3,5)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(24,7,2,6)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(25,7,1,7)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(26,8,5,4)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(27,8,4,5)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(28,8,3,6)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(29,8,2,7)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(30,8,1,8)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(31,9,5,5)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(32,9,4,6)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(33,9,3,7)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(34,9,2,8)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(35,9,1,9)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(36,10,5,6)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(37,10,4,7)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(38,10,3,8)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(39,10,2,9)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(40,10,1,10)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(41,11,5,7)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(42,11,4,8)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(43,11,3,9)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(44,11,2,10)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(45,12,5,8)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(46,12,4,9)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(47,12,3,10)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(48,13,5,9)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(49,13,4,10)
into test_dsv_YearLoanData(ser, Year_Num, LoanYear, LoanYearCount) values(50,14,5,10)
select * from dual;

From the above table it’s possible to calculate the withdrawal per year and get more complex calculations such as predicting interest rate…etc.
The idea behind having to store data in est_dsv_YearLoanData is to record a logic sequence for the behavior of each loan year amount. For example, Year 1 (year_Num) will only have Loan 1 active (LoanYear) and this will be the first time this loan has been withdrawn from (LoanYearCount). For Year 2, there will be both Loan 1 and Loan 2 active, but loan 1 will be active for the second year while Loan 2 will be active for the first year (Loan Year Count). After year 5, Loan 1 will be fully withdrawn and will not appear in the list and so on.

The issue with this technique is that it must create and store values in test_dsv_YearLoanData and confines the ability to change the number of withdrawal years for loans as this will require recreating the table test_dsv_YearLoanData again.
What I need help in is how to write a query to get table test_dsv_YearLoanData as a function of the number of withdrawal years per loan, and the amount given as loans for each year.
 insert into test_dsv_loanCon_Param(ParamerterID, ParameterName, ParameterValue) values(10, 'Loan Withdrawal years', 5);
Thanks,
Ferro

[Updated on: Tue, 26 April 2022 01:48]

Report message to a moderator

Re: Query to create data instead of storing it [message #685916 is a reply to message #685912] Tue, 26 April 2022 05:31 Go to previous message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
It seems that my case is not clear, in short what I need is a query to generate the data inserted in test_dsv_YearLoanData.
I have read the row generation post that is pinned in the forum but couldn't find something close.
Previous Topic: How to append a header on the top of the CSV file
Next Topic: Remove unwanted characters
Goto Forum:
  


Current Time: Fri Mar 29 02:17:15 CDT 2024