Home » SQL & PL/SQL » SQL & PL/SQL » Complex results
Complex results [message #190547] Thu, 31 August 2006 04:34 Go to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi,

I have a query which returns me two rows , where one invoice is of STANDARD type and other is of CREDIT.

Only difference between the two invoices is, STANDARD invoice was a paid one which will have some value "Document Number" & "Payment Batch name" whereas the other transaction will have NULL values for the "Document Number" & "Payment btach name".

since we've retrieved the unpaid invoices using the outer join.

I would like to display the "Document Number" & "Payment btach name" of the paid invoice for the unpaid invoice also, instead of null value.

SELECT pv.vendor_name, ai.invoice_num,
ai.invoice_date, ai.pay_group_lookup_code,
ai.invoice_amount, ai.amount_paid,
ac.check_number "Document Number",
ac.checkrun_name "Payment Batch Name",
ai.invoice_type_lookup_code invoice_type
FROM ap_invoices_all ai,
po_vendors pv,
ap_invoice_payments_all ip,
ap_checks_all ac,
hr_all_organization_units haou
WHERE pv.vendor_id = ai.vendor_id
AND ip.invoice_id(+) = ai.invoice_id
AND ac.check_id(+) = ip.check_id
AND haou.organization_id = ai.org_id
-- AND ai.invoice_amount <> ai.amount_paid
AND ai.invoice_num(+) LIKE 'ICCT1000022'
AND ip.external_bank_account_id(+) IS NOT NULL
AND ac.status_lookup_code(+) <> 'VOIDED'
ORDER BY 2

The output would look like ,

Vendor| Invoice |InvDate |Pay|Inv |Paid|Doc|Pay |Inv.Type
Name | Num | |grp|amt |amt |Num|Batch|

ABC |ICCT1000022|12-Jun-06|101|1500|1500|123|Test |STANDARD
XYZ |ICCT1000022|12-Jun-06|450|1500| 0 |null|null|CREDIT

I want to display the Doc.Num & Pay Batch of the STANDARD invoice for the "credit" invoice instaead of null values.

Could any one throw some light on this??

Thanks
Safeeq

Re: Complex results [message #190597 is a reply to message #190547] Thu, 31 August 2006 06:22 Go to previous messageGo to next message
aorehek
Messages: 52
Registered: August 2006
Member
Use NVL function on null values.

SELECT pv.vendor_name, ai.invoice_num,
       ai.invoice_date, ai.pay_group_lookup_code,
       ai.invoice_amount, ai.amount_paid,
       nvl( ac.check_number, ai.check_number)  "Document Number",
       nvl( ac.checkrun_name , ai.batch_number) "Payment Batch Name",
       ai.invoice_type_lookup_code invoice_type
Re: Complex results [message #190607 is a reply to message #190597] Thu, 31 August 2006 06:53 Go to previous messageGo to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

ap_invoices "ai" table doesnt have the check_number only the ap_checks will have the check_number column.
Re: Complex results [message #190610 is a reply to message #190607] Thu, 31 August 2006 06:59 Go to previous message
aorehek
Messages: 52
Registered: August 2006
Member
I don't know column names in your tables. Just put instead of
nvl( ac.check_number, ai.check_number) and nvl( ac.checkrun_name , ai.batch_number) correct column names.

Previous Topic: MERGE
Next Topic: Reg : Rank Command
Goto Forum:
  


Current Time: Sat Dec 10 14:56:41 CST 2016

Total time taken to generate the page: 0.11344 seconds