how to get rid of nulls in my query

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)

how to get rid of nulls in my query

Author

Topic

phaze
Starting Member

42 Posts

Posted - 2010-01-28 : 15:58:19

with the following query i get back 3 columns

FinanceCoCode, PrimaryBorrowerEmail, and CoBorrowerEmail.

I get a few nulls in the result and would like to get rid of null with a blank space

here is my current code

select top 100 CurrentOverallBalance.cLoanOriginationType as FinanceCoCode,
	case when CurrentOverallCustomer.cRecType=0 then cEmail end AS PrimaryBorrowerEmail, 
	case when CurrentOverallCustomer.cRecType=6 then cEmail end AS CoBorrowerEmail
from CurrentOverallCustomer
	join CurrentOverallBalance 
		on CurrentOverallCustomer.nAccount = CurrentOverallBalance.nAccount
where cRecType = '6' or cRecType = '0'
	and cEmail like '%@%.%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-28 : 16:02:33

1) You are missing ELSE statements
2) You are misusing AND's and OR's
3) You are using both integer values and string values for same column

SELECT TOP(100)	CurrentOverallBalance.cLoanOriginationType AS FinanceCoCode,
		case CurrentOverallCustomer.cRecType
			when 0 then cEmail
			else ''
		end AS PrimaryBorrowerEmail, 
		case CurrentOverallCustomer.cRecType
			when 6 then cEmail
			else ''
		 end AS CoBorrowerEmail
from		CurrentOverallCustomer
inner join	CurrentOverallBalance on CurrentOverallBalance.nAccount = CurrentOverallCustomer.nAccount
where		cRecType IN ('6', '0')
		and cEmail like '%@%.%'

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources