Need help on Query

Hi team,

I need an help on the query for the below requirement

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)

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

Sample data :


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


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


p.customer_id , p.pay_type_id ,
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)
(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 ,

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 .
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
It appears your sample data does not match your expected result.  The row with the ACTION_CODE of REV should have a ORIGINAL_INV_NO of 2 in the PAYMENT_MASTER table.  Is that correct?  If so, then this would appear to give the result that you posted:
SELECT p.customer_id, 
       i.inv_amt amt 
FROM   payment_master p 
       join invoice_master i 
         ON i.inv_no = p.original_inv_no 
            AND i.pay_id = p.pay_id 
WHERE  p.customer_id = 301; 

Open in new window

I don't see the need for the SUM in your original query.  If you are trying to add some records together, then you need something else in your select and/or where because you will aggregate the first and last row in your sample output.

Also, not sure what the case was doing since both sides of the case statement had the same logic.
awking00Information Technology SpecialistCommented:
I think the issue is to join on invoice_master.inv_lineno = payment_master.original_inv_no

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:

That could be correct, but I was working on the assumption that the subquery in the original post was returning the correct result.
sam_2012Author Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.