SQLTeam.com | Weblogs | Forums

Need help with minus command

#1

Hello,
So I have put in this input.
Select account_name, credit_amounts-debit_amounts income_stmt
from coa
where account_number between 4000 and 7000

It spits out a two column chart with all the account names filled in but it does not fill in the numbers for the one positive credit amount i have and the negative debit amounts I have. I am trying to create a single step income statement.

when i do however do something such as
select account_name, debit_amounts income_stmt
from coa
where account_number between 4000 and 7000

it fills in the numbers for the debit amount
is there something wrong with my credit_amounts-debit_amounts income_stmt command?

#2

Perhaps the [credit_amounts] and [debit_amounts] columns are NULL (rather than ZERO) were there is no value?

Anything - NULL
or
NULL - Anything

will be NULL.

Try this perhaps?

Select account_name, COALESCE(credit_amounts, 0.0) - COALESCE(debit_amounts, 0.0) AS income_stmt
#3

it worked thank you sql god! But thats weird I must of made a mistake on my chart of accounts?

#4

Sounds like those two columns are storing NULL instead of an actual value.

NULL is a good value to store for "unknown". I can;t make my mind up whether, if I was writing an accounting system, I would force credit_amounts and debit_amounts to always have a value (e.g. "0.0"), or whether I would allow them to be NULL.

I'm not an expert on account, but I'll guess that it is wrong for them BOTH to be NULL ... and maybe it is also impossible for them both to have a value? So on that basis I think NULL is useful as it allows you to detect that neither/both are NULL ... and raise an error to the user.

Whereas if both always contain values, and both are 0.0 what does that tell you? Nothing really ... they could both, wrongly, have "No value" ...

You could still detect that both were <> 0.0 and raise an error for that, but I don't think you could detect "Failed to give either a Credit or a Debit value".

So on balance I can talk myself into allowing NULLs in those columns is GOOD!

However, it does mean that you need to make allowance for when they are NULL in your calculations. If you have to do that often then you could create a VIEW

CREATE VIEW coa_VIEW
AS
SELECT account_name, 
       ... List All Other Columns ...
       COALESCE(credit_amounts, 0.0) AS [credit_amounts],
       COALESCE(debit_amounts, 0.0) AS [debit_amounts}
FROM coa

and then anytime you want "guaranteed numeric values" from the [coa] table just query the VIEW instead of the underlying table.