Skip to Main Content

SQL & PL/SQL

Announcement

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!

Split amount with minimum amount per month

ProkopisSep 23 2021 — edited Sep 23 2021

Hi all, I have a table with a contract and it's payable amount.
I want to split amounts to 36 doses.

I am trying the following code :

WITH BASE_QUERY(CONTRACT_NUM, NUM_OF_DOSES, AMOUNT) AS (
SELECT '0423423423442', 36, 1300.12 FROM DUAL UNION ALL
SELECT '04238423442', 36, 412.22 FROM DUAL UNION ALL
SELECT '04238411111', 36, 120.69 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;

The problem I have is 
1. When I summary per contract I don't take as result the total_amount. For example for contract 0423423423442 I take as summary 1299,96 not 1300,12
This happens due to rounding. Is it possible to assign the difference to the last dose ?
2. Moreover, I want to have a minimum amount in each dose for example 100.
So, the doses in contract 04238411111 will be the first with 100 and the second with 20.69

Could you help me please ?
Thanks in advance

This post has been answered by Frank Kulash on Sep 23 2021
Jump to Answer

Comments

DannyS-Oracle
Answer

Answering my own question:

In the end I gave up using manual sql PIVOT and used the PIVOT feature provided from the Interactive Report's Action > Format > Pivot.

I decided to give up on retaining the "Pass" or "Fail" values since none of the aggregate functions (MIN, MAX, COUNT) support varchar type. So I changed the values to numbers and manually color-coded the cells based on the numbers using JavaScript (e.g. Green for "Pass" and Red for "Fail"). Looks OK for me.

Marked as Answer by DannyS-Oracle · Sep 27 2020
fac586

DannyS-Oracle wrote:

Hi guys, to illustrate my problem, I will use this screenshot:

Screen Shot 2016-10-07 at 1.06.40 PM.png

Currently I have the table on the right, and I want to create a report just like the table on the left. The first solution that came into my mind was using PIVOT, for example:

  1. select*from(
  2. select(service,area,resultfromsample_table
  3. )
  4. pivotxml(
  5. max(result)
  6. forarea
  7. in(selectareafromsample_table)
  8. )
  9. orderby"service"

select * from ( select (service, area, result from sample_table ) pivot xml ( max(result) for area in (select area from sample_table) ) order by "service"

But Oracle SQL does not support dynamic number of columns (for the IN keyword), and using XML keyword will return [unsupported data type] message on the report. Is there another approach to solve this problem?

P.S. I am using Apex v5.0.4 and DB v12.

UPDATE

When I was looking from older discussions, fac586 gave this accepted answer Re: Pivot query using XML option in APEX , mentioning serializing the XML and then shredding the XMLType in the report. This seems close to what I am looking for, but can someone please give more detailed step-by-step on how to shred the XMLType into the report? Thank you!

I created a demonstration of that in response to another thread:

Another option is to use a PL/SQL Function Body returning SQL Query report source with a dynamic pivot specification:

However, neither of those options is possible in an interactive report due to the dynamic number of columns involved (among other things), so if an interactive report is a requirement you'll have to stick to the built-in IR pivot feature.

1 - 2

Post Details

Added on Sep 23 2021
10 comments
84 views