Combination of three Null Values

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)

Combination of three Null Values

Author

Topic

gsgill76
Posting Yak Master

137 Posts

Posted - 2008-09-12 : 00:55:13

Hi
In my table i have three columns, my aim is to write SQL which will find the row(s), if the value in the any column is Null.(Not that row(s) which is non-null for all the three columns)
CODE


SELECT HUD1FeeLineTypeDesc ,  HUD1FeeAmtFrmBor , HUD1FeeAmtFrmSeller     FROM HUD1SettlementCharges
WHERE LoanNum =  147579028
AND HUD1FeeRowTypeId = 5 
AND (
		(	ISNULL(HUD1FeeLineTypeDesc,'')		<> ''
			AND ISNULL(HUD1FeeAmtFrmBor,0)		= 0 
			AND ISNULL(HUD1FeeAmtFrmSeller,0)	= 0 )
		OR 
		(	ISNULL(HUD1FeeLineTypeDesc,'')		= ''
			AND ISNULL(HUD1FeeAmtFrmBor,0)		<> 0 
			AND ISNULL(HUD1FeeAmtFrmSeller,0)	= 0 )
		OR
		(	ISNULL(HUD1FeeLineTypeDesc,'')		= ''
			AND ISNULL(HUD1FeeAmtFrmBor,0)		= 0 
			AND ISNULL(HUD1FeeAmtFrmSeller,0)	<> 0 )
		OR
		(	ISNULL(HUD1FeeLineTypeDesc,'')		<> ''
			AND ISNULL(HUD1FeeAmtFrmBor,0)		<> 0 
			AND ISNULL(HUD1FeeAmtFrmSeller,0)	= 0 )
		OR
		(	ISNULL(HUD1FeeLineTypeDesc,'')		= ''
			AND ISNULL(HUD1FeeAmtFrmBor,0)		<> 0 
			AND ISNULL(HUD1FeeAmtFrmSeller,0)	<> 0 )
  )

Its working fine, is there any other easy way to write the same?
Thanks
Gurpreet Gill

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-12 : 01:05:55

if the condition is what you said "if the value in the any column is Null", why not just


AND HUD1FeeRowTypeId = 5 
AND (
        HUD1FeeLineTypeDesc is null
    or  HUD1FeeAmtFrmBor    is null
    or  HUD1FeeAmtFrmSeller is null
    )

KH
[spoiler]Time is always against us[/spoiler]

gsgill76
Posting Yak Master

137 Posts

Posted - 2008-09-12 : 02:08:11

Hi KH
this returns the Row(s), for which all the columns are Empty, i want rows for which any of the colom is NULL.

Thanks
Gurpreet Gill

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-12 : 02:10:59

quote:
Originally posted by gsgill76

Hi KH
this returns the Row(s), for which all the columns are Empty, i want rows for which any of the colom is NULL.

Thanks
Gurpreet Gill

it is returning rows where any one of the columns IS NULL.

Can you post some sample data where my query returns all the columns are empty ! And what do you mean by EMPTY ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-12 : 02:17:13

What OP has written is a case where

1) one of the columns is NULL and the other two aren't NULL.
2) one of the columns is not NULL and the other two are NULL.

Which is it, gsgill76?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-12 : 02:22:53

Yes. What OP describe on what he wanted and the actual coding is actually different. He is not implementing what OP want or unless that is not what OP wanted.

KH
[spoiler]Time is always against us[/spoiler]

gsgill76
Posting Yak Master

137 Posts

Posted - 2008-09-12 : 02:31:19

Kh
Thanks
Problem Solved as

SELECT HUD1FeeLineNum ,HUD1FeeLineTypeDesc , HUD1FeeAmtFrmBor , HUD1FeeAmtFrmSeller FROM HUD1SettlementCharges
WHERE LoanNum = 147579028 AND HUD1FeeRowTypeId = 5 and (
(ISNULL(HUD1FeeAmtFrmBor,0.00) = 0.00 )
or (ISNULL(HUD1FeeAmtFrmSeller,0.00) = 0.00)
or (ISNULL(HUD1FeeLineTypeDesc,'') = '' ) )
AND Not (
(ISNULL(HUD1FeeAmtFrmBor,0.00) = 0.00 )
AND (ISNULL(HUD1FeeAmtFrmSeller,0.00) = 0.00)
AND (ISNULL(HUD1FeeLineTypeDesc,'') = '' ) )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources