Checking null in select list

Hi all,
The following is in Select list, it returns null, how do I account if there
is a null in there or not
when writting this?
'1.) ' + dbo.TempERCP.Final_Diagnosis + ' 2.) ' +
dbo.TempERCP.Final_Diagnosis2 + ' 3.) ' + dbo.TempERCP.Final_Diagnosis3 as
[Final Diagnosis]
thanks
gvsee isnull() or coalesce() in BOL
gv wrote:
> Hi all,
> The following is in Select list, it returns null, how do I account if ther
e
> is a null in there or not
> when writting this?
> '1.) ' + dbo.TempERCP.Final_Diagnosis + ' 2.) ' +
> dbo.TempERCP.Final_Diagnosis2 + ' 3.) ' + dbo.TempERCP.Final_Diagnosis3
as
> [Final Diagnosis]
> thanks
> gv
>|||First the answer to your question:
Use isnull (assuming the first one can't be null...):
select '1.) ' + dbo.TempERCP.Final_Diagnosis, +
isnull(' 2.) ' + dbo.TempERCP.Final_Diagnosis2, '') +
isnull(' 3.) ' + dbo.TempERCP.Final_Diagnosis3, '') as [Final Diagnosis]
Then a quick spell of preachiness. Columns that end in numbers are
generally a sign of a bad idea (not to mention I don't want to go to a
doctor that gives me three different final_diagnoses :) Putting these in a
different table would make dealing with them in SQL far easier, and doing
this formatting in the client would be easier still.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"gv" <viatorg@.musc.edu> wrote in message
news:eC28HedIGHA.1088@.tk2msftngp13.phx.gbl...
> Hi all,
> The following is in Select list, it returns null, how do I account if
> there is a null in there or not
> when writting this?
> '1.) ' + dbo.TempERCP.Final_Diagnosis + ' 2.) ' +
> dbo.TempERCP.Final_Diagnosis2 + ' 3.) ' + dbo.TempERCP.Final_Diagnosis3
> as [Final Diagnosis]
> thanks
> gv
>|||SELECT
COALESCE(
'1.) ' + Final_Diagnosis + ' 2.) ' + Final_Diagnosis2 + ' 3.) ' +
Final_Diagnosis3,
'1.) ' + Final_Diagnosis + ' 2.) ' + Final_Diagnosis2,
'1.) ' + Final_Diagnosis + ' 2.) ' + Final_Diagnosis3,
'1.) ' + Final_Diagnosis,
'1.) ' + Final_Diagnosis2,
'1.) ' + Final_Diagnosis2 + ' 2.) ' + Final_Diagnosis3,
'1.) ' + Final_Diagnosis3,
'No Diagnosis for Patient')
AS
[Final Diagnosis]
That being said, I would strongly suggest considering redesigning your db so
that this information is not stored in repeating columns.
(If Celko sees this, be prepared for an onslaught!)
"gv" wrote:

> Hi all,
> The following is in Select list, it returns null, how do I account if ther
e
> is a null in there or not
> when writting this?
> '1.) ' + dbo.TempERCP.Final_Diagnosis + ' 2.) ' +
> dbo.TempERCP.Final_Diagnosis2 + ' 3.) ' + dbo.TempERCP.Final_Diagnosis3
as
> [Final Diagnosis]
> thanks
> gv
>
>|||thanks
gv
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:D265A10D-4E09-438E-BCF4-25CD9AB8276F@.microsoft.com...
> SELECT
> COALESCE(
> '1.) ' + Final_Diagnosis + ' 2.) ' + Final_Diagnosis2 + ' 3.) ' +
> Final_Diagnosis3,
> '1.) ' + Final_Diagnosis + ' 2.) ' + Final_Diagnosis2,
> '1.) ' + Final_Diagnosis + ' 2.) ' + Final_Diagnosis3,
> '1.) ' + Final_Diagnosis,
> '1.) ' + Final_Diagnosis2,
> '1.) ' + Final_Diagnosis2 + ' 2.) ' + Final_Diagnosis3,
> '1.) ' + Final_Diagnosis3,
> 'No Diagnosis for Patient')
> AS
> [Final Diagnosis]
> That being said, I would strongly suggest considering redesigning your db
> so
> that this information is not stored in repeating columns.
> (If Celko sees this, be prepared for an onslaught!)
> --
> "gv" wrote:
>|||thks
gv
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:eP1G5odIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> see isnull() or coalesce() in BOL
>
> gv wrote:|||I agree with you on the column and table structures, long story and was not
created by
me.
thanks
gv
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OpGzcqdIGHA.216@.TK2MSFTNGP15.phx.gbl...
> First the answer to your question:
> Use isnull (assuming the first one can't be null...):
> select '1.) ' + dbo.TempERCP.Final_Diagnosis, +
> isnull(' 2.) ' + dbo.TempERCP.Final_Diagnosis2, '') +
> isnull(' 3.) ' + dbo.TempERCP.Final_Diagnosis3, '') as [Final
> Diagnosis]
> Then a quick spell of preachiness. Columns that end in numbers are
> generally a sign of a bad idea (not to mention I don't want to go to a
> doctor that gives me three different final_diagnoses :) Putting these in
> a different table would make dealing with them in SQL far easier, and
> doing this formatting in the client would be easier still.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "gv" <viatorg@.musc.edu> wrote in message
> news:eC28HedIGHA.1088@.tk2msftngp13.phx.gbl...
>|||Been there done that. I just try never to give advice like this without
making sure :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"gv" <viatorg@.musc.edu> wrote in message
news:uu83f0dIGHA.3492@.TK2MSFTNGP09.phx.gbl...
>I agree with you on the column and table structures, long story and was not
>created by
> me.
> thanks
> gv
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:OpGzcqdIGHA.216@.TK2MSFTNGP15.phx.gbl...
>