Null out a returned value - 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 out a returned value

Author

Topic

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2008-01-29 : 16:42:01

Anyone know how I can null-out the returned values of the PrjMgr field? I have bolded the field in question.
Here is the code:

select AgencyId
,ControlNumber
,ISNULL(LLA_AUDITS.PrjMgr,Projects.PrjMgr) as PrjMgr,ISNULL(CASE LEN(datepart(mm, PeriodFinish)) WHEN 1 THEN '0' + CAST(datepart(mm, PeriodFinish) as varchar(2)) ELSE CAST(datepart(mm, PeriodFinish) as varchar(2)) END ,'')As month
,ISNULL(CAST(datepart(dd, PeriodFinish) as varchar), '') As day
,ISNULL(CAST(right(datepart(yy, PeriodFinish),2) as varchar),'') As year
,ISNULL(CAST(left(datepart(yy, PeriodFinish),2) as varchar),'') As century
,custName
from LLA_Audits Left Outer Join Projects
on controlnumber = prjcode Left Outer Join Customers on Agencyid = CustCode
where AgencyId is not null
and not(LLA_AUDITS.PrjMgr is null and Projects.PrjMgr is null)
and left(controlnumber,2)<>'72'
and left(controlnumber,2) <>'13'
and (CPAFirmId IN ('0','')or CPAFirmId is null)
order by ControlNumber

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 16:43:23

,COALESCE(LLA_AUDITS.PrjMgr, Projects.PrjMgr, '') AS PrjMgr

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 16:44:55

If you want to help you further you MUST prefix all columns in the query with the corresponding table name, so that we know which table every column comes from.

E 12°55'05.25"
N 56°04'39.16"

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2008-01-29 : 16:59:29

This has returned the same result. I am trying to force the column to display as a null for all rows returned to the PrjMgr field.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 17:32:53

That will be hard since you also have this WHERE clause

and not(LLA_AUDITS.PrjMgr is null and Projects.PrjMgr is null)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 17:33:37

Once again, if you want help, please repost query with ALL columns prefixed with table names!

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 17:37:26

And if you want help fast, follow the advices in this blog post
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2008-01-29 : 17:58:41

OK here everything prefixed!

select LLA_Audits.AgencyId
,LLA_Audits.ControlNumber
,ISNULL(LLA_AUDITS.PrjMgr,Projects.PrjMgr) as PrjMgr
,ISNULL(CASE LEN(datepart(mm, LLA_Audits.PeriodFinish)) WHEN 1 THEN '0' + CAST(datepart(mm, LLA_Audits.PeriodFinish) as varchar(2)) ELSE CAST(datepart(mm, LLA_Audits.PeriodFinish) as varchar(2)) END ,'')As month
,ISNULL(CAST(datepart(dd, LLA_Audits.PeriodFinish) as varchar), '') As day
,ISNULL(CAST(right(datepart(yy, LLA_Audits.PeriodFinish),2) as varchar),'') As year
,ISNULL(CAST(left(datepart(yy, LLA_Audits.PeriodFinish),2) as varchar),'') As century
,custName
from LLA_Audits Left Outer Join Projects
on controlnumber = Projects.prjcode Left Outer Join Customers on LLA_Audits.Agencyid = Customers.CustCode
where LLA_Audits.AgencyId is not null
and not(LLA_AUDITS.PrjMgr is null and Projects.PrjMgr is null)
and left(LLA_Audits.controlnumber,2)<>'72'
and left(LLA_Audits.controlnumber,2) <>'13'
and (LLA_Audits.CPAFirmId IN ('0','')or LLA_Audits.CPAFirmId is null)
order by LLA_Audits.ControlNumber

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 18:09:39

What do you mean with "null out"? Neither LLA_AUDITS.PrjMgr nor Projects.PrjMgr can be null.
You have made that sure in the WHERE clause.

Do you mean you want NULL as result then LLA_AUDITS.PrjMgr equals Projects.PrjMgr?

select		LLA_Audits.AgencyId,
		LLA_Audits.ControlNumber,
		ISNULL(LLA_AUDITS.PrjMgr, Projects.PrjMgr) as PrjMgr,
		ISNULL(RIGHT('0' + datename(month, LLA_Audits.PeriodFinish), 2), '') As month,
		ISNULL(datename(day, LLA_Audits.PeriodFinish), '') As day,
		ISNULL(right(datename(year, LLA_Audits.PeriodFinish), 2), '') As year,
		ISNULL(left(datename(year, LLA_Audits.PeriodFinish), 2), '') As century,
		Customers.custName
from		LLA_Audits
Left Join	Projects on Projects.prjcode = LLA_Audits.controlnumber
Left Join	Customers on Customers.CustCode = LLA_Audits.Agencyid
where		LLA_Audits.AgencyId is not null 
		and LLA_AUDITS.PrjMgr is not null
		and Projects.PrjMgr is not null
		and left(LLA_Audits.controlnumber,2) NOT IN ('72', '13')
		and (
			LLA_Audits.CPAFirmId IN ('0','')
			or LLA_Audits.CPAFirmId is null
		)
order by	LLA_Audits.ControlNumber

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources