CODEWITHSUNDEEP
sqlsql-server
mameesh
mameesh

Reputation: 3761

Adding a numeric keeps returning NULL

I have 2 temp tables that I am populating with a numeric value:

CREATE TABLE #Initial
(
[SalesTax] NUMERIC (20,4),
[Unit] VARCHAR(10)
);

INSERT INTO #Initial ([SalesTax],[Unit])
(
SELECT SUM(SalesTax), Unit
FROM Billing
GROUP BY Unit
);

CREATE TABLE #End
(
[SalesTax] NUMERIC (20,4),
[Unit] VARCHAR(10)
);

INSERT INTO #End([SalesTax],[Unit])
(
SELECT SUM(SalesTax), Unit
FROM BillingEnd
GROUP BY Unit
);

Then I am trying to add these values together when I select them in another query(This query is a very simple example of a much bigger query. It has to be done like this):

SELECT 
u.Unit,
i.SalesTax,
e.SalesTax,
(i.SalesTax+ e.SalesTax) AS SalesTaxFeeTotal
FROM tblUnit u
LEFT JOIN #Initial i ON u.Unit = i.Unit
LEFT JOIN #End e ON u.Unit = e.Unit

For some reason the SalesTax column is coming back as NULL when the i.SalesTax and e.SalesTax column are returning values for those same records.

There is no NULL values in either temp table. If there is no fee, it is 0.00

Why are these retuning nulls?

Upvotes: 1

Views: 398

Answers (2)

JonH
JonH

Reputation: 33183

You select StateFeeBegin and StateFeeEnd but you add two columns called StateFee from Initial and End. Use ISNULL(YourField, 0) to replace any null occurrence with 0.

Upvotes: 1

gbn
gbn

Reputation: 432621

Change this

(i.StateFee + e.StateFee) AS StateFeeTotal

to 

(ISNULL(i.StateFee,0) + ISNULL(e.StateFee,0)) AS StateFeeTotal

Or with maybe correct column name

(ISNULL(i.StateFeeBegin,0) + ISNULL(e.StateFeeEnd,0)) AS StateFeeTotal

With the LEFT JOIN, you can get NULLs for the values. NULL added to anything else will always be NULL. This answer uses ISNULL to replace NULL vales.

Upvotes: 5

Related Questions