Link to home
Start Free TrialLog in
Avatar of Swaminathan K
Swaminathan KFlag for India

asked on

Need help on Query

Hi team,

I need an help on the query for the below requirement

Tables
Payment_master
pay_type_id number
Pay_id   Number
Post_date date
inv_no number
original_pay_id number
original_inv_no number
action_type varchar2(6)


Invoice_master
Inv_no number
inv_amt number(9,2)
inv_date date
pay_id number
inv_line_no number

Sample data :

Payment_master

customer_id |pay_type_id |Pay_id  |Post_date | inv_no |original_pay_id |original_inv_no |action_type
301|               101             | 1         | 01-Jan-2015| 1     | 0                      | 1                     | APP
301|                 102           |2           |02-FEB-2015 |2     |1                        |1                       |REV
301|               101             |3           |19-MAR-2015 | 2  |0                        |2                      |APP

Invoice_master_detail

Inv_no |inv_amt |inv_date  |pay_id | inv_lineno
1 |230               |01-JAN-2015|1 |1
2 |230                |02-MAR-2015|2|1
2 |340                |02-MAR-2015|3|2




I need to display an output as below :

Customer_id , Pay_type_id , Action_code  amt
301                 101               APP              230
301                  102              REV              230
301                  101              APP              340


I need help on the query .

Joining payment_master and Invoice_master is based on the original_inv_no


Select

p.customer_id , p.pay_type_id ,
p.action_type,
case when p.action_type='APP' then
(Select sum(amt) from invoice_master where inv_no = p.original_inv_no and pay_id=  p.pay_id)
else
(Select sum(amt) from invoice_master where inv_no = p.original_inv_no and pay_id=  p.pay_id)
end amt
payment_master p
where p.customer_id=301
group by p.customer_id , p.pay_type_id ,
p.action_type,


Iam not getting the proper results , any help is really appreciated. I need to remove the case statement and write a query with joins to get the output .
SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
awking,

That could be correct, but I was working on the assumption that the subquery in the original post was returning the correct result.
Avatar of Swaminathan K

ASKER

awesome