For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
Cisco ASDM issues with 8u72
While testing u72 I noticed that Cisco ASDM does not load and give error referencing line 72-ea
Hi, @prokopis Thanks for posting the sample data. Don't forget to post the exact results you want from the given sample data, an explain, step by step, how you get those results. Always give your full Oracle version (e.g. 18.4.0.0.0). I want to split amounts to 36 doses. Does that mean the 3 rows of sample data will produce 3 * 36 = 108 row of results? To avoid posting all 108 rows, you may want to use an example where you only need to split amounts into 3 doses. Just make it clear that you need 36 doses in your real problem; you'll get answers that can easily be changed.
Thanks for your comment I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit I give below a sample with 3 doses
WITH BASE_QUERY(CONTRACT_NUM, NUM_OF_DOSES, AMOUNT) AS ( SELECT '0423423423442', 3, 1300.12 FROM DUAL UNION ALL SELECT '04238423442', 3, 412.22 FROM DUAL ) ,DATA (CONTRACT_NUM, NUM_OF_DOSES, AMOUNT, ITER, MONTH_AMOUNT) AS (SELECT CONTRACT_NUM, NUM_OF_DOSES , AMOUNT, 1, ROUND(AMOUNT/NUM_OF_DOSES,2) FROM BASE_QUERY UNION ALL SELECT CONTRACT_NUM, NUM_OF_DOSES, AMOUNT, ITER+1, MONTH_AMOUNT FROM DATA WHERE ITER+1 <= NUM_OF_DOSES) SELECT CONTRACT_NUM, NUM_OF_DOSES, ITER AS CONTRACT_DOSE_ITER, ADD_MONTHS(TO_DATE('25/09/2021','dd/mm/yyyy'), ITER) AS MONTH_DATE, AMOUNT AS TOTAL_AMOUNT, MONTH_AMOUNT FROM DATA ORDER BY CONTRACT_NUM, CONTRACT_DOSE_ITER;
I get as result But supposing a minimum amount 500 and fixing decimals to the last dose I want to get
WITH BASE_QUERY(CONTRACT_NUM, NUM_OF_DOSES, AMOUNT) AS ( SELECT '0423423423442',36,1300.12 FROM DUAL ) SELECT CONTRACT_NUM, NUM_OF_DOSES, AMOUNT, ITER, CASE ITER WHEN NUM_OF_DOSES THEN AMOUNT - ROUND(AMOUNT/NUM_OF_DOSES,2) * (NUM_OF_DOSES - 1) ELSE ROUND(AMOUNT/NUM_OF_DOSES,2) END MONTH_AMOUNT FROM BASE_QUERY, LATERAL( SELECT LEVEL ITER FROM DUAL CONNECT BY LEVEL <= NUM_OF_DOSES ) / CONTRACT_NUM NUM_OF_DOSES AMOUNT ITER MONTH_AMOUNT ------------- ------------ ---------- ---------- ------------ 0423423423442 36 1300.12 1 36.11 0423423423442 36 1300.12 2 36.11 0423423423442 36 1300.12 3 36.11 0423423423442 36 1300.12 4 36.11 0423423423442 36 1300.12 5 36.11 0423423423442 36 1300.12 6 36.11 0423423423442 36 1300.12 7 36.11 0423423423442 36 1300.12 8 36.11 0423423423442 36 1300.12 9 36.11 0423423423442 36 1300.12 10 36.11 0423423423442 36 1300.12 11 36.11 0423423423442 36 1300.12 12 36.11 0423423423442 36 1300.12 13 36.11 0423423423442 36 1300.12 14 36.11 0423423423442 36 1300.12 15 36.11 0423423423442 36 1300.12 16 36.11 0423423423442 36 1300.12 17 36.11 0423423423442 36 1300.12 18 36.11 0423423423442 36 1300.12 19 36.11 0423423423442 36 1300.12 20 36.11 0423423423442 36 1300.12 21 36.11 0423423423442 36 1300.12 22 36.11 0423423423442 36 1300.12 23 36.11 0423423423442 36 1300.12 24 36.11 0423423423442 36 1300.12 25 36.11 0423423423442 36 1300.12 26 36.11 0423423423442 36 1300.12 27 36.11 0423423423442 36 1300.12 28 36.11 0423423423442 36 1300.12 29 36.11 0423423423442 36 1300.12 30 36.11 0423423423442 36 1300.12 31 36.11 0423423423442 36 1300.12 32 36.11 0423423423442 36 1300.12 33 36.11 0423423423442 36 1300.12 34 36.11 0423423423442 36 1300.12 35 36.11 0423423423442 36 1300.12 36 36.27 36 rows selected. SQL>
SY.
Hi, Prokopis This happens due to rounding. Is it possible to assign the difference to the last dose ? So, for all iters except the last one, you want month_amount to be ROUND (amount / num_of_doses, 2), For the highest value of iter, you want month_amount to be whatever it takes to make the total of all month_amounts to be exactly amount; is that right? Here's one way to do that:
WITH data (contract_num, num_of_doses, amount, iter, month_amount) AS ( SELECT contract_num, num_of_doses , amount, 1, ROUND (amount / num_of_doses, 2) FROM base_query UNION ALL SELECT contract_num, num_of_doses, amount, iter + 1 , CASE WHEN iter + 1 < num_of_doses THEN month_amount ELSE amount - ( (num_of_doses - 1) * month_amount ) END FROM data WHERE iter + 1 <= num_of_doses ) SELECT contract_num , num_of_doses , iter AS contract_dose_iter , ADD_MONTHS ( TO_DATE ('25/09/2021','dd/mm/yyyy') , iter ) AS month_date , amount AS total_amount , month_amount FROM data ORDER BY contract_num , contract_dose_iter ;
Results (with num_of_doses changed to 3 on all rows):
CONTRACT_NUM NUM_OF_DOSES CONTRACT_DOSE_ITER MONTH_DATE TOTAL_AMOUNT MONTH_AMOUN ------------- ------------ ------------------ ----------- ------------ ------------ 0423423423442 3 1 25-Oct-2021 1300.12 433.37 0423423423442 3 2 25-Nov-2021 1300.12 433.37 0423423423442 3 3 25-Dec-2021 1300.12 433.38 04238411111 3 1 25-Oct-2021 120.69 40.23 04238411111 3 2 25-Nov-2021 120.69 40.23 04238411111 3 3 25-Dec-2021 120.69 40.23 04238423442 3 1 25-Oct-2021 412.22 137.41 04238423442 3 2 25-Nov-2021 412.22 137.41 04238423442 3 3 25-Dec-2021 412.22 137.4
Here is how I would do it. Note that this is a direct computation in closed form, rather than an iterative computation; so I can use a hierarchical (CONNECT BY) query instead of a recursive one. Also using the LATERAL join syntax, available since Oracle 12. The condition on LEVEL in the lateral subquery could be written more compactly (dividing AMOUNT by PRELIM_MONTH_AMOUNT ) but that may cause rounding errors and the wrong number of doses. I changed the number of doses to 12 so the output is not too long. I allow the number of doses and the minimum amount per month to depend on the contract (neither 12 nor 100 are hard-coded); if instead they should be constant across all rows (but still user inputs - like 36 and 110 for all contracts, instead of 36 and 100, or 12 and 100), you will have to modify this slightly to accept those as inputs, instead of being part of the table data.
CONNECT BY
LATERAL
LEVEL
AMOUNT
PRELIM_MONTH_AMOUNT
with base_query (contract_num, num_of_doses, amount, min_amount) as ( select '0423423423442', 12, 1300.12, 100 from dual union all select '04238423442' , 12, 412.22, 100 from dual union all select '04238411111' , 12, 120.69, 100 from dual ) , prep (contract_num, num_of_doses, amount, min_amount, prelim_month_amount) as ( select contract_num, num_of_doses, amount, min_amount, greatest(100, round(amount / num_of_doses, 2)) from base_query ) select p.contract_num, l.iter, case l.isleaf when 0 then p.prelim_month_amount else p.amount - (l.iter - 1) * p.prelim_month_amount end as month_amount from prep p cross join lateral ( select level as iter, connect_by_isleaf as isleaf from dual connect by (level - 1) * p.prelim_month_amount <= p.amount and level <= p.num_of_doses ) l ; CONTRACT_NUM ITER MONTH_AMOUNT ------------- ---------- ------------ 0423423423442 1 108.34 0423423423442 2 108.34 0423423423442 3 108.34 0423423423442 4 108.34 0423423423442 5 108.34 0423423423442 6 108.34 0423423423442 7 108.34 0423423423442 8 108.34 0423423423442 9 108.34 0423423423442 10 108.34 0423423423442 11 108.34 0423423423442 12 108.38 04238423442 1 100 04238423442 2 100 04238423442 3 100 04238423442 4 100 04238423442 5 12.22 04238411111 1 100 04238411111 2 20.69
While I was working on the problem, you posted a Reply in which you said your Oracle version is 11. Please include your version with all your questions - it's much more relevant than most people think. The LATERAL clause is not supported in your version. So you will need to modify the query I offered as shown below.
select contract_num, level as iter, case connect_by_isleaf when 0 then prelim_month_amount else amount - (level - 1) * prelim_month_amount end as month_amount from prep connect by (level - 1) * prelim_month_amount <= amount and level <= num_of_doses and prior contract_num = contract_num and prior sys_guid() is not null ;
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit OUCH! That is completely out of support.
Hi, Prokopis I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Then LATERAL or CROSS APPLY won't help you; those were new features in Oracle 12.1. But supposing a minimum amount 500 and fixing decimals to the last dose I want to get Okay, now I see what you want. Here's one way to do it:
WITH data (contract_num, num_of_doses, amount, iter, month_amount, amount_left) AS ( SELECT contract_num, num_of_doses , amount, 0 , GREATEST ( ROUND (amount / num_of_doses, 2) , :min_amount ) , amount FROM base_query UNION ALL SELECT contract_num, num_of_doses, amount, iter + 1 , month_amount , amount_left - month_amount FROM data WHERE iter + 1 <= num_of_doses ) SELECT contract_num , num_of_doses , iter AS contract_dose_iter , ADD_MONTHS ( TO_DATE ('25/09/2021','dd/mm/yyyy') , iter ) AS month_date , amount AS total_amount , CASE WHEN amount_left > 0 AND iter < num_of_doses THEN month_amount ELSE amount_left + month_amount END AS month_amount --, amount_left -- FOR DEBUGGING ONLY FROM data WHERE iter > 0 AND amount_left + month_amount >= 0 ORDER BY contract_num , contract_dose_iter ;
The query above uses a bind variable (:min_amount). You could use any other technique to get the appropriate value into the query. Since it's only used in one place, you could hard-code it.
Thanks a lot for all you answers !!
I did not know about Lateral function. I will search it. Thanks again
I see that you chose an answer based on recursive WITH clause as the "accepted solution". In my replies, I suggested a solution using CONNECT BY, which does all the computations in closed form rather than relying on recursive calculations. This should result in faster execution times. I set out to see if that's true - and if it is, how much faster (since small differences may not matter). For testing I used my second solution, where I am not using the LATERAL clause. It is the solution that you can use in Oracle 11.2. The answer is - if you have only 1000 contracts to work on, the CONNECT BY query is 2-3 times faster, but that may not matter to you, since execution times are short: about 0.15 to 0.2 seconds for CONNECT BY vs. 0.5 seconds even for the slower, recursive WITH clause approach. As expected, the difference becomes greater with more data. When I tested on a table with 10,000 rows (do you have that many contracts?) the CONNECT BY query takes 0.5 seconds, while the recursive WITH solution that you accepted takes 9 seconds. That (as anyone can calculate) is 18 times slower. Is that OK for your purposes? Obviously, the best way to decide, if you are going to spend time on this, is to test on your own data. For what it's worth, the CONNECT BY + LATERAL solution (available only from version 12.1 on) runs in less than 0.3 seconds for the same 10,000 contracts. Faster, but still the same order of magnitude. Going from recursive WITH to CONNECT BY, even without LATERAL, is what will make by far the biggest difference.
WITH
As an aside, as I was testing, I noticed that Mr. Kulash's query isn't 100% correct. When the amount is an exact multiple of the "minimum amount" (let's say 100), and smaller than that minimum amount times the number of doses (example: min amount 100, num of doses 36, but total amount 1200), the query will produce one extra row, for an unneeded period (month), with a monthly amount of 0. Of course, that can be fixed easily; just letting you know so you can take a look at it and fix it.