Calculate with some NULL fields? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculate with some NULL fields?

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-06-29 : 13:00:36

I'm a little confuse. I have some fields that have "NULL" value, and when I try to calculate it with a non NULL field, it just gives me "NULL". Anyone know why it might be doing this?

This query is pulling from database:



SELECT 	W.ID,
	WL.WAREHOUSE_ID
	CASE WHEN WAREHOUSE_ID = 'CBS' THEN SUM(QTY) ELSE 0 END AS CBS_QTY,
	CASE WHEN WAREHOSUE_ID = 'SHELF' THEN SUM(QTY) ELSE 0 END AS SHELF_QTY,
	WL.DESCRIPTION,
	RECEIVED.CBS_QTY_1,
	RECEIVED.SHELF_QTY_1

FROM 	WAREHOUSE W INNER JOIN WAREHOUSE_LOCATION WL
	ON W.ID = WL.PART_ID

	LEFT OUTER JOIN
	(SELECT CASE WHEN WAREHOUSE_ID = 'CBS' THEN SUM(QTY) ELSE 0 END AS CBS_QTY_1,
		CASE WHEN WAREHOUSE_ID = 'SHELF' THEN SUM(QTY) ELSE 0 END AS SHELF_QTY_1,
		W.ID, WL.WAREHOUSE_ID
	FROM 	WAREHOUSE W INNER JOIN WAREHOUSE_LOCATION WL
		ON W.ID = WL.PART_ID
	WHERE 	ID BETWEEN @SID AND @EID
		AND TYPE = 'O'	
		AND CLASS = 'R'
	GROUP BY ID, WAREHOUSE_ID) RECEIVED ON W.ID = RECEIVED.ID
		AND WL.WAREHOUSE_ID = RECEIVED.WAREHOUSE_ID

WHERE ID BETWEEN @SID AND @EID
	AND TYPE = 'O'
	AND CLASS = 'I'
GROUP BY W.ID, WL.WAREHOUSE_ID, WL.DESCRIPTION

Here's the result that I get which is correct:


ID	DESCRIPTION	CBS QTY		SHELF QTY	CBS_QTY_1	SHELF_QTY_1
232	ABS2330		0		10		NULL		1
343	TR3432		10		0		1		NULL
321	BS2200		0		60		NULL		NULL	
332	RPB2321		1		0		1		NULL

Now what I want to do is that any where there is a value for CBS_QTY_1 or SHELF_QTY_1, I want it to calculate to the correspond field CBS_QTY or SHELF_QTY.

Here's my query:



SELECT 	W.ID,
	WL.WAREHOUSE_ID
	CASE WHEN WAREHOUSE_ID = 'CBS' AND RECEIVED.CBS_QTY_1 IS NOT NULL
	THEN SUM(QTY) ELSE SUM(QTY) - RECEIVED.CBS_QTY_1 END AS CBS_QTY,
	
	CASE WHEN WAREHOSUE_ID = 'SHELF' AND RECEIVED.SHELF_QTY_1 IS NOT NULL 
	THEN SUM(QTY) ELSE SUM(QTY) - RECEIVED.SHELF_QTY_1 END AS SHELF_QTY,

	WL.DESCRIPTION

FROM 	WAREHOUSE W INNER JOIN WAREHOUSE_LOCATION WL
	ON W.ID = WL.PART_ID

	LEFT OUTER JOIN
	(SELECT CASE WHEN WAREHOUSE_ID = 'CBS' THEN SUM(QTY) ELSE 0 END AS CBS_QTY_1,
		CASE WHEN WAREHOUSE_ID = 'SHELF' THEN SUM(QTY) ELSE 0 END AS SHELF_QTY_1,
		W.ID, WL.WAREHOUSE_ID
	FROM 	WAREHOUSE W INNER JOIN WAREHOUSE_LOCATION WL
		ON W.ID = WL.PART_ID
	WHERE 	ID BETWEEN @SID AND @EID
		AND TYPE = 'O'	
		AND CLASS = 'R'
	GROUP BY ID, WAREHOUSE_ID) RECEIVED ON W.ID = RECEIVED.ID
		AND WL.WAREHOUSE_ID = RECEIVED.WAREHOUSE_ID

WHERE ID BETWEEN @SID AND @EID
	AND TYPE = 'O'
	AND CLASS = 'I'
GROUP BY W.ID, WL.WAREHOUSE_ID, WL.DESCRIPTION

Here's the result that I got and not what I wanted:


ID	DESCRIPTION	CBS QTY		SHELF QTY	
232	ABS2330		NULL		NULL
343	TR3432		NULL		NULL
321	BS2200		0		60
332	RPB2321		NULL		NULL

I'm after this result:


ID	DESCRIPTION	CBS QTY		SHELF QTY	
232	ABS2330		0		9		
343	TR3432		9		0		
321	BS2200		0		60			
332	RPB2321		0		0

Can anyone see why or what I did wrong with my second query example? Any help is greatly appreciated.

dsdeming

479 Posts

Posted - 2005-06-29 : 13:32:55

See ISNULL or COALESCE in BOL.

Dennis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources