Hi All
Please write me the reason for
SQL:
--
select * from table1
where Required <> 'Y'
the result set is empty.
Table Schema is:
--
CREATE TABLE [dbo].[Table1] (
[Names] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Required] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert Statement is is:
--
insert into table1 ([Names],Required)
values('Test1',null)
insert into table1 ([Names],Required)
values('Test2','Y')
insert into table1 ([Names],Required)
values('Test3',null)
insert into table1 ([Names],Required)
values('Test4','Y')
insert into table1 ([Names],Required)
values('Test5',null)
Is there any option I need to get the appropriate result
set?
B'coz my result set expected is
Test1 NULL
Test3 NULL
Test5 NULLWhy are you allowing NULLs? That's the problem, you can't say NULL is not
equal to 'Y'... since the definition of NULL is unknown, then it very may
well by 'Y' ...
How about making it NOT NULL DEFAULT 'N'?
Or make your query WHERE COALESCE(Required, 'N') = 'N'?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ana" <anonymous@.discussions.microsoft.com> wrote in message
news:a96401c43652$5032ef30$a601280a@.phx.gbl...
> Hi All
> Please write me the reason for
> SQL:
> --
> select * from table1
> where Required <> 'Y'
> the result set is empty.
> Table Schema is:
> --
> CREATE TABLE [dbo].[Table1] (
> [Names] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Required] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Insert Statement is is:
> --
> insert into table1 ([Names],Required)
> values('Test1',null)
> insert into table1 ([Names],Required)
> values('Test2','Y')
> insert into table1 ([Names],Required)
> values('Test3',null)
> insert into table1 ([Names],Required)
> values('Test4','Y')
> insert into table1 ([Names],Required)
> values('Test5',null)
> Is there any option I need to get the appropriate result
> set?
> B'coz my result set expected is
> Test1 NULL
> Test3 NULL
> Test5 NULL|||Hi Aaron Bertrand
Thanks for your clear information.Now I undestand.
Thanks once again
Ana.
>--Original Message--
>Why are you allowing NULLs? That's the problem, you
can't say NULL is not
>equal to 'Y'... since the definition of NULL is unknown,
then it very may
>well by 'Y' ...
>How about making it NOT NULL DEFAULT 'N'?
>Or make your query WHERE COALESCE(Required, 'N') = 'N'?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Ana" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a96401c43652$5032ef30$a601280a@.phx.gbl...
>
>.
>|||Answered in .programming.
Please don't multi-post.
David Portas
SQL Server MVP
--
Showing posts with label null. Show all posts
Showing posts with label null. Show all posts
Tuesday, March 20, 2012
Clarification on NULL values in the records.
Hi All
Please write me the reason for
SQL:
--
select * from table1
where Required <> 'Y'
the result set is empty.
Table Schema is:
--
CREATE TABLE [dbo].[Table1] (
[Names] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Required] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert Statement is is:
--
insert into table1 ([Names],Required)
values('Test1',null)
insert into table1 ([Names],Required)
values('Test2','Y')
insert into table1 ([Names],Required)
values('Test3',null)
insert into table1 ([Names],Required)
values('Test4','Y')
insert into table1 ([Names],Required)
values('Test5',null)
Is there any option I need to get the appropriate result
set?
B'coz my result set expected is
Test1 NULL
Test3 NULL
Test5 NULLWhy are you allowing NULLs? That's the problem, you can't say NULL is not
equal to 'Y'... since the definition of NULL is unknown, then it very may
well by 'Y' ...
How about making it NOT NULL DEFAULT 'N'?
Or make your query WHERE COALESCE(Required, 'N') = 'N'?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ana" <anonymous@.discussions.microsoft.com> wrote in message
news:a96401c43652$5032ef30$a601280a@.phx.gbl...
> Hi All
> Please write me the reason for
> SQL:
> --
> select * from table1
> where Required <> 'Y'
> the result set is empty.
> Table Schema is:
> --
> CREATE TABLE [dbo].[Table1] (
> [Names] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Required] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Insert Statement is is:
> --
> insert into table1 ([Names],Required)
> values('Test1',null)
> insert into table1 ([Names],Required)
> values('Test2','Y')
> insert into table1 ([Names],Required)
> values('Test3',null)
> insert into table1 ([Names],Required)
> values('Test4','Y')
> insert into table1 ([Names],Required)
> values('Test5',null)
> Is there any option I need to get the appropriate result
> set?
> B'coz my result set expected is
> Test1 NULL
> Test3 NULL
> Test5 NULL|||Hi Aaron Bertrand
Thanks for your clear information.Now I undestand.
Thanks once again
Ana.
>--Original Message--
>Why are you allowing NULLs? That's the problem, you
can't say NULL is not
>equal to 'Y'... since the definition of NULL is unknown,
then it very may
>well by 'Y' ...
>How about making it NOT NULL DEFAULT 'N'?
>Or make your query WHERE COALESCE(Required, 'N') = 'N'?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Ana" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a96401c43652$5032ef30$a601280a@.phx.gbl...
>> Hi All
>> Please write me the reason for
>> SQL:
>> --
>> select * from table1
>> where Required <> 'Y'
>> the result set is empty.
>> Table Schema is:
>> --
>> CREATE TABLE [dbo].[Table1] (
>> [Names] [nvarchar] (50) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [Required] [nvarchar] (10) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL
>> ) ON [PRIMARY]
>> GO
>> Insert Statement is is:
>> --
>> insert into table1 ([Names],Required)
>> values('Test1',null)
>> insert into table1 ([Names],Required)
>> values('Test2','Y')
>> insert into table1 ([Names],Required)
>> values('Test3',null)
>> insert into table1 ([Names],Required)
>> values('Test4','Y')
>> insert into table1 ([Names],Required)
>> values('Test5',null)
>> Is there any option I need to get the appropriate result
>> set?
>> B'coz my result set expected is
>> Test1 NULL
>> Test3 NULL
>> Test5 NULL
>
>.
>|||Answered in .programming.
Please don't multi-post.
--
David Portas
SQL Server MVP
--sqlsql
Please write me the reason for
SQL:
--
select * from table1
where Required <> 'Y'
the result set is empty.
Table Schema is:
--
CREATE TABLE [dbo].[Table1] (
[Names] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Required] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert Statement is is:
--
insert into table1 ([Names],Required)
values('Test1',null)
insert into table1 ([Names],Required)
values('Test2','Y')
insert into table1 ([Names],Required)
values('Test3',null)
insert into table1 ([Names],Required)
values('Test4','Y')
insert into table1 ([Names],Required)
values('Test5',null)
Is there any option I need to get the appropriate result
set?
B'coz my result set expected is
Test1 NULL
Test3 NULL
Test5 NULLWhy are you allowing NULLs? That's the problem, you can't say NULL is not
equal to 'Y'... since the definition of NULL is unknown, then it very may
well by 'Y' ...
How about making it NOT NULL DEFAULT 'N'?
Or make your query WHERE COALESCE(Required, 'N') = 'N'?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ana" <anonymous@.discussions.microsoft.com> wrote in message
news:a96401c43652$5032ef30$a601280a@.phx.gbl...
> Hi All
> Please write me the reason for
> SQL:
> --
> select * from table1
> where Required <> 'Y'
> the result set is empty.
> Table Schema is:
> --
> CREATE TABLE [dbo].[Table1] (
> [Names] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Required] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Insert Statement is is:
> --
> insert into table1 ([Names],Required)
> values('Test1',null)
> insert into table1 ([Names],Required)
> values('Test2','Y')
> insert into table1 ([Names],Required)
> values('Test3',null)
> insert into table1 ([Names],Required)
> values('Test4','Y')
> insert into table1 ([Names],Required)
> values('Test5',null)
> Is there any option I need to get the appropriate result
> set?
> B'coz my result set expected is
> Test1 NULL
> Test3 NULL
> Test5 NULL|||Hi Aaron Bertrand
Thanks for your clear information.Now I undestand.
Thanks once again
Ana.
>--Original Message--
>Why are you allowing NULLs? That's the problem, you
can't say NULL is not
>equal to 'Y'... since the definition of NULL is unknown,
then it very may
>well by 'Y' ...
>How about making it NOT NULL DEFAULT 'N'?
>Or make your query WHERE COALESCE(Required, 'N') = 'N'?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Ana" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a96401c43652$5032ef30$a601280a@.phx.gbl...
>> Hi All
>> Please write me the reason for
>> SQL:
>> --
>> select * from table1
>> where Required <> 'Y'
>> the result set is empty.
>> Table Schema is:
>> --
>> CREATE TABLE [dbo].[Table1] (
>> [Names] [nvarchar] (50) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [Required] [nvarchar] (10) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL
>> ) ON [PRIMARY]
>> GO
>> Insert Statement is is:
>> --
>> insert into table1 ([Names],Required)
>> values('Test1',null)
>> insert into table1 ([Names],Required)
>> values('Test2','Y')
>> insert into table1 ([Names],Required)
>> values('Test3',null)
>> insert into table1 ([Names],Required)
>> values('Test4','Y')
>> insert into table1 ([Names],Required)
>> values('Test5',null)
>> Is there any option I need to get the appropriate result
>> set?
>> B'coz my result set expected is
>> Test1 NULL
>> Test3 NULL
>> Test5 NULL
>
>.
>|||Answered in .programming.
Please don't multi-post.
--
David Portas
SQL Server MVP
--sqlsql
Sunday, March 11, 2012
choosing a the column that is not null
I have two columns in a table. For every record, one column contains a value and the other one is null. Which one is filled and which one is empty differs per record. In my query I only want to retrieve the column that contains the value, but not the other one. Is it possible to do this with a standard tsql function? Other solutions are also welcome.REfer IS [NOT] NULL from BOL.|||I tried to use "is null", "not null" and stuff like that, but it did not get the desired results. Meanwhile I found the functions "ISNULL()" and "NULLIF()". As I can see it now, these provide the correct results.|||Originally posted by jora
I tried to use "is null", "not null" and stuff like that, but it did not get the desired results. Meanwhile I found the functions "ISNULL()" and "NULLIF()". As I can see it now, these provide the correct results.
You might want to take a look at the function COALESCE() which is designed to solve the problem you have.
The function is also part of the ANSI standard and is supported by many RDBMS.
I tried to use "is null", "not null" and stuff like that, but it did not get the desired results. Meanwhile I found the functions "ISNULL()" and "NULLIF()". As I can see it now, these provide the correct results.
You might want to take a look at the function COALESCE() which is designed to solve the problem you have.
The function is also part of the ANSI standard and is supported by many RDBMS.
Thursday, March 8, 2012
Chicken and egg
Hi there,
This strikes me as odd but I was trying to create a NULL datetime value for storage in a SQL server table and found that a .NET datetime object defaults to its min value of Jan 1, 1 A.D. OK, now this in itself is not a problem, but the min value for a date in SQL server is Jan 1 1753, so if I just try to save the default value .NET datetime, I get a value out of range exception. Annoying but reasonably easy to remedy.
I was just wondering if anyone knew which min value came first, why, and why the second min value, or at least its default value, wasn't made equal to the other.
Cheers, DanYou could just give your input parameter in SQL a default of NULL and if your date in your .NET code is 1/1/1 then do NOT add a parameter for it (so your NULL default will be used).
Sunday, February 19, 2012
checking null value
Hi all,
I know someone answered this in the past but couldn't find the post.
How do I check for nulls if they might be in the column? colb and colc might
have null value?
the below query returns some rows that don't contain value "blue" which is
wrong.
select cola,colb,colc from tableA where
cola = 'blue' or
colb = 'blue' or
colc = 'blue'
thanks
gv>> How do I check for nulls if they might be in the column? colb and colc
Nulls can be checked using IS [NOT] NULL clause in SQL.
Since you are using OR operator, the query might return every row where any
one of cola, colb, col3 or any combination of the three has a value 'blue'.
Based on the query you posted, the result you see is valid.
Anith
I know someone answered this in the past but couldn't find the post.
How do I check for nulls if they might be in the column? colb and colc might
have null value?
the below query returns some rows that don't contain value "blue" which is
wrong.
select cola,colb,colc from tableA where
cola = 'blue' or
colb = 'blue' or
colc = 'blue'
thanks
gv>> How do I check for nulls if they might be in the column? colb and colc
Nulls can be checked using IS [NOT] NULL clause in SQL.
Since you are using OR operator, the query might return every row where any
one of cola, colb, col3 or any combination of the three has a value 'blue'.
Based on the query you posted, the result you see is valid.
Anith
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...
>
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...
>
Checking last user Identity on remote server
Is it possible to get the latest inserted Identity value on a remote server?
(The following gives a NULL result:
select ident_current('[my_linked_server].thedatabase.dbo.thetable')
)@.@.IDENTITY (assuming you mean server wide irrespective of the user\ connections etc).
HTH|||Beg your pardon - @.@.Identity is specific to session - sorry - didn't read every last line of BoL before hitting Submit.|||@.@.IDENTITY (assuming you mean server wide irrespective of the user\ connections etc).
HTH
Are you sure? I think that's what I tried first. Doesn't
it give the latest Id value used in the server where
you are (so to speak), not the remote server?|||Finally, I understood this is the way to do it. Just as feedback.
DECLARE @.return_status int
EXEC @.return_status = MyLinkedServer.the_database.dbo.a_stored_procedure
SELECT 'Return Status' = @.return_stat
The SP "a_stored_procedure" is stored on the linked server. It returns
Ident_Current for the appropriate table, using RETURN, simply.
(The following gives a NULL result:
select ident_current('[my_linked_server].thedatabase.dbo.thetable')
)@.@.IDENTITY (assuming you mean server wide irrespective of the user\ connections etc).
HTH|||Beg your pardon - @.@.Identity is specific to session - sorry - didn't read every last line of BoL before hitting Submit.|||@.@.IDENTITY (assuming you mean server wide irrespective of the user\ connections etc).
HTH
Are you sure? I think that's what I tried first. Doesn't
it give the latest Id value used in the server where
you are (so to speak), not the remote server?|||Finally, I understood this is the way to do it. Just as feedback.
DECLARE @.return_status int
EXEC @.return_status = MyLinkedServer.the_database.dbo.a_stored_procedure
SELECT 'Return Status' = @.return_stat
The SP "a_stored_procedure" is stored on the linked server. It returns
Ident_Current for the appropriate table, using RETURN, simply.
Checking if Value from a column is blank in SQL Server 2000
Hi
I want to check if the values of a column from a table is blank (i.e. empty but not null) . If it is blank, then I want to replace it with some other text. Is there some function like, Isnull or coalesce in SQL Server 2000, with which I can check if the value is blank and replace it. I want to use it as part of query so, I do not wish to use if trim(...) etc.
If any one has an solution, please do tell me.
Regards
Vineed
Here it is,
Code Snippet
Create table #Data(
ValueColumn varchar(100)
)
Insert Into #Data Values(' ');
Insert Into #Data Values('');
Insert Into #Data Values(' ');
Insert Into #Data Values(' ');
Insert Into #Data Values(NULL);
Insert Into #Data Values('Some value');
Select Case When ValueColumn='' Then '(Empty)' Else ValueColumn End From #Data
Checking if String is NULL or EMPTY in SQL
I need to check in my Stored procedure if the information passed is null or empty so I can decided to insert the new value or keep the old. How do I accomplish this please in T-SQL. Thanks in advance.
IFISNULL(@.param)OR @.param =''THEN doSomething...
(Note the two apostrophes ' and ', not a quote mark!)|||
Books online (help files that comes with SQL) is an amzing little app. Below is an example and the Syntax.
USE pubsGOSELECTAVG(ISNULL(price, $10.00))FROM titlesGOISNULL ( check_expression , replacement_value )|||
Create Procedure mySpNameHere
@.InputValueHere VARCHAR(50) = NULL
AS
IF @.InputValue IS NULL OR @.InputValue = ''
/*Keep the old value*/
ELSE
/*Run the update statement here with your new value*/
|||
cheetahtech:
ISNULL ( check_expression , replacement_value )
Sorry. I got a blackout. Of course, ISNULL syntax is to be used in a query where you want to specify an alternative value, if the expression is NULL.
The correct way to check for NULL in a condition is IF @.Param IS NULL as rich freeman points out.
Checking for where a datetime column has a null value
I'm trying to filter out sales reps in a resultset that are no longer
with a company, whereas 'Start' is when the salesrep was hired ( a
datetime datatype ) and "Terminated' is when a salesrep was
terminated ( a datetime datatype).
Nulls are allowed in the Terminated column. If that column is null,
then a salesrep still is employed. But when I want to include it in a
where clause:
WHERE (QueriedDate = '20050707') AND (SalesReps.Terminated =
'null'), the syntax is invalid . What function can i use in this sql
string to temporarily change 'Terminated' into something useable so
that I can check for nulls in that column.
Thanks
.NetSportsNULL is not a string, it marks the absence of a value (e.g. UNKNOWN). And
you cannot expect a sane answer from "is terminated = unknown?" Try
AND Terminated IS NULL
"netsports" <ballz2wall@.cox-dot-net.no-spam.invalid> wrote in message
news:EomdnRFBorvv9ZfeRVn_vQ@.giganews.com...
> I'm trying to filter out sales reps in a resultset that are no longer
> with a company, whereas 'Start' is when the salesrep was hired ( a
> datetime datatype ) and "Terminated' is when a salesrep was
> terminated ( a datetime datatype).
> Nulls are allowed in the Terminated column. If that column is null,
> then a salesrep still is employed. But when I want to include it in a
> where clause:
> WHERE (QueriedDate = '20050707') AND (SalesReps.Terminated =
> 'null'), the syntax is invalid . What function can i use in this sql
> string to temporarily change 'Terminated' into something useable so
> that I can check for nulls in that column.
> Thanks
> NetSports
>
with a company, whereas 'Start' is when the salesrep was hired ( a
datetime datatype ) and "Terminated' is when a salesrep was
terminated ( a datetime datatype).
Nulls are allowed in the Terminated column. If that column is null,
then a salesrep still is employed. But when I want to include it in a
where clause:
WHERE (QueriedDate = '20050707') AND (SalesReps.Terminated =
'null'), the syntax is invalid . What function can i use in this sql
string to temporarily change 'Terminated' into something useable so
that I can check for nulls in that column.
Thanks
.NetSportsNULL is not a string, it marks the absence of a value (e.g. UNKNOWN). And
you cannot expect a sane answer from "is terminated = unknown?" Try
AND Terminated IS NULL
"netsports" <ballz2wall@.cox-dot-net.no-spam.invalid> wrote in message
news:EomdnRFBorvv9ZfeRVn_vQ@.giganews.com...
> I'm trying to filter out sales reps in a resultset that are no longer
> with a company, whereas 'Start' is when the salesrep was hired ( a
> datetime datatype ) and "Terminated' is when a salesrep was
> terminated ( a datetime datatype).
> Nulls are allowed in the Terminated column. If that column is null,
> then a salesrep still is employed. But when I want to include it in a
> where clause:
> WHERE (QueriedDate = '20050707') AND (SalesReps.Terminated =
> 'null'), the syntax is invalid . What function can i use in this sql
> string to temporarily change 'Terminated' into something useable so
> that I can check for nulls in that column.
> Thanks
> NetSports
>
Thursday, February 16, 2012
Checking for NULL within an Expression
Hi All,
I would like an example of checking for NULL within an expression. I don't know the correct syntax. Checking for IsNull is incorrect so is IS Null. Can anyone help me?
Another C#er!
Try if(IsNothing(Myvalue)) or if(Myvvalue = Nothing)
hth
Helen
|||Read this post, it should help
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1248764&SiteID=1
Checking for Null Values in a Table/Matrix Cell
What would be an equivalent expression for ISNULL(datafield, 0) for a table/matrix cell? I am using iif( Len().. to find out if there is something in the cell, and displaying zero in the cell if the length of cell item is 0, however am wondering if there is any better/elegant way of doing that?
=iif(fieldname = nothing, 0, fieldname)|||um, wouldn't that be =iif(fieldname = dbnull.value,0,fieldname) ?|||Actually, SSRS converts dbnull to an actual null in the report, so using:iif(fields!fieldname.value = nothing , 0, fields!fieldname.value)
or
iif(IsNothing(fields!fieldname.value), 0, fields!fieldname.value)
seems to be the MSFT suggested way to trap for nulls.
iif(fields!fieldname.value = dbnull.value, 0, fields!fieldname.value) won't work. You might be able to get away with iif(fields!fieldname.value is system.dbnull.value, 0, fields!fieldname.value), but people have reported some issues with using System.DBNull and all the MSFT guys say to use "nothing".
checking for Null value
Hi,
I'm drawing a blank on how to do this, I'm trying to return the rows
where TempERCP.PI_Therapy column does not contain 'sphincterotomy' but,
I also want the rows that contain 'Null' there which isn't being returned?
SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate, TempERCP.Hospno
, TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
FROM
TempERCP INNER JOIN
TempDemo ON TempERCP.Hospno = TempDemo.Hospno
where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
( TempERCP.PI_Therapy not in ('sphincterotomy') )
order by TempERCP.Examdate, TempERCP.Hospno
thanks
gvdu... I think this is it
( TempERCP.PI_Therapy not in ('sphincterotomy')or TempERCP.PI_Therapy is
null )
would I use this COALESCE? example of using this
which is better?
thanks
gv
"gv" <viatorg@.musc.edu> wrote in message
news:exTZ7J2$FHA.3872@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm drawing a blank on how to do this, I'm trying to return the rows
> where TempERCP.PI_Therapy column does not contain 'sphincterotomy' but,
> I also want the rows that contain 'Null' there which isn't being returned?
>
> SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate,
> TempERCP.Hospno
> , TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
> FROM
> TempERCP INNER JOIN
> TempDemo ON TempERCP.Hospno = TempDemo.Hospno
> where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
> ( TempERCP.PI_Therapy not in ('sphincterotomy') )
> order by TempERCP.Examdate, TempERCP.Hospno
> thanks
> gv
>
>|||gv wrote:
> Hi,
> I'm drawing a blank on how to do this, I'm trying to return the rows
> where TempERCP.PI_Therapy column does not contain 'sphincterotomy'
> but,
> I also want the rows that contain 'Null' there which isn't being
> returned?
>
> SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate,
> TempERCP.Hospno , TempERCP.Full_Name,TempERCP.PI_Therapy,
> TempERCP.comment FROM
> TempERCP INNER JOIN
> TempDemo ON TempERCP.Hospno = TempDemo.Hospno
> where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
> ( TempERCP.PI_Therapy not in ('sphincterotomy') )
> order by TempERCP.Examdate, TempERCP.Hospno
>
You need to set ansi_nulls off:
select 'test' pi_therapy into #test
union all
select null
union all
select 'sphincterotomy'
set ansi_nulls on
select pi_therapy from #test where
pi_therapy not in ('sphincterotomy')
set ansi_nulls off
select pi_therapy from #test where
pi_therapy not in ('sphincterotomy')
drop table #test
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate, TempERCP.Hospno,
TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
FROM
TempERCP INNER JOIN
TempDemo ON TempERCP.Hospno = TempDemo.Hospno
where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
( TempERCP.PI_Therapy not in ('sphincterotomy') )
Union
SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate, TempERCP.Hospno,
TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
FROM
TempERCP INNER JOIN
TempDemo ON TempERCP.Hospno = TempDemo.Hospno
where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
( TempERCP.PI_Therapy is Null )
order by TempERCP.Examdate, TempERCP.Hospno
Personally I see the word Sphincterotomy and I just want to run as far and
fast as I can!
"gv" <viatorg@.musc.edu> wrote in message
news:exTZ7J2$FHA.3872@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm drawing a blank on how to do this, I'm trying to return the rows
> where TempERCP.PI_Therapy column does not contain 'sphincterotomy' but,
> I also want the rows that contain 'Null' there which isn't being returned?
>
> SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate,
> TempERCP.Hospno
> , TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
> FROM
> TempERCP INNER JOIN
> TempDemo ON TempERCP.Hospno = TempDemo.Hospno
> where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
> ( TempERCP.PI_Therapy not in ('sphincterotomy') )
> order by TempERCP.Examdate, TempERCP.Hospno
> thanks
> gv
>
>|||thanks guys
gv
"gv" <viatorg@.musc.edu> wrote in message
news:exTZ7J2$FHA.3872@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm drawing a blank on how to do this, I'm trying to return the rows
> where TempERCP.PI_Therapy column does not contain 'sphincterotomy' but,
> I also want the rows that contain 'Null' there which isn't being returned?
>
> SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate,
> TempERCP.Hospno
> , TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
> FROM
> TempERCP INNER JOIN
> TempDemo ON TempERCP.Hospno = TempDemo.Hospno
> where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
> ( TempERCP.PI_Therapy not in ('sphincterotomy') )
> order by TempERCP.Examdate, TempERCP.Hospno
> thanks
> gv
>
>
I'm drawing a blank on how to do this, I'm trying to return the rows
where TempERCP.PI_Therapy column does not contain 'sphincterotomy' but,
I also want the rows that contain 'Null' there which isn't being returned?
SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate, TempERCP.Hospno
, TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
FROM
TempERCP INNER JOIN
TempDemo ON TempERCP.Hospno = TempDemo.Hospno
where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
( TempERCP.PI_Therapy not in ('sphincterotomy') )
order by TempERCP.Examdate, TempERCP.Hospno
thanks
gvdu... I think this is it
( TempERCP.PI_Therapy not in ('sphincterotomy')or TempERCP.PI_Therapy is
null )
would I use this COALESCE? example of using this
which is better?
thanks
gv
"gv" <viatorg@.musc.edu> wrote in message
news:exTZ7J2$FHA.3872@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm drawing a blank on how to do this, I'm trying to return the rows
> where TempERCP.PI_Therapy column does not contain 'sphincterotomy' but,
> I also want the rows that contain 'Null' there which isn't being returned?
>
> SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate,
> TempERCP.Hospno
> , TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
> FROM
> TempERCP INNER JOIN
> TempDemo ON TempERCP.Hospno = TempDemo.Hospno
> where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
> ( TempERCP.PI_Therapy not in ('sphincterotomy') )
> order by TempERCP.Examdate, TempERCP.Hospno
> thanks
> gv
>
>|||gv wrote:
> Hi,
> I'm drawing a blank on how to do this, I'm trying to return the rows
> where TempERCP.PI_Therapy column does not contain 'sphincterotomy'
> but,
> I also want the rows that contain 'Null' there which isn't being
> returned?
>
> SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate,
> TempERCP.Hospno , TempERCP.Full_Name,TempERCP.PI_Therapy,
> TempERCP.comment FROM
> TempERCP INNER JOIN
> TempDemo ON TempERCP.Hospno = TempDemo.Hospno
> where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
> ( TempERCP.PI_Therapy not in ('sphincterotomy') )
> order by TempERCP.Examdate, TempERCP.Hospno
>
You need to set ansi_nulls off:
select 'test' pi_therapy into #test
union all
select null
union all
select 'sphincterotomy'
set ansi_nulls on
select pi_therapy from #test where
pi_therapy not in ('sphincterotomy')
set ansi_nulls off
select pi_therapy from #test where
pi_therapy not in ('sphincterotomy')
drop table #test
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate, TempERCP.Hospno,
TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
FROM
TempERCP INNER JOIN
TempDemo ON TempERCP.Hospno = TempDemo.Hospno
where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
( TempERCP.PI_Therapy not in ('sphincterotomy') )
Union
SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate, TempERCP.Hospno,
TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
FROM
TempERCP INNER JOIN
TempDemo ON TempERCP.Hospno = TempDemo.Hospno
where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
( TempERCP.PI_Therapy is Null )
order by TempERCP.Examdate, TempERCP.Hospno
Personally I see the word Sphincterotomy and I just want to run as far and
fast as I can!
"gv" <viatorg@.musc.edu> wrote in message
news:exTZ7J2$FHA.3872@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm drawing a blank on how to do this, I'm trying to return the rows
> where TempERCP.PI_Therapy column does not contain 'sphincterotomy' but,
> I also want the rows that contain 'Null' there which isn't being returned?
>
> SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate,
> TempERCP.Hospno
> , TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
> FROM
> TempERCP INNER JOIN
> TempDemo ON TempERCP.Hospno = TempDemo.Hospno
> where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
> ( TempERCP.PI_Therapy not in ('sphincterotomy') )
> order by TempERCP.Examdate, TempERCP.Hospno
> thanks
> gv
>
>|||thanks guys
gv
"gv" <viatorg@.musc.edu> wrote in message
news:exTZ7J2$FHA.3872@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm drawing a blank on how to do this, I'm trying to return the rows
> where TempERCP.PI_Therapy column does not contain 'sphincterotomy' but,
> I also want the rows that contain 'Null' there which isn't being returned?
>
> SELECT CONVERT(varchar,TempERCP.Examdate, 111)as Examdate,
> TempERCP.Hospno
> , TempERCP.Full_Name,TempERCP.PI_Therapy, TempERCP.comment
> FROM
> TempERCP INNER JOIN
> TempDemo ON TempERCP.Hospno = TempDemo.Hospno
> where TempERCP.Examdate between '01/01/1996' and '10/01/2005' and
> ( TempERCP.PI_Therapy not in ('sphincterotomy') )
> order by TempERCP.Examdate, TempERCP.Hospno
> thanks
> gv
>
>
checking for null statement in sql
Hi Everyone,
I have a query
select name, address, city from table1
I want to append address +'-' + city in this query . I want to show hyphen only if both address and city are not null. If name is null then I don't want to show hyphen. How can I get around this problem.
Any help will be appreciated.
Thanks,
Vinki
It depends on how you're displaying it -
Also - will there be mulitple records returned?
You could keep the query the way it is - but, based on what is returned, with each row, write a function to do that for you, display-wise only.
|||If you want to do it within your query then look at the CASE statement -see here for details. The idea is you do something like:SELECT Name, CASE Address + City WHEN NULL THEN NULL ELSE Address + ' - ' + City END FROM Table1
You'll need to put in more conditions, but that's the basic syntax.
Another alternative is to look at user-defined functions.|||I'd suggest this:
SELECT Name, Address + ISNULL(Address + City,'','-') + City FROM Table1I don't understand this part of your requirement, though:
vinki wrote:
If name is null then I don't want to show hyphen
Checking for NULL parameters in an expression
I have a report parameter that is NULLable. If it's NULL, I'd like a label on my report to say that it's null. If it's not NULL, the label will simply show the value. How do I check, in an expression, for a NULL parameter?IF whatever IS System.DBNull.Value then
Checking for null in Xtab query
I have the following query and it works. Where the left part of the join doe
s
not have any records i'd like to return a zero. I tried the case but, ...
thanks all
kes
select
c.calId,
c.calDate,
c.calShortDesc,
c.calDesc,
c.CalHoliday,
e3.evtAll,
CASE e3.NS WHEN NULL then 0 else e3.NS END NS,
e3.SS,
e3.WS,
e3.C,
e3.WE,
e3.ED,
e3.NW
from cal c
LEFT Join (select e.evtDateTime, count(e.evtDateTime) EvtALL,
(select count(e2.evtLocID) from evt e2 where evtlocID = 1 and e2.evtdatetime
= e.evtdatetime) as NS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 2 and e2.evtdatetime
= e.evtdatetime) as SS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 3 and e2.evtdatetime
= e.evtdatetime) as WS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 4 and e2.evtdatetime
= e.evtdatetime) as C,
(select count(e2.evtCatID) from evt e2 where evtCatID = 1 and e2.evtdatetime
= e.evtdatetime) as WE,
(select count(e2.evtCatID) from evt e2 where evtCatID = 2 and e2.evtdatetime
= e.evtdatetime) as ED,
(select count(e2.evtCatID) from evt e2 where evtCatID = 3 and e2.evtdatetime
= e.evtdatetime) as NW
from evt e
group by e.evtDatetime) e3 on e3.evtDateTime = c.calDate
where c.calDate >= '20050626' and c.calDate <= '20050806'
(I can post the Data def, but this is a simple question a bout returning a
zero for null)will this work?
CASE WHEN e3.NS IS NULL then 0 else e3.NS END NS?
or is there a better idea?
thanks
kes
"WebBuilder451" wrote:
> I have the following query and it works. Where the left part of the join d
oes
> not have any records i'd like to return a zero. I tried the case but, ...
> thanks all
> kes
> select
> c.calId,
> c.calDate,
> c.calShortDesc,
> c.calDesc,
> c.CalHoliday,
> e3.evtAll,
> CASE e3.NS WHEN NULL then 0 else e3.NS END NS,
> e3.SS,
> e3.WS,
> e3.C,
> e3.WE,
> e3.ED,
> e3.NW
> from cal c
> LEFT Join (select e.evtDateTime, count(e.evtDateTime) EvtALL,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 1 and e2.evtdateti
me
> = e.evtdatetime) as NS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 2 and e2.evtdateti
me
> = e.evtdatetime) as SS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 3 and e2.evtdateti
me
> = e.evtdatetime) as WS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 4 and e2.evtdateti
me
> = e.evtdatetime) as C,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 1 and e2.evtdateti
me
> = e.evtdatetime) as WE,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 2 and e2.evtdateti
me
> = e.evtdatetime) as ED,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 3 and e2.evtdateti
me
> = e.evtdatetime) as NW
> from evt e
> group by e.evtDatetime) e3 on e3.evtDateTime = c.calDate
> where c.calDate >= '20050626' and c.calDate <= '20050806'
> (I can post the Data def, but this is a simple question a bout returning a
> zero for null)
>
>|||Yes, that will work.
Why haven't you tried it?
ML|||You don't even need a Case expression. The following will work:
IsNull(e3.NS,0) as NS
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:F506A0DE-48CD-40DE-8982-532DC5390C05@.microsoft.com...
> will this work?
> CASE WHEN e3.NS IS NULL then 0 else e3.NS END NS?
> or is there a better idea?
> thanks
> kes
> "WebBuilder451" wrote:
>|||that's the answer !!!
thanks
IOU1 kes
"JT" wrote:
> You don't even need a Case expression. The following will work:
> IsNull(e3.NS,0) as NS
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:F506A0DE-48CD-40DE-8982-532DC5390C05@.microsoft.com...
>
>
s
not have any records i'd like to return a zero. I tried the case but, ...
thanks all
kes
select
c.calId,
c.calDate,
c.calShortDesc,
c.calDesc,
c.CalHoliday,
e3.evtAll,
CASE e3.NS WHEN NULL then 0 else e3.NS END NS,
e3.SS,
e3.WS,
e3.C,
e3.WE,
e3.ED,
e3.NW
from cal c
LEFT Join (select e.evtDateTime, count(e.evtDateTime) EvtALL,
(select count(e2.evtLocID) from evt e2 where evtlocID = 1 and e2.evtdatetime
= e.evtdatetime) as NS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 2 and e2.evtdatetime
= e.evtdatetime) as SS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 3 and e2.evtdatetime
= e.evtdatetime) as WS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 4 and e2.evtdatetime
= e.evtdatetime) as C,
(select count(e2.evtCatID) from evt e2 where evtCatID = 1 and e2.evtdatetime
= e.evtdatetime) as WE,
(select count(e2.evtCatID) from evt e2 where evtCatID = 2 and e2.evtdatetime
= e.evtdatetime) as ED,
(select count(e2.evtCatID) from evt e2 where evtCatID = 3 and e2.evtdatetime
= e.evtdatetime) as NW
from evt e
group by e.evtDatetime) e3 on e3.evtDateTime = c.calDate
where c.calDate >= '20050626' and c.calDate <= '20050806'
(I can post the Data def, but this is a simple question a bout returning a
zero for null)will this work?
CASE WHEN e3.NS IS NULL then 0 else e3.NS END NS?
or is there a better idea?
thanks
kes
"WebBuilder451" wrote:
> I have the following query and it works. Where the left part of the join d
oes
> not have any records i'd like to return a zero. I tried the case but, ...
> thanks all
> kes
> select
> c.calId,
> c.calDate,
> c.calShortDesc,
> c.calDesc,
> c.CalHoliday,
> e3.evtAll,
> CASE e3.NS WHEN NULL then 0 else e3.NS END NS,
> e3.SS,
> e3.WS,
> e3.C,
> e3.WE,
> e3.ED,
> e3.NW
> from cal c
> LEFT Join (select e.evtDateTime, count(e.evtDateTime) EvtALL,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 1 and e2.evtdateti
me
> = e.evtdatetime) as NS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 2 and e2.evtdateti
me
> = e.evtdatetime) as SS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 3 and e2.evtdateti
me
> = e.evtdatetime) as WS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 4 and e2.evtdateti
me
> = e.evtdatetime) as C,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 1 and e2.evtdateti
me
> = e.evtdatetime) as WE,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 2 and e2.evtdateti
me
> = e.evtdatetime) as ED,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 3 and e2.evtdateti
me
> = e.evtdatetime) as NW
> from evt e
> group by e.evtDatetime) e3 on e3.evtDateTime = c.calDate
> where c.calDate >= '20050626' and c.calDate <= '20050806'
> (I can post the Data def, but this is a simple question a bout returning a
> zero for null)
>
>|||Yes, that will work.
Why haven't you tried it?
ML|||You don't even need a Case expression. The following will work:
IsNull(e3.NS,0) as NS
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:F506A0DE-48CD-40DE-8982-532DC5390C05@.microsoft.com...
> will this work?
> CASE WHEN e3.NS IS NULL then 0 else e3.NS END NS?
> or is there a better idea?
> thanks
> kes
> "WebBuilder451" wrote:
>|||that's the answer !!!
thanks
IOU1 kes
"JT" wrote:
> You don't even need a Case expression. The following will work:
> IsNull(e3.NS,0) as NS
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:F506A0DE-48CD-40DE-8982-532DC5390C05@.microsoft.com...
>
>
Checking for NULL dates in Report
Hi,
I have a date field in my report dataset. I want to check for NULL date
before displaying it in the report. I have tried using the exp
=IIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day(Fields!Date.Value)&"/"&Year(Fields!Date.Value)).
But this doesnt work. I see #error in my report where ever date value is
NULL or blank. I have also tried using Len and IsDate functions but they too
dont seem to work. Can anyone help me ?
Thanks,
RKHi,
Tue and False part are evaluated in a IIF function. That's why you have an
#error.
You can use Visibility property.
--
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"ramakrishna" <ramakrishna@.translogicsys.com> wrote in message
news:O2Cm3S%23ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a date field in my report dataset. I want to check for NULL date
> before displaying it in the report. I have tried using the exp
> =IIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day(Fields!Date.Value)&"/"&Year(Fields!Date.Value)).
> But this doesnt work. I see #error in my report where ever date value is
> NULL or blank. I have also tried using Len and IsDate functions but they
> too dont seem to work. Can anyone help me ?
> Thanks,
> RK
>
>
>|||Hi,
Thanks for the reply. I want to display the date only when its not null. So
I have used the IIF expression. But the checking using the function
IsNothing or IsDate doesnt seem to work.
--
RK
"Jean-Pierre Riehl" <jean-pierre.riehl@.bewise.fr> wrote in message
news:19928F8F-CDF8-4F4F-8C6A-C8B50D13C5C7@.microsoft.com...
> Hi,
> Tue and False part are evaluated in a IIF function. That's why you have an
> #error.
> You can use Visibility property.
> --
> Jean-Pierre Riehl
> http://blog.djeepy1.net
> http://www.bewise.fr
>
> "ramakrishna" <ramakrishna@.translogicsys.com> wrote in message
> news:O2Cm3S%23ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have a date field in my report dataset. I want to check for NULL date
>> before displaying it in the report. I have tried using the exp
>> =IIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day(Fields!Date.Value)&"/"&Year(Fields!Date.Value)).
>> But this doesnt work. I see #error in my report where ever date value is
>> NULL or blank. I have also tried using Len and IsDate functions but they
>> too dont seem to work. Can anyone help me ?
>> Thanks,
>> RK
>>
>>
>|||On Feb 5, 11:49=A0pm, "ramakrishna" <ramakris...@.translogicsys.com>
wrote:
> Hi,
> Thanks for the reply. I want to display the date only when its not null. S=o
> I have used the IIF expression. But the checking using the function
> IsNothing or IsDate doesnt seem to work.
> --
> RK"Jean-Pierre Riehl" <jean-pierre.ri...@.bewise.fr> wrote in message
> news:19928F8F-CDF8-4F4F-8C6A-C8B50D13C5C7@.microsoft.com...
>
> > Hi,
> > Tue and False part are evaluated in a IIF function. That's why you have =an
> > #error.
> > You can use Visibility property.
> > --
> > Jean-Pierre Riehl
> >http://blog.djeepy1.net
> >http://www.bewise.fr
> > "ramakrishna" <ramakris...@.translogicsys.com> wrote in message
> >news:O2Cm3S%23ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
> >> Hi,
> >> I have a date field in my report dataset. I want to check for NULL date=
> >> before displaying it in the report. I have tried using the exp
> >> =3DIIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day=(Field=ADs!Date.Value)&"/"&Year(Fields!Date.Value)).
> >> But this doesnt work. I see #error in my report where ever date value i=s
> >> NULL or blank. I have also tried using Len and IsDate functions but the=y
> >> too dont seem to work. Can anyone help me ?
> >> Thanks,
> >> RK- Hide quoted text -
> - Show quoted text -
Any value that returns "NULL" from your database is treated like a
"Nothing" object in .Net. Wherever possible, you will want to use the
IsNothing function. If I recall, Len(Nothing) returns Nothing, not 0
Second, the Month, Day and Year functions expect a Date object. It is
possible that you are passing them a Text fieldand it doesn't know how
to parse it. This could be why the function is returning a #error.
Third, when doing Date formatting, you should probably use the Format
function instead of breaking the date apart.
=3DIIF( IsNothing(Fields!Date.Value), "", Format( CDate(Fields!
Date.Value), "M/d/yyyy" ) )
-- Scott
I have a date field in my report dataset. I want to check for NULL date
before displaying it in the report. I have tried using the exp
=IIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day(Fields!Date.Value)&"/"&Year(Fields!Date.Value)).
But this doesnt work. I see #error in my report where ever date value is
NULL or blank. I have also tried using Len and IsDate functions but they too
dont seem to work. Can anyone help me ?
Thanks,
RKHi,
Tue and False part are evaluated in a IIF function. That's why you have an
#error.
You can use Visibility property.
--
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"ramakrishna" <ramakrishna@.translogicsys.com> wrote in message
news:O2Cm3S%23ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a date field in my report dataset. I want to check for NULL date
> before displaying it in the report. I have tried using the exp
> =IIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day(Fields!Date.Value)&"/"&Year(Fields!Date.Value)).
> But this doesnt work. I see #error in my report where ever date value is
> NULL or blank. I have also tried using Len and IsDate functions but they
> too dont seem to work. Can anyone help me ?
> Thanks,
> RK
>
>
>|||Hi,
Thanks for the reply. I want to display the date only when its not null. So
I have used the IIF expression. But the checking using the function
IsNothing or IsDate doesnt seem to work.
--
RK
"Jean-Pierre Riehl" <jean-pierre.riehl@.bewise.fr> wrote in message
news:19928F8F-CDF8-4F4F-8C6A-C8B50D13C5C7@.microsoft.com...
> Hi,
> Tue and False part are evaluated in a IIF function. That's why you have an
> #error.
> You can use Visibility property.
> --
> Jean-Pierre Riehl
> http://blog.djeepy1.net
> http://www.bewise.fr
>
> "ramakrishna" <ramakrishna@.translogicsys.com> wrote in message
> news:O2Cm3S%23ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have a date field in my report dataset. I want to check for NULL date
>> before displaying it in the report. I have tried using the exp
>> =IIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day(Fields!Date.Value)&"/"&Year(Fields!Date.Value)).
>> But this doesnt work. I see #error in my report where ever date value is
>> NULL or blank. I have also tried using Len and IsDate functions but they
>> too dont seem to work. Can anyone help me ?
>> Thanks,
>> RK
>>
>>
>|||On Feb 5, 11:49=A0pm, "ramakrishna" <ramakris...@.translogicsys.com>
wrote:
> Hi,
> Thanks for the reply. I want to display the date only when its not null. S=o
> I have used the IIF expression. But the checking using the function
> IsNothing or IsDate doesnt seem to work.
> --
> RK"Jean-Pierre Riehl" <jean-pierre.ri...@.bewise.fr> wrote in message
> news:19928F8F-CDF8-4F4F-8C6A-C8B50D13C5C7@.microsoft.com...
>
> > Hi,
> > Tue and False part are evaluated in a IIF function. That's why you have =an
> > #error.
> > You can use Visibility property.
> > --
> > Jean-Pierre Riehl
> >http://blog.djeepy1.net
> >http://www.bewise.fr
> > "ramakrishna" <ramakris...@.translogicsys.com> wrote in message
> >news:O2Cm3S%23ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
> >> Hi,
> >> I have a date field in my report dataset. I want to check for NULL date=
> >> before displaying it in the report. I have tried using the exp
> >> =3DIIF(IsNothing(Fields!Date.Value),"",Month(Fields!Date.Value)&"/"&Day=(Field=ADs!Date.Value)&"/"&Year(Fields!Date.Value)).
> >> But this doesnt work. I see #error in my report where ever date value i=s
> >> NULL or blank. I have also tried using Len and IsDate functions but the=y
> >> too dont seem to work. Can anyone help me ?
> >> Thanks,
> >> RK- Hide quoted text -
> - Show quoted text -
Any value that returns "NULL" from your database is treated like a
"Nothing" object in .Net. Wherever possible, you will want to use the
IsNothing function. If I recall, Len(Nothing) returns Nothing, not 0
Second, the Month, Day and Year functions expect a Date object. It is
possible that you are passing them a Text fieldand it doesn't know how
to parse it. This could be why the function is returning a #error.
Third, when doing Date formatting, you should probably use the Format
function instead of breaking the date apart.
=3DIIF( IsNothing(Fields!Date.Value), "", Format( CDate(Fields!
Date.Value), "M/d/yyyy" ) )
-- Scott
Sunday, February 12, 2012
Checkbox data-bound to a bit SQL field cant handle NULL values
I have a checkbox on my webform that is bound to a bit field in my SQL table. I'm fine as long as I've got the bit field set to 0 or 1, but if the field is NULL, the checkbox throws an exception during the databind.
Is there any way to handle this without removing the data binding and manually setting the value (ie: some way to intercept it before the exception gets thrown and then setting the field value in the dataset)?
Thanks!Hi,
You may try to use the ISNULL() function in your SELECT statement,
or you can use aHelper Function to do so.
Colt
Subscribe to:
Posts (Atom)