Handling NULL values - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Handling NULL values

Author

Topic

Razin
Starting Member

3 Posts

Posted - 2009-10-22 : 05:55:52

Hello:
The following query returns values like below if both INTERNET AND WAP have values for a date:

INTERNET WAP
-----------------------
5000 4000

but if any of those contain NULL value for a date suppose WAP is NULL the whole row become NULL like this:

INTERNET WAP
----------------------

but I need the result as follows:

INTERNET WAP
-----------------------
5000

How can I achieve it?

select A.INTERNET, B.WAP
FROM
(Select ControlDate,
case
when sum(A.AMOUNT) IS NULL then 0
else sum(A.AMOUNT)
END AS INTERNET
FROM TABLE1
WHERE SubscriberType = 1
AND VASTYPE = 'INTERNET'
AND ControlDate = '10-OCT-2009'
group by ControlDate) A
Inner Join
(
Select ControlDate,
case
when sum(B.AMOUNT) IS NULL then 0
else sum(B.AMOUNT)
END AS WAP
FROM TABLE1
WHERE SubscriberType = 1
AND VASTYPE = 'WAP'
AND ControlDate = '10-OCT-2009'
group by ControlDate
) B
ON A.ControlDate = B.ControlDate

gaauspawcscwcj
Starting Member

29 Posts

Posted - 2009-10-22 : 06:21:30

select A.INTERNET, B.WAP
FROM
(Select ControlDate,
case ISNULL(A.AMOUNT,0)
when 0 then 0
else sum(A.AMOUNT)
END AS INTERNET
FROM TABLE1
WHERE SubscriberType = 1
AND VASTYPE = 'INTERNET'
AND ControlDate = '10-OCT-2009'
group by ControlDate) A
Inner Join
(
Select ControlDate,
case ISNULL(B.AMOUNT,0 )
when 0 then ''
else sum(B.AMOUNT)
END AS WAP
FROM TABLE1
WHERE SubscriberType = 1
AND VASTYPE = 'WAP'
AND ControlDate = '10-OCT-2009'
group by ControlDate
) B
ON A.ControlDate = B.ControlDate

Razin
Starting Member

3 Posts

Posted - 2009-10-25 : 00:29:48

Dear gaauspawcscwcj:

whenever I run this query it gives error like:

Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "A.AMOUNT" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "A.AMOUNT" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "B.AMOUNT" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "B.AMOUNT" could not be bound.

Please let me know what's the problem.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-10-26 : 09:10:32

try this one


select A.INTERNET, B.WAP
FROM
(Select ControlDate,
 sum(ISNULL(AMOUNT,0)) AS INTERNET
FROM TABLE1
WHERE SubscriberType = 1
AND VASTYPE = 'INTERNET'
AND ControlDate = '10-OCT-2009'
group by ControlDate) A
LEFT Join
(
Select ControlDate,
 sum(ISNULL(AMOUNT,0)) AS WAP
FROM TABLE1
WHERE SubscriberType = 1
AND VASTYPE = 'WAP'
AND ControlDate = '10-OCT-2009'
group by ControlDate
) B
ON A.ControlDate = B.ControlDate

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2009-10-26 : 19:15:11

SELECT SUM(CASE WHEN VASTYPE = 'INTERNET' THEN AMOUNT ELSE 0 END) AS INTERNET
,SUM(CASE WHEN VASTYPE = 'WAP' THEN AMOUNT ELSE 0 END) AS WAP
FROM TABLE1
WHERE SubscriberType = 1
AND VASTYPE IN ('INTERNET', 'WAP') -- assumes more than these two types exist
AND ControlDate = '20091010' -- using unambiguous date format

Razin
Starting Member

3 Posts

Posted - 2009-10-28 : 00:35:55

Hi jeffw8713:
Thank a lot. This is what I was looking for.

Hi bklr:
This query returns a row if any of the item returns NULL. But the problem is:

Suppose Internet is NULL and WAP is 1000. Dispaly should be like

Internet WAP
-------- ----
NULL 1000

But it shows:

Internet WAP
-------- ----
NULL NULL

Thanks again,
Razin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources