null issue - 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

General SQL Server Forums

New to SQL Server Programming

null issue

Author

Topic

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-12-05 : 09:44:00

i have a table that has 5 columns each column has some values in it those values also hold some NULL. how would i write code to say if NULL in all 4 columns dont show on my list.

CASE WHEN sixty < 0 THEN NULL ELSE sixty END AS sixty, CASE WHEN ninety < 0 THEN NULL ELSE ninety END AS ninety, CASE WHEN onetwenty < 0 THEN NULL ELSE onetwenty END AS onetwenty, CASE WHEN over120 < 0 THEN NULL ELSE over120 END AS over120, kcustnum

FROM dbo.Delinquencyview1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-05 : 09:46:25

WHERE not (col1 is null and col2 is null and col3 is null and col4 is null)

E 12°55'05.63"
N 56°04'39.26"

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-12-05 : 09:53:50

WHERE NOT (sixty IS 'null' AND ninety IS 'null' AND onetwenty IS 'null' AND over120 IS 'null')

some reason this is not working

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-05 : 10:15:09

Of course not. You are not checking against NULL values. You are checking against value that are "null". Strings.

WHERE NOT (sixty IS null AND ninety IS null AND onetwenty IS null AND over120 IS null)

E 12°55'05.63"
N 56°04'39.26"

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-12-05 : 11:01:41

that does not work it creates an or statement.

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-12-05 : 11:02:45

and it still does not remove the ones that have all NULL's for.
Any other ideas

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-12-05 : 11:03:11

when i place your code in this is what it does to it when i run to see the results

WHERE (NOT (sixty IS NULL)) OR
(NOT (ninety IS NULL)) OR
(NOT (onetwenty IS NULL)) OR
(NOT (over120 IS NULL))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-05 : 11:09:18

Try this (there is a difference between SQL Server 2000 and SQL Server 2005 and you havenä't told us which you use).

WHERE sixty > 0 AND ninety > 0 AND onetwenty > 0 AND over120 > 0

E 12°55'05.63"
N 56°04'39.26"

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-12-05 : 11:34:27

sorry i should have told you it was sql server 2005 also that code does not work it removes all nulls i dont want that. Here is example of what the is displayed.

sixty, ninety, onetwenty, and over120
Null, 21.21, NULL, NULL
NULL, NULL, NULL, NULL
43, 123, NULL, NULL

All i want the code to do is when sixty, ninety, onetwenty and over120 all say null remove that line. The other lines should stay even though they have null in them.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-05 : 11:53:42

WHERE (sixty > 0 or ninety > 0 or onetwenty > 0 or over120 > 0)

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 12:20:35

[code]WHERE (sixty + ninety + onetwenty + over120) IS NOT NULL [/code]

under default settings

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-05 : 12:31:06

quote:
WHERE (sixty + ninety + onetwenty + over120) IS NOT NULL

That's incorrect surely. The OP said he only wants line removed where everything is NULL

(10 + NULL + 2 + 0) IS NULL so wouldn't be brought back but
(NULL + NULL + NULL + NULL) IS NULL and also wouldn't be brought back.

OP wants to find the care where they are all NULL?

TEST CASE


DECLARE @test TABLE (
	[a] INT
	, [b] INT
	, [c] INT
	, [d] INT
	)

INSERT @test
      SELECT NULL, 4, 1, 0
UNION SELECT NULL, NULL, NULL, NULL
UNION SELECT 23, 12, 12, 1
UNION SELECT 0, NULL, 0, 1
UNION SELECT 34, 2, 1, NULL

SELECT * FROM @test

SELECT * FROM @test
WHERE
	NOT (
	[a] IS NULL
	AND [b] IS NULL
	AND [c] IS NULL
	AND [d] IS NULL
	)

Does this work?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-05 : 12:33:36

Which I see Peso has already posted (in the first post) so I am confused!

Midpenntech. You can copy and paste the code I posted above. It won't effect any of your data -- it works on my system (2005) what does it do on yours?

Is it possible that your NULLS are not NULLS they are some sort of string?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 12:53:41

quote:
Originally posted by Transact Charlie

quote:
WHERE (sixty + ninety + onetwenty + over120) IS NOT NULL

That's incorrect surely. The OP said he only wants line removed where everything is NULL

(10 + NULL + 2 + 0) IS NULL so wouldn't be brought back but
(NULL + NULL + NULL + NULL) IS NULL and also wouldn't be brought back.

OP wants to find the care where they are all NULL?

TEST CASE

DECLARE @test TABLE (
[a] INT
, [b] INT
, [c] INT
, [d] INT
)

INSERT @test
SELECT NULL, 4, 1, 0
UNION SELECT NULL, NULL, NULL, NULL
UNION SELECT 23, 12, 12, 1
UNION SELECT 0, NULL, 0, 1
UNION SELECT 34, 2, 1, NULL

SELECT * FROM @test

SELECT * FROM @test
WHERE
NOT (
[a] IS NULL
AND [b] IS NULL
AND [c] IS NULL
AND [d] IS NULL
)

Does this work?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ah...yes ...you're right it seems...OP wants only rows with all nulls to be eliminated.I misinterpreted. it must be the below


WHERE CASE WHEN sixty IS NULL THEN 1 ELSE 0 END  + 
CASE WHEN ninety IS NULL THEN 1 ELSE 0 END  + 
CASE WHEN  onetwenty IS NULL THEN 1 ELSE 0 END  + 
CASE WHEN  over120 IS NULL THEN 1 ELSE 0 END <4

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-12-05 : 13:18:52

where coalesce(col1, col2, col3, col4, col5) is null

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-05 : 13:25:55

Shouldn't it be like this

quote:
Originally posted by blindman

where coalesce(col1, col2, col3, col4, col5) is not null

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-12-05 : 13:46:58

Yup.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-12-05 : 14:13:50

that did not resolve it

Here is the code
SELECT CASE WHEN sixty < 0 THEN NULL ELSE sixty END AS sixty, CASE WHEN ninety < 0 THEN NULL ELSE ninety END AS ninety,
CASE WHEN onetwenty < 0 THEN NULL ELSE onetwenty END AS onetwenty, CASE WHEN over120 < 0 THEN NULL ELSE over120 END AS over120,
kcustnum
FROM dbo.Delinquencyview1
WHERE (COALESCE (sixty, ninety, onetwenty, over120) IS NOT NULL)

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-12-05 : 14:17:31

sixty, ninety, onetwenty, over120, kcust#
NULL, NULL, NULL, NULL, 100014
NULL, NULL, NULL, NULL, 100050
29.89, NULL, NULL, NULL, 100314
NULL, NULL, 500.5, 468.7, 100428
59.26, 7.98, NULL, NULL, 100542
NULL, NULL, NULL, NULL, 100675
13.66, NULL, NULL, NULL, 101113
2405.79,957.18, NULL, NULL, 101275
890.72, NULL, NULL, NULL, 101280
5.56, NULL, 371, NULL, 102045
NULL, NULL, NULL, NULL, 102110
1.31, NULL, NULL, NULL, 102430
NULL, NULL, NULL, NULL, 102876
NULL, NULL, NULL, NULL, 102969
100, NULL, NULL, NULL, 103300
NULL, NULL, NULL, NULL, 103320
9812.27,830.52, NULL, NULL, 104295
NULL, NULL, NULL, NULL, 104585
NULL, NULL, NULL, NULL, 104597
NULL, NULL, NULL, NULL, 105155
NULL, NULL, NULL, NULL, 105189
2.84, 2.84, 5.68, 200.61, 105413
NULL, NULL, NULL, NULL, 106141
NULL, NULL, NULL, NULL, 106394
NULL, NULL, NULL, NULL, 106870
135.53, NULL, NULL, NULL, 107070
5.76, 3.32, NULL, NULL, 107147
46.61, 3107.06,NULL, NULL, 107233
101.77, NULL, NULL, NULL, 107405
NULL, NULL, NULL, NULL, 107668
NULL, NULL, NULL, NULL, 107796
1451.66,NULL, NULL, NULL, 108442

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-12-05 : 14:19:59

i gave you sample data, the first 2 rows i dont want shown the 3rd, 4th, 5th i want shown, 6th not shown, 7th shown. ect ect.

Only show when 1 or more of the fields has a value. IF NULL is in all fields (Sixty, ninety, onetwenty, over120) Then dont show.

i am using sql 2005

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-12-05 : 14:23:07

This is the correct code:
WHERE COALESCE (sixty, ninety, onetwenty, over120) IS NOT NULL
It will only show records where one of the columns has a value.
If you are not getting the right results from this, then you have bad data. Most likely some of the values that you think are nulls are actually zero-length strings.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources