## Forum Stats

• 3,836,844 Users
• 2,262,198 Discussions

Discussions

Member Posts: 23
edited Dec 8, 2009 5:40AM
Hi i need a query to perform the below operation
I have a table as shown below

invoice rec_no r_cost d_cost chg tax code
1 12345 114.35 100.30 1.00 1.00 234
2 12345 114.35 2.00 1.00 1.00 201
3 12345 114.35 14.00 1.00 1.00 600
4 23412 120.90 110.40 1.00 1.05 700
5 23412 120.90 12.50 1.00 1.05 900

From this table i need to find the adjustment for each rec_no as
(r_cost + chg +tax) -sum(d_cost)for rec_no=12345
(114.35 + 1.00+1.00) -(100.30+2.00+14.00)=0.05
This 0.05 should be added to the minimum(code) for the rec_no 12345.
so the d_cost of code, 201 with rec_no=12345 should be (2.00 + 0.05=2.05).

and,
(120.90 + 1.00+1.05) -(110.40+12.50)=0.05
This 0.05 should be added to the minimum(code) for the rec_no 23412.
so the d_cost of code, 700 with rec_no=23412 should be (110.40+ 0.05=110.45).

Kindly help me in writing a oracle query to perform the above operation.

• Member Posts: 1,072
``` with tab as (
select 1 invoice,12345 rec_no,114.35 r_cost,100.30 d_cost,1.00 chg,1.00 tax,234 code from dual union all
select 2 invoice,12345 rec_no,114.35 r_cost,2.00 d_cost,1.00 chg,1.00 tax,201 code from dual union all
select 3 invoice,12345 rec_no,114.35 r_cost,14.00 d_cost,1.00 chg,1.00 tax,600 code from dual union all
select 4 invoice,23412 rec_no,120.90 r_cost,110.40 d_cost,1.00 chg,1.05 tax,700 code from dual union all
select 5 invoice,23412 rec_no,120.90 r_cost,12.50 d_cost,1.00 chg,1.05 tax,900 code from dual
)
SELECT tab.*,
CASE
WHEN DENSE_RANK() OVER (partition BY rec_no ORDER BY code)=1
THEN r_cost + chg +tax-SUM(d_cost) over (partition BY rec_no)+MIN(d_cost) keep (DENSE_RANK FIRST
ORDER BY code) over (partition BY rec_no)
ELSE d_cost
FROM tab
ORDER BY invoice```
check this out...

Ravi Kumar

• Member Posts: 1,072
edited Nov 26, 2009 2:29AM
check this out....may be you require this
```with tab as (
select 1 invoice,12345 rec_no,114.35 r_cost,100.30 d_cost,1.00 chg,1.00 tax,234 code from dual union all
select 2 invoice,12345 rec_no,114.35 r_cost,2.00 d_cost,1.00 chg,1.00 tax,201 code from dual union all
select 3 invoice,12345 rec_no,114.35 r_cost,14.00 d_cost,1.00 chg,1.00 tax,600 code from dual union all
select 4 invoice,23412 rec_no,120.90 r_cost,110.40 d_cost,1.00 chg,1.05 tax,700 code from dual union all
select 5 invoice,23412 rec_no,120.90 r_cost,12.50 d_cost,1.00 chg,1.05 tax,900 code from dual
)
SELECT tab.*,
r_cost + chg +tax-SUM(d_cost) over (partition BY rec_no)+MIN(d_cost) keep (DENSE_RANK FIRST
ORDER BY code) over (partition BY rec_no) adj
FROM tab```
Ravi Kumar

Just formatted for better understanding

Edited by: ravikumar.sv on Nov 26, 2009 12:58 PM
• Member Posts: 23
Hi Ravi,
Thank you so much! Your query was really useful!
I want to make the result to be printed in a little different way.
The calculated adjustment amount should be added to the comm_cost whose comm_code is minimum only.
From the below query i get this result,
invoice rec_no r_cost d_cost chg tax code Adj
1 12345 114.35 100.30 1.00 1.00 234 2.05
2 12345 114.35 2.00 1.00 1.00 201 2.05
3 12345 114.35 14.00 1.00 1.00 600 2.05
4 23412 120.90 110.40 1.00 1.05 700 110.45
5 23412 120.90 12.50 1.00 1.05 900 110.45

Instead i want a result like this,
invoice rec_no r_cost d_cost chg tax code Adj
1 12345 114.35 100.30 1.00 1.00 234 100.30
2 12345 114.35 2.00 1.00 1.00 201 2.05
3 12345 114.35 14.00 1.00 1.00 600 14.00
4 23412 120.90 110.40 1.00 1.05 700 110.45
5 23412 120.90 12.50 1.00 1.05 900 12.50

Can this be done with oracle query? kindly guide me.
• Member Posts: 1,072
``` with tab as (
select 1 invoice,12345 rec_no,114.35 r_cost,100.30 d_cost,1.00 chg,1.00 tax,234 code from dual union all
select 2 invoice,12345 rec_no,114.35 r_cost,2.00 d_cost,1.00 chg,1.00 tax,201 code from dual union all
select 3 invoice,12345 rec_no,114.35 r_cost,14.00 d_cost,1.00 chg,1.00 tax,600 code from dual union all
select 4 invoice,23412 rec_no,120.90 r_cost,110.40 d_cost,1.00 chg,1.05 tax,700 code from dual union all
select 5 invoice,23412 rec_no,120.90 r_cost,12.50 d_cost,1.00 chg,1.05 tax,900 code from dual
)
SELECT tab.*,
CASE
WHEN DENSE_RANK() OVER (partition BY rec_no ORDER BY code)=1
THEN r_cost + chg +tax-SUM(d_cost) over (partition BY rec_no)+MIN(d_cost) keep (DENSE_RANK FIRST
ORDER BY code) over (partition BY rec_no)
ELSE d_cost
FROM tab
ORDER BY invoice```
check this out...

Ravi Kumar
• Member Posts: 23
Thank you so much Ravi
• Member Posts: 1,072 Bronze Badge
edited Dec 8, 2009 5:40AM
Hahaha I like OLAP usage like below
```with tab as (
select 1 invoice,12345 rec_no,114.35 r_cost,100.30 d_cost,1.00 chg,1.00 tax,234 code from dual union all
select 2 invoice,12345 rec_no,114.35 r_cost,2.00 d_cost,1.00 chg,1.00 tax,201 code from dual union all
select 3 invoice,12345 rec_no,114.35 r_cost,14.00 d_cost,1.00 chg,1.00 tax,600 code from dual union all
select 4 invoice,23412 rec_no,120.90 r_cost,110.40 d_cost,1.00 chg,1.05 tax,700 code from dual union all
select 5 invoice,23412 rec_no,120.90 r_cost,12.50 d_cost,1.00 chg,1.05 tax,900 code from dual)
SELECT tab.*,
CASE
WHEN code = min(code) OVER (partition BY rec_no)
--DENSE_RANK() OVER (partition BY rec_no ORDER BY code)=1
THEN r_cost + chg +tax
-SUM(d_cost) over(partition BY rec_no
order by code rows between 1 following
and unbounded following)
FROM tab
ORDER BY invoice;

INVOICE  REC_NO  R_COST  D_COST  CHG   TAX  CODE     ADJ
-------  ------  ------  ------  ---  ----  ----  ------
1   12345  114.35   100.3    1     1   234   100.3
2   12345  114.35       2    1     1   201    2.05
3   12345  114.35      14    1     1   600      14
4   23412   120.9   110.4    1  1.05   700  110.45
5   23412   120.9    12.5    1  1.05   900    12.5```
DB2 allows order by code rows 1 following.
It means order by code rows between 1 following and unbounded following.
Does Oracle11gR2 allow above usage?
This discussion has been closed.