Query Question 2

[shannanl]

I have a query that returns several items. It works fine except for one thing. One of the tables is called Accounts_payable_master_T and one is called AP_transaction_T. I take the Orig_balance field from the Accounts_payable_master_T and then I sum the payments found in the AP_transaction_T.Entry_amount as PAYMENTS and then I subtract those to find the Remaining balance as REMAINING. If there are no entries in the AP_transaction_T then I get nothing. As long as one entry exists, it returns the data. I believe I need to tell it if the sum is null to return zero but I am not sure.

Any help would be greatly appreciated.

Shannan


SELECT Vendor_T.V_name, Vendor_T.V_number, Accounts_payable_master_T.Date_entered, Accounts_payable_master_T.Orig_balance, Accounts_payable_master_T.Due_date, Accounts_payable_master_T.Comments, Accounts_payable_master_T.Account_num, Accounts_payable_master_T.Entry_id, Sum(AP_transaction_T.Entry_amount) AS PAYMENTS, Orig_balance - Sum(AP_transaction_T.Entry_amount) as REMAINING FROM (Vendor_T INNER JOIN Accounts_payable_master_T ON Vendor_T.Entry_id = Accounts_payable_master_T.Vendor_id) INNER JOIN AP_transaction_T ON Accounts_payable_master_T.Entry_id = AP_transaction_T.AP_id WHERE [Accounts_payable_master_T].[Account_status] = 'O' GROUP BY Vendor_T.V_name, Vendor_T.V_number, Accounts_payable_master_T.Date_entered, Accounts_payable_master_T.Orig_balance, Accounts_payable_master_T.Due_date, Accounts_payable_master_T.Comments, Accounts_payable_master_T.Account_num, Accounts_payable_master_T.Entry_id ORDER BY Vendor_T.V_name

 

[jbenson001]

Try using Left Outer Joins, and display 0 if the sum is null.

 

[sharkette11]

You need to left outer join the table that can contains no rows sometimes and then use COALESCE or ISNULL to make the value zero if it's null.

Something like:

SELECT ...., Orig_balance - Sum(ISNULL(AP_transaction_T.Entry_amount,0)) as REMAINING
FROM (Vendor_T
INNER JOIN Accounts_payable_master_T ON Vendor_T.Entry_id = Accounts_payable_master_T.Vendor_id)
LEFT OUTER JOIN AP_transaction_T ON Accounts_payable_master_T.Entry_id = AP_transaction_T.AP_id
WHERE ...
GROUP ...

 

[shannanl]

That worked greate. Thanks guys for the help.

Shannan

 

[jbenson001]

Glad to help... Jim