Swaminathan K
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 .
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
awesome
That could be correct, but I was working on the assumption that the subquery in the original post was returning the correct result.