Adding a numeric keeps returning NULL

Question

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?

Your not adding StateFeeBegin and StateFeeEnd but StateFee instead. — ba__friend
– ba__friend, Commented Jul 21, 2011 at 17:01
Can you edit your question? Columns StateFeeBegin and StateFeeEnd are not defined in #Initial #End declarations. Also, the last query is missing commas after i.StateFeeBegin and e.StateFeeEnd — Ben Tennen
– Ben Tennen, Commented Jul 21, 2011 at 17:06

gbn · Accepted Answer · 2011-07-21 17:01:59Z

5

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.

answered Jul 21, 2011 at 17:01

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JonH · Accepted Answer · 2011-07-21 17:03:48Z

1

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.

answered Jul 21, 2011 at 17:03

JonH

33.2k13 gold badges93 silver badges149 bronze badges

Collectives™ on Stack Overflow

Adding a numeric keeps returning NULL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related