Numeric value with comma separator...

Hi,
In select statement how will i get the numeric values with comma separated
format .
Is there any sql function available.
Regards,
M. SubbaiahSomeone was asleep in their Database 101 class! What is the **most
fundamental** concept in tiered architecture? DISPLAY IS ALWAYS DONE
IN THE CLIENT SIDE!!
Can you please stop programming until you have read at least one book?|||Hi
As Celko pointed out yet you will be better of douing such reports on the
client side
However T-SQL has an ability to do that .
CREATE TABLE #Test (col INT NOT NULL)
INSERT INTO #Test VALUES (1)
INSERT INTO #Test VALUES (10)
INSERT INTO #Test VALUES (20)
DECLARE @.st VARCHAR(20)
SET @.st=''
SELECT @.st=@.st+COALESCE(CAST(col AS VARCHAR(5)),'0')+','
FROM #test
SELECT LEFT(@.st,LEN(@.st)-1)
"Subbaiah" <subbaiah@.cspl.com> wrote in message
news:eqflLkeMGHA.3272@.tk2msftngp13.phx.gbl...
> Hi,
> In select statement how will i get the numeric values with comma separated
> format .
> Is there any sql function available.
> Regards,
> M. Subbaiah
>|||Hi Uri Dimant,
Thanks for your information.
I learned new sql function COALESCE( ) and the usage.
My posted query was ,
Suppose in sql table the value is 1234567.45
My out put wiill be 1,234,567.45
Can you please answer the above one.
Regards
M. Subbaiah
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OIp4qvfMGHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi
> As Celko pointed out yet you will be better of douing such reports on the
> client side
> However T-SQL has an ability to do that .
> CREATE TABLE #Test (col INT NOT NULL)
> INSERT INTO #Test VALUES (1)
> INSERT INTO #Test VALUES (10)
> INSERT INTO #Test VALUES (20)
>
> DECLARE @.st VARCHAR(20)
> SET @.st=''
> SELECT @.st=@.st+COALESCE(CAST(col AS VARCHAR(5)),'0')+','
> FROM #test
> SELECT LEFT(@.st,LEN(@.st)-1)
>
>
>
> "Subbaiah" <subbaiah@.cspl.com> wrote in message
> news:eqflLkeMGHA.3272@.tk2msftngp13.phx.gbl...
>|||NO!
Display is ALWAYS done where it is most efficient to do it.
You DO NOT pull back 1 MILLION rows into your middle tier or client tier
only to grab page 2 of 10!
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1139978481.527767.63680@.z14g2000cwz.googlegroups.com...
> Someone was asleep in their Database 101 class! What is the **most
> fundamental** concept in tiered architecture? DISPLAY IS ALWAYS DONE
> IN THE CLIENT SIDE!!
> Can you please stop programming until you have read at least one book?
>|||If you want to cheat, use the money data type and convert:
declare @.someFloat money
set @.someFloat = 1234567.45
select convert(varchar(15), @.someFloat, 1)
Gives:
1,234,567.45
Cheers,
Stefan
http://www.fotia.co.uk
> Hi Uri Dimant,
> Thanks for your information.
> I learned new sql function COALESCE( ) and the usage.
> My posted query was ,
> Suppose in sql table the value is 1234567.45
> My out put wiill be 1,234,567.45
> Can you please answer the above one.
> Regards
> M. Subbaiah
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OIp4qvfMGHA.3556@.TK2MSFTNGP10.phx.gbl...
>|||Hi
declare @.someDEC DECIMAL(18,2)
set @.someDEC = 1234567.45
SELECT CONVERT(VARCHAR,CAST(@.someDEC AS MONEY),1)
"Subbaiah" <subbaiah@.cspl.com> wrote in message
news:uqE9mUgMGHA.2668@.tk2msftngp13.phx.gbl...
> Hi Uri Dimant,
> Thanks for your information.
> I learned new sql function COALESCE( ) and the usage.
> My posted query was ,
> Suppose in sql table the value is 1234567.45
> My out put wiill be 1,234,567.45
> Can you please answer the above one.
> Regards
> M. Subbaiah
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OIp4qvfMGHA.3556@.TK2MSFTNGP10.phx.gbl...
>sql

Numeric value only for a character field

Hi
I have a character field (char ot varchar) that I want to force only to
contain numeric characters.
Can that be done by way of defining a constraint on the field ?
or by any other way in the field/table definition ?
What id the syntax ?
Anyone have examples ?

Thanks

David GreenbergOn Thu, 02 Aug 2007 10:05:40 +0200, David Greenberg wrote:

Quote:

Originally Posted by

>Hi
>I have a character field (char ot varchar) that I want to force only to
>contain numeric characters.
>Can that be done by way of defining a constraint on the field ?
>or by any other way in the field/table definition ?
>What id the syntax ?
>Anyone have examples ?

Hi David,

The obvious solution is to declare the column with one of the numeric
data types instead of char or varchar.

But if you insist on using character columns for numeric data, you can
add a CHECK constraint:

CREATE TABLE Example
(SomeCol char(9) CHECK (SomeCol NOT LIKE '%[^0-9]%'));

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Numer formatting question

Hi,
I have a number formating question.
If the value is less than 0, I would like the formatting to look like
(12,345).
If the value is greater than 0, I would like the formatting to look like
12,345.
If the value is equal to 0, I would like a - to be displayed.
I tried =iif( Fields!SPECIALTY_LEASING.Value=0,"-",iif(
Fields!SPECIALTY_LEASING.Value <0,"(0,0)","N0")) but I get a - every time.
Thanks,
MelissaMelissa,
Try the format string #,##0;(#,##0);\-
You must have the backslash character '\' before the dash '-' character. If
you want more than one dash you would have to have \-\-\- in the third part
of the format string. Or, if you want the word Zero you'd have to use
\Z\e\r\o.
HTH,
magendo_man
Stirling, Scotland
"Me" wrote:
> Hi,
> I have a number formating question.
> If the value is less than 0, I would like the formatting to look like
> (12,345).
> If the value is greater than 0, I would like the formatting to look like
> 12,345.
> If the value is equal to 0, I would like a - to be displayed.
> I tried =iif( Fields!SPECIALTY_LEASING.Value=0,"-",iif(
> Fields!SPECIALTY_LEASING.Value <0,"(0,0)","N0")) but I get a - every time.
> Thanks,
> Melissa
>|||That worked!! Thank you so much!
Melissa
"magendo_man" wrote:
> Melissa,
> Try the format string #,##0;(#,##0);\-
> You must have the backslash character '\' before the dash '-' character. If
> you want more than one dash you would have to have \-\-\- in the third part
> of the format string. Or, if you want the word Zero you'd have to use
> \Z\e\r\o.
> HTH,
> magendo_man
> Stirling, Scotland
>
> "Me" wrote:
> > Hi,
> >
> > I have a number formating question.
> >
> > If the value is less than 0, I would like the formatting to look like
> > (12,345).
> > If the value is greater than 0, I would like the formatting to look like
> > 12,345.
> > If the value is equal to 0, I would like a - to be displayed.
> >
> > I tried =iif( Fields!SPECIALTY_LEASING.Value=0,"-",iif(
> > Fields!SPECIALTY_LEASING.Value <0,"(0,0)","N0")) but I get a - every time.
> >
> > Thanks,
> > Melissa
> >

Numbering column with a start number of 109

Hi,
I have a table as follows:
StatId AgencyID Value
1 10
2 47
3 38
4 59
5 60
.. ..
All the fields in the StatId field is blank. However,
I have to fill up the field in statId starting from
109 with increment of 1 for each row. Altogether I have
about 10,000 row in the above agency table.
Any help as to how to proceed programmatically is highly
appreciated. Thanks in advance.How will you determine what the order should be? That is, should the row
with AgencyID = 10 have a StatId of 109?
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> Hi,
> I have a table as follows:
> StatId AgencyID Value
> 1 10
> 2 47
> 3 38
> 4 59
> 5 60
> .. ..
> All the fields in the StatId field is blank. However,
> I have to fill up the field in statId starting from
> 109 with increment of 1 for each row. Altogether I have
> about 10,000 row in the above agency table.
> Any help as to how to proceed programmatically is highly
> appreciated. Thanks in advance.|||What's the logic then? How will you programatically number these rows?
You'll probably have to write a loop to manually update the rows, one by
one, based on whatever logic you're ordering them by.
Or, you could try creating a new table with StatId INT IDENTITY(109, 1),
then insert the entire batch at once using INSERT SELECT, with an ORDER BY,
but there is no guarantee that the rows will show up in the right order. So
although you could try that, it may not work the way you want.
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:65435A6C-DB6B-4828-A4F4-754005BB31E7@.microsoft.com...
> That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
> "Adam Machanic" wrote:
> > How will you determine what the order should be? That is, should the
row
> > with AgencyID = 10 have a StatId of 109?
> >
> >
> > "Jack" <Jack@.discussions.microsoft.com> wrote in message
> > news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> > > Hi,
> > > I have a table as follows:
> > >
> > > StatId AgencyID Value
> > > 1 10
> > > 2 47
> > > 3 38
> > > 4 59
> > > 5 60
> > > .. ..
> > > All the fields in the StatId field is blank. However,
> > > I have to fill up the field in statId starting from
> > > 109 with increment of 1 for each row. Altogether I have
> > > about 10,000 row in the above agency table.
> > >
> > > Any help as to how to proceed programmatically is highly
> > > appreciated. Thanks in advance.
> >
> >
> >

Number of rows depending on a column value

Hi all,

I have a table with artikels and count, sample:

Art Count

12A 3

54G 2

54A 4

I would like to query this table and for each 'count' retrieve one row:

query result:

Art Count

12A 3

12A 3

12A 3

54G 2

54G 2

54A 4

54A 4

54A 4

54A 4

Is this possible?

Thanks, Perry

Certainly one way to do this would be to join with a table of numbers. Give a look to this article about a table of numbers here. In the meantime, I will see about getting you an example. Also, if this info is meant for a front end application, you might consider doing this work in the application instead of the database. Here is an example:

Code Snippet

declare @.artikels table
( Art varchar(5),
[Count] integer
)
insert into @.Artikels
select '12A', 3 union all
select '54G', 2 union all
select '54A', 4

select Art, [count]
from @.artikels
join numbers
on [count] >= n
and n <= 10 -- setting an arbitrary upper bound
order by Art

/*
Art count
-- --
12A 3
12A 3
12A 3
54A 4
54A 4
54A 4
54A 4
54G 2
54G 2
*/

|||

Thanks

This can do the job, but not the (easy) way i was looking for. I need a view to feed a report writer (data dynamics active reports) to print barcodes, for each product 1 barcode.

Now i will query the view and copy records in the resultset as many as needed, then feed this dataset to the report writer

Thanks

Perry

I

Number of rows depending on a column value

Hi all,

I have a table with artikels and count, sample:

Art Count

12A 3

54G 2

54A 4

I would like to query this table and for each 'count' retrieve one row:

query result:

Art Count

12A 3

12A 3

12A 3

54G 2

54G 2

54A 4

54A 4

54A 4

54A 4

Is this possible?

Thanks, Perry

Certainly one way to do this would be to join with a table of numbers. Give a look to this article about a table of numbers here. In the meantime, I will see about getting you an example. Also, if this info is meant for a front end application, you might consider doing this work in the application instead of the database. Here is an example:

Code Snippet

declare @.artikels table
( Art varchar(5),
[Count] integer
)
insert into @.Artikels
select '12A', 3 union all
select '54G', 2 union all
select '54A', 4

select Art, [count]
from @.artikels
join numbers
on [count] >= n
and n <= 10 -- setting an arbitrary upper bound
order by Art

/*
Art count
-- --
12A 3
12A 3
12A 3
54A 4
54A 4
54A 4
54A 4
54G 2
54G 2
*/

|||

Thanks

This can do the job, but not the (easy) way i was looking for. I need a view to feed a report writer (data dynamics active reports) to print barcodes, for each product 1 barcode.

Now i will query the view and copy records in the resultset as many as needed, then feed this dataset to the report writer

Thanks

Perry

I

number of errorlogs in mssql 7.0

hi,
Is there any setting in mssql 7.0 to change the number of
sqlserver error logs from the default value of 6?
I know for sure - we can change a registry setting for
mssql2000. IS there anything for mssql7.0?
regards,
bharathbharath wrote:
> hi,
> Is there any setting in mssql 7.0 to change the number of
> sqlserver error logs from the default value of 6?
> I know for sure - we can change a registry setting for
> mssql2000. IS there anything for mssql7.0?
> regards,
> bharath
You need to change the registry entry:
HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQ
LServer
DWORD NumErrorLogs = <xx>
(answered previously by Neil Pike, MVP)
http://groups.google.pl/groups?hl=p...&rnu
m=4
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.sql

Nulls in SQL 2005

Hello,

I have a varchar(20) field which stores a value or empty string.

I have noticed that if the value is NULL and I use is null in my query, the query runs very fast. However If I also include in my query a check for an empty string (len(rtrim(field)) = 0) the query is much slower.

Is it a good idea to force the empty strings to null and if Yes is there an automatic way in sql 2005 (maybe a trigger) to update all empty strings to null.

Thanks, I just need some advice.

Michael

The difference may be coming from the use of functions than checking for an empty string versus a null string. The optimizer cannot make use of an expression in the same way as it makes use of a field.

Using a trigger to turn blanks into null is not a bad idea, but it is not without overhead either. Also consider whether a blank string represents something different than a NULL. You might also want to revisit your data model and see whether it makes more sense to put the value in a separate table and remove the entry when it is not necessary.

|||One more option is to create computed column and index over it, but there are some requirements for that (refer for details: http://msdn2.microsoft.com/en-us/library/ms189292.aspx )|||

HI

U can also use this query

select * from table1 where col1 is null or col1 = ''

Nulls -conditional display of value in Visual Studio

I am trying to display one of 2 values based on an input parameter. I have
created a field which is supposed to evaluate the parameter and display the
appropriate field.
My code is as follows: =iif( Parameters!LastWeek.Value = "Y",
Fields!Measures_M30N_MTD_Last_Week_of_Month.Value,
Fields!Measures_M9N_MTD.Value)
When I preview my report and select "Y" as the parameter, my field displays
nothing. I have checked the data and know there is a value in the field
represented by the "true" portion of my statement.
If I change the staement to this:=iif( Parameters!LastWeek.Value = "Y",
Fields!Measures_M30N_MTD_Last_Week_of_Month.Value, 0)
I actually get the value I am expecting.
My conclusion is the data in the "false" portion of the statement is
actually null or non-existant. I think this is causing my statement to
malfunction. How do I account for this and make my statement work properly?
There will eventually be data in the field represented in the "false"
portion of the statement. The data will reside in either/or both depending
on the time of month.
Thank you... PB> My conclusion is the data in the "false" portion of the statement is
> actually null or non-existant. I think this is causing my statement to
> malfunction.
Assuming this conclusion is correct, you could use a nested IIF() test using
the ISNOTHING() function, like this:
=iif( Parameters!LastWeek.Value = "Y",
Fields!Measures_M30N_MTD_Last_Week_of_Month.Value,
iif(ISNOTHING( Fields!Measures_M9N_MTD.Value), 0,
Fields!Measures_M9N_MTD.Value) )
... however (still assuming your conclusion about why it's not working is
correct) it might be safer to assume that *either* value could be missing,
so you could do the test in both places:
=iif( Parameters!LastWeek.Value = "Y",
iif(ISNOTHING(Fields!Measures_M30N_MTD_Last_Week_of_Month.Value),0,
Fields!Measures_M30N_MTD_Last_Week_of_Month.Value),
iif(ISNOTHING(Fields!Measures_M9N_MTD.Value), 0,
Fields!Measures_M9N_MTD.Value) )
... double-however <g> when you look at it this way, it might be simpler to
do the same work in your data query. IOW use ISNULL() or COALESCE() in your
SELECT statement, to provide a default value for values that might be
missing, before you get to the report level.
Regards,
>L<
"ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
news:7F96FEB5-806D-4761-8077-4C3A7B85973D@.microsoft.com...
>I am trying to display one of 2 values based on an input parameter. I have
> created a field which is supposed to evaluate the parameter and display
> the
> appropriate field.
> My code is as follows: =iif( Parameters!LastWeek.Value = "Y",
> Fields!Measures_M30N_MTD_Last_Week_of_Month.Value,
> Fields!Measures_M9N_MTD.Value)
> When I preview my report and select "Y" as the parameter, my field
> displays
> nothing. I have checked the data and know there is a value in the field
> represented by the "true" portion of my statement.
> If I change the staement to this:=iif( Parameters!LastWeek.Value = "Y",
> Fields!Measures_M30N_MTD_Last_Week_of_Month.Value, 0)
> I actually get the value I am expecting.
> My conclusion is the data in the "false" portion of the statement is
> actually null or non-existant. I think this is causing my statement to
> malfunction. How do I account for this and make my statement work
> properly?
> There will eventually be data in the field represented in the "false"
> portion of the statement. The data will reside in either/or both
> depending
> on the time of month.
> Thank you... PB

Nulls as 1.1.1900

I would be very thankful if anybody could help me with this:
If there is a date field with a null value it defaults to 1.1.1900
is it possible to change the default to the empty datevalue or ' / / '
Thanks
Bjarni SigurdssonIf the value is indeed NULL, you can detect that by binding an indicator
field for the row/parameter in question.
"gandalf" <anonymous@.discussions.microsoft.com> wrote in message
news:08ed01c4b1ca$4e9c6900$a401280a@.phx.gbl...[vbcol=seagreen]
> a date is internally stored as a number
> You could use a CASE-statement to show another value
>
> this:
> 1.1.1900
> datevalue or ' / / '

Nulls as 1.1.1900

I would be very thankful if anybody could help me with this:
If there is a date field with a null value it defaults to 1.1.1900
is it possible to change the default to the empty datevalue or ' / / '
Thanks
Bjarni Sigurdsson
If the value is indeed NULL, you can detect that by binding an indicator
field for the row/parameter in question.
"gandalf" <anonymous@.discussions.microsoft.com> wrote in message
news:08ed01c4b1ca$4e9c6900$a401280a@.phx.gbl...[vbcol=seagreen]
> a date is internally stored as a number
> You could use a CASE-statement to show another value
> this:
> 1.1.1900
> datevalue or ' / / '

nullifying value from FOR XML EXPLICIT

In this query (using T-SQL, don't know about SQLXML) hello becomes NULL and an error is returned...
select 2 as tag, null as parent
,null as [ffffff:eeeeeeeeeeeeeee!2!bbbbbbbbbbbbbbbbbbbbbbbb !element]
,null as [ffffff:eeeeeeeeeeeeeee!2!cccccccccccccccccccccccc !element]
,null as [aaaaaaaaaaaaaaaaaaaaaaa!19]
union all
select 19 as tag, 2 as parent
,null as [ffffff:eeeeeeeeeeeeeee!2!bbbbbbbbbbbbbbbbbbbbbbbb !element]
,null as [ffffff:eeeeeeeeeeeeeee!2!cccccccccccccccccccccccc !element]
,'hello' as [aaaaaaaaaaaaaaaaaaaaaaa!19]
for xml explicit
But change just about anything (make it xml auto, add or remove a few chars, change the 2 to a 12 or the 19 to either a 9 or a 190...) and it will work fine.
Anyone know what gives? Seems to be related to string lengths... Our workaround is to start tag ID's at >100 but does that mean some other combination will flake out?
Cheers...
John
Try changing alias [aaaaaaaaaaaaaaaaaaaaaaa!19] to
[aaaaaaaaaaaaaaaaaaaaaaa!19!] - there's a known bug in FOR XML EXPLICIT
code.
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Nowak" <anonymous@.discussions.microsoft.com> wrote in message
news:08168845-CD3E-47DD-A4A9-686AF2B2525F@.microsoft.com...
> In this query (using T-SQL, don't know about SQLXML) hello becomes NULL
and an error is returned...
> select 2 as tag, null as parent
> ,null as [ffffff:eeeeeeeeeeeeeee!2!bbbbbbbbbbbbbbbbbbbbbbbb !element]
> ,null as [ffffff:eeeeeeeeeeeeeee!2!cccccccccccccccccccccccc !element]
> ,null as [aaaaaaaaaaaaaaaaaaaaaaa!19]
> union all
> select 19 as tag, 2 as parent
> ,null as [ffffff:eeeeeeeeeeeeeee!2!bbbbbbbbbbbbbbbbbbbbbbbb !element]
> ,null as [ffffff:eeeeeeeeeeeeeee!2!cccccccccccccccccccccccc !element]
> ,'hello' as [aaaaaaaaaaaaaaaaaaaaaaa!19]
> for xml explicit
> But change just about anything (make it xml auto, add or remove a few
chars, change the 2 to a 12 or the 19 to either a 9 or a 190...) and it
will work fine.
> Anyone know what gives? Seems to be related to string lengths... Our
workaround is to start tag ID's at >100 but does that mean some other
combination will flake out?
> Cheers...
> John

nullable and unique column

I created a table and one of its column can be null, or
unique value.
it turned out that I could only insert one null record for
this column,
I had 'duplicate key...' error when trying to insert the
second null for this column.
what is the way to make a column that is nullable for more
than one record, and
is unique if the data is not null?
thanks!One way to achieve that is to create a view that selects only the
non-NULL rows and create a unique clustered index on the relevant
columns.
HTH,
Gert-Jan
amy wrote:
> I created a table and one of its column can be null, or
> unique value.
> it turned out that I could only insert one null record for
> this column,
> I had 'duplicate key...' error when trying to insert the
> second null for this column.
> what is the way to make a column that is nullable for more
> than one record, and
> is unique if the data is not null?
> thanks!|||Hi Amy,
Thank you for using MSDN Newsgroup! My name is Billy and it's my pleasure to assist you
with your issue.
From your description, I understand that you would like to allow multiple nulls in a UNIQUE
column. Have I fully understood you? If there is anything I misunderstood, please feel free to
let me know.
Unfortunately, this feature is not allowed in relational databases that adhere to the SQL
standard. As we know, Null, by definition is an "unknown" value and is not equal to any other
value even the null itself.
However, UNIQUE constraint regards Null as other normal values. In this way, a unique
column only allow one Null. On the other hand, for the purposes of unique indexes, SQL Server
also treats all NULLs as equal so you cannot have more than one.
It's better to not allow Null in a unique column as SQL Server allows only one null (I understand
this may be a problem when you insert rows and left that column null/blank). If you need to
perform like that, I recommend you use some check mechanism such as triggers to check
rows inserted into the table first to avoid the duplicated nulls errors.
Amy, does this answer your question? If there is anything more I can do to assist you, please
feel free to post it in the group
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you Billy Yao !
Yes, now I'm using a trigger to check the data during
insert.
-amy
>--Original Message--
>Hi Amy,
>Thank you for using MSDN Newsgroup! My name is Billy and
it's my pleasure to assist you
>with your issue.
>From your description, I understand that you would like
to allow multiple nulls in a UNIQUE
>column. Have I fully understood you? If there is anything
I misunderstood, please feel free to
>let me know.
>Unfortunately, this feature is not allowed in relational
databases that adhere to the SQL
>standard. As we know, Null, by definition is an "unknown"
value and is not equal to any other
>value even the null itself.
>However, UNIQUE constraint regards Null as other normal
values. In this way, a unique
>column only allow one Null. On the other hand, for the
purposes of unique indexes, SQL Server
>also treats all NULLs as equal so you cannot have more
than one.
>It's better to not allow Null in a unique column as SQL
Server allows only one null (I understand
>this may be a problem when you insert rows and left that
column null/blank). If you need to
>perform like that, I recommend you use some check
mechanism such as triggers to check
>rows inserted into the table first to avoid the
duplicated nulls errors.
>Amy, does this answer your question? If there is anything
more I can do to assist you, please
>feel free to post it in the group
>Best regards,
>Billy Yao
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>
>.
>

NULL vs. "Unknown Value"

I'm running into a situation that has me adding a value of "Unknown" to
a reference table. I am being pulled between two trains of thought, and
was curious to get other's input on in. I give an example below.

1-) Adding "Unknown" to a reference table is bad. Doing so effectively
changes the Nullability option of every FK that references the table to
a NULLable FK relation.

2-) Simply adding a "Not Known/Undetermined" value to the reference
table greatly simplifies things. No schema changes are required, and
programs that use the reference table to populate their drop-downs will
automatically see the new value.

Perhaps both approaches are good, but it would all depend of the
context, the criticality of other FKs that reference the table, how/when
the data is being used?

==============================================
EXAMPLE
==============================================

Assume two tables. Employee & EyeColor, as described below.

+===================================+
|Employee |
+------+------+
|EmployeeId(PK) | EyeColorId (FK) |
|NOT NULL | NOT NULL |
+------+------+
|marc | 1 |
|dan | 2 |
|sonya | 1 |
+================+==================+

+================================================= ===+
|EyeColor |
+-----+------+------+
|EyeColorId(PK) | EnglishName(AK1) | FrenchName(AK2) |
|NOT NULL | NOT NULL | NOT NULL |
+-----+------+------+
| 1 | Brown | Brun |
| 2 | Bloodshot | Rouge |
| 3 | Blue | Bleue |
+===============+==================+============== ===+

And let's say that an automated process is being built to import Eye
Colors from central database. In this process EyeColor may no longer be
available.

With solution #1, new (or existing) data is changed as follows:
+------+------+
|EmployeeId(PK) | EyeColorId (FK) |
|NOT NULL | NULL |
+------+------+
|marc | 1 |
|dan | 2 |
|sonya | 1 |
|newemp | NULL |
+================+==================+

+================================================= ===+
|EyeColor |
+-----+------+------+
|EyeColorId(PK) | EnglishName(AK1) | FrenchName(AK2) |
|NOT NULL | NOT NULL | NOT NULL |
+-----+------+------+
| 1 | Brown | Brun |
| 2 | Bloodshot | Rouge |
| 3 | Blue | Bleue |
+===============+==================+============== ===+

With solution #2, new (or existing) data is changed as follows:
+------+------+
|EmployeeId(PK) | EyeColorId (FK) |
|NOT NULL | NULL |
+------+------+
|marc | 1 |
|dan | 2 |
|sonya | 1 |
|newemp | 0 |
+================+==================+

+================================================= ===+
|EyeColor |
+-----+------+------+
|EyeColorId(PK) | EnglishName(AK1) | FrenchName(AK2) |
|NOT NULL | NOT NULL | NOT NULL |
+-----+------+------+
| 0 | Unknown | Inconnu |
| 1 | Brown | Brun |
| 2 | Bloodshot | Rouge |
| 3 | Blue | Bleue |
+===============+==================+============== ===+

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Marcus wrote:
> I'm running into a situation that has me adding a value of "Unknown" to
> a reference table. I am being pulled between two trains of thought, and
> was curious to get other's input on in. I give an example below.
> 1-) Adding "Unknown" to a reference table is bad. Doing so effectively
> changes the Nullability option of every FK that references the table to
> a NULLable FK relation.
You could fix this with check constraints. A bigger issue is that null
is intended to mean unknown (although it gets used for absent, as well).
By using a real value for unknown, three-valued logic will only come up
when it's inappropriate (as I would say absent really is a value, and
should be treated as such in comparisons).

If the distinction between absent and unknown is important for your
application, I would leave the unknowns as null, and put a row into
the reference table for absent. This is cleanest with a identity
keys, because checking for a magic value that means absent will be
inevitable, and either 0 or -1 is a fairly obvious choice that can
be the same for all tables.

(To head off a repeat discussion, we'll acknowledge that some people --
Joe Celko foremost among them -- consider identity keys evil, as they
are sort-of reminiscent of file pointers. I consider meaningful keys
to be evil, as they violate the spirit of the 1NF and 2NF rules.
I don't think anyone is likely to be convinced here.)

Bill|||William Cleveland wrote:

> Marcus wrote:
>> I'm running into a situation that has me adding a value of "Unknown" to
>> a reference table. I am being pulled between two trains of thought, and
>> was curious to get other's input on in. I give an example below.
>>
>> 1-) Adding "Unknown" to a reference table is bad. Doing so effectively
>> changes the Nullability option of every FK that references the table to
>> a NULLable FK relation.
>>
> You could fix this with check constraints. A bigger issue is that null
> is intended to mean unknown (although it gets used for absent, as well).
> By using a real value for unknown, three-valued logic will only come up
> when it's inappropriate (as I would say absent really is a value, and
> should be treated as such in comparisons).
> If the distinction between absent and unknown is important for your
> application, I would leave the unknowns as null, and put a row into
> the reference table for absent. This is cleanest with a identity
> keys, because checking for a magic value that means absent will be
> inevitable, and either 0 or -1 is a fairly obvious choice that can
> be the same for all tables.
> (To head off a repeat discussion, we'll acknowledge that some people --
> Joe Celko foremost among them -- consider identity keys evil, as they
> are sort-of reminiscent of file pointers. I consider meaningful keys
> to be evil, as they violate the spirit of the 1NF and 2NF rules.
> I don't think anyone is likely to be convinced here.)
> Bill

One thing to consider is that when using joins, imagine a database with
two tables, Patients and Blood, if blood type is unknown and you use a
query to match up patients with bags of blood by blood type then the
results can be deadly. Extreme example I know but food for thought.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer|||>> I'm running into a situation that has me adding a value of
"Unknown" to a reference table. <<

Did you mean "Referenced" table?

>> 1-) Adding "Unknown" to a reference table is bad. <<

No it is not. Get a copy of DATA & DATABASES and read the chapters on
missing values and designing encoding schemes.

A good encoding scheme can have several kinds of missing values.
Example; ICD disease codes use 000.000 for "undiagnosed, just
admitted" and 999.999 for "admited, diagnosed and we don't know what
this crud is". Likewise a good survey form DB has {'yes', 'no', 'not
answered', 'N/A'}, the ISO sex codes are {0 =unknown, 1 =male, 2
=female, 9 =lawful person such as corporations, etc. }

Even Dr. Codd had two kinds of NULLs in the second version of the
Relational model (missing value and missing attribute). SPARC listed
over 20 kinds of missing values in a 1975 paper.

>> Perhaps both approaches are good, but it would all depend of the
context, .. <<

Bingo! The NULL is portable and it has particular behavior. I use
NULLs in (start_time, finish_time) duration pairs as an "eternity"
symbol because I can say "COALESCE (finish_time, CURRENT_TIMESTAMP)"
to get a truly current report. But time is a continuum and encodings
are discrete sets of values.

I'd probably make 'Unknown' the DEFAULT to take advantage of the DRI
actions, too.

NULL Values vs empty string vs space

How do I define a field to have the default value = ''. Not NULL but not a space either in SQL Server 2005?

Use the following in the field (default value) property

('')

Regards

|||I tried that and it didn't work.|||Just to get your point, did you mean that you want to place a default empty string?|||Smiling is correct, that is how you do it. When you say "it didn't work", perhaps you mean it didn't do what you wanted/expected it to, but that is how you define a default of a zero-length string (from within management studio).|||

That's what I thought too. I tried it again and it does work. I think what was going on is I had the table definition open and saved it but it really did not save. So I closed the defintion window and tried it and it works.

Thanks

|||Whats even more interesting it does NOT work when using the FormView control in ASP.NET 2.0 but if I enter the data directly into the table using the server explorer it does work.

???|||Does it show NULL value though?|||

YES, when viewing the data in the database server explorer the value is NULL even though the default value is ''.

Like I said entering data directly in the server explorer it works, when using a FormView control it does not. When using the formview all code is generated by VisualStudio.

Here is the SQLDatasource code generated by Visual Studio...

<asp:SqlDataSource ID="SqlDataSourceUserProfile" runat="server" ConnectionString="<%$ ConnectionStrings:AEISITConnectionString %>"
DeleteCommand="DELETE FROM [UserProfile] WHERE [UserProfile_RecID] = @.UserProfile_RecID"
InsertCommand="INSERT INTO [UserProfile] ([UserID], [UserRole], [FirstName], [LastName],, [CampusID], [DistrictID], [RegionID], [SessionID]) VALUES (@.UserID, @.UserRole, @.FirstName, @.LastName, @.Email, @.CampusID, @.DistrictID, @.RegionID, @.SessionID)"
SelectCommand="SELECT UserProfile_RecID, UserID, UserRole, FirstName, LastName, Email, CampusID, DistrictID, RegionID, SessionID FROM UserProfile WHERE (UserProfile_RecID = @.UserProfile_RecID)"
UpdateCommand="UPDATE [UserProfile] SET [UserID] = @.UserID, [UserRole] = @.UserRole, [FirstName] = @.FirstName, [LastName] = @.LastName, = @.Email, [CampusID] = @.CampusID, [DistrictID] = @.DistrictID, [RegionID] = @.RegionID, [SessionID] = @.SessionID WHERE [UserProfile_RecID] = @.UserProfile_RecID">
<DeleteParameters>
<asp:Parameter Name="UserProfile_RecID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="UserID" Type="String" />
<asp:Parameter Name="UserRole" Type="String" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="CampusID" Type="String" />
<asp:Parameter Name="DistrictID" Type="String" />
<asp:Parameter Name="RegionID" Type="String" />
<asp:Parameter Name="SessionID" Type="String" />
<asp:Parameter Name="UserProfile_RecID" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="UserListBox" Name="UserProfile_RecID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="UserID" Type="String" />
<asp:Parameter Name="UserRole" Type="String" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="CampusID" Type="String" />
<asp:Parameter Name="DistrictID" Type="String" />
<asp:Parameter Name="RegionID" Type="String" />
<asp:Parameter Name="SessionID" Type="String" />
</InsertParameters>
</asp:SqlDataSource>

|||

That would explain it.

If you want the default value on insert, remove all references to the field in the insertcommand. Alternatively, change the parameter's convertemptystringtonull property to false.

|||

Yes, whatMotley said is correct.

Just change the convertemptystringtonull property to false.

null values return

Hi,
How do I join 2 tables with null value return?
Table1
Month, Year, Hours
Table2
Month, Year, Accounting_hrs
Here is my current statement.
Select Table1.month as MONTH, Table1.year as YEAR,
Table1.sumhrs/Table2.accounting_hrs AS AVERAGE
from Table2,
(
select month, year, sum (hours) as sumhrs
from Table1
where year = 2005
group by month, year
)
where
(
Table1.year = Table2.year
and
Table1.month = Table2.month
)
order by Table1.Month
RESULT:
MONTH YEAR AVERAGE
(Table1.hours/Table2.Accounting_hrs)
Jan 2005 5
Feb 2005 10
I would like the results to look like this:
MONTH YEAR AVERAGE
(Table1.hours/Table2.Accounting_hrs)
Jan 2005 5
Feb 2005 10
Mar 2005 NULL
Apr 2005 NULL
May 2005 NULL
etc.
TIA!!
Joyce
*** Sent via Developersdex http://www.examnotes.net ***Probably the easiest method to solve this problem is the build a calendar ta
ble
that contains a row for every day from now until some arbitrary point in the
future. That makes this problem trival to solve. For example you might have
(granted without the specific DDL of your solution, this may not be perfect)
Create Table Calendar
(
SpecificDate DateTime
, Month TinyInt
, Year SmallInt
, W TinyInt
, Quarter TinyInt
)
Select C.Month, C.Year
, Sum(T1.Hours) As SumHours
, Sum(T1.Hours) / Sum(T2.Accounting_Hrs) As SumHours
From Calendar As C
Left Join Table1 As T1
On C.Month = T1.Month
And C.Year = T1.Year
Left Join Table2 As T2
On C.Month = T2.Month
And C.Year = T2.Year
Group By C.Month, C.Year
If you know that for every value in Table2 there exists a value in Table1, t
hen
you can adjust the query slightly like so:
Select C.Month, C.Year
, Sum(T1.Hours) As SumHours
, Sum(T1.Hours / T2.Accounting_Hrs) As SumHours
From Calendar As C
Left Join (Table1 As T1
Join Table2 As T2
On T1.Month = T2.Month
And T1.Year = T2.Year)
On C.Month = T1.Month
And C.Year = T1.Year
Group By C.Month, C.Year
This solution of course presumes that Sum(Accounting_Hrs) will not be zero.
It
should also be noted that if either Sum(T1.Hours) or Sum(T2.Accounting_Hrs)
is
null that you will get Null for the result.
HTH,
Thomas|||Actually, as I think about you'll get bad results joining directly to the
calendar table. You would need to group your Calendar table first like so:
Select C.Month, C.Year
, Sum(T1.Hours) As TotalHours
, Sum(T1.Hours) / Sum(T2.Accounting_hrs) As AverageHours
From (
Select C1.Month, C1.Year
From Calendar As C1
Where C1.Year = 2005
Group By C1.Month, C1.Year
) As C
Left Join Table1 As T1
On C.Month = T1.Month
And C.Year = T1.Year
Left Join Table2 As T2
On C.Month = T2.Month
And C.Year = T2.Year
Thomas
"Joyce L" <jsh_57@.hotmail.com> wrote in message
news:OdVy8sbSFHA.2384@.tk2msftngp13.phx.gbl...
> Hi,
> How do I join 2 tables with null value return?
> Table1
> Month, Year, Hours
> Table2
> Month, Year, Accounting_hrs
> Here is my current statement.
> Select Table1.month as MONTH, Table1.year as YEAR,
> Table1.sumhrs/Table2.accounting_hrs AS AVERAGE
> from Table2,
> (
> select month, year, sum (hours) as sumhrs
> from Table1
> where year = 2005
> group by month, year
> )
> where
> (
> Table1.year = Table2.year
> and
> Table1.month = Table2.month
> )
> order by Table1.Month
> RESULT:
> MONTH YEAR AVERAGE
> (Table1.hours/Table2.Accounting_hrs)
> Jan 2005 5
> Feb 2005 10
>
> I would like the results to look like this:
> MONTH YEAR AVERAGE
> (Table1.hours/Table2.Accounting_hrs)
> Jan 2005 5
> Feb 2005 10
> Mar 2005 NULL
> Apr 2005 NULL
> May 2005 NULL
> .etc.
> TIA!!
> Joyce
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thank you very much Thomas!!!
*** Sent via Developersdex http://www.examnotes.net ***

NULL values in XML string

Can someone tell me how to format my XML string so when I load it into my
table the field actually has a NULL value and not an empty space?
My XML string is formated like this.
<reimbursement>
<tbl_Reimbursement>
<Type10Rec Provider_Type='INSTIT' Provider_SubType='11' ReimDesc=""
Status='PMA' CreateUsr='Test' LastModDate="" LastModUsr="" ApproveDate=""/>
</tbl_Reimbursement><
The problem is that I want the ones with nothing between the doublw quotes
to get entered into the table as NULL values not empty strings. I am having
difficulty doing this. Can someone help me? Thanks in advance.>> The problem is that I want the ones with nothing between the doublw
What is the datatype of the columns in the table? Do you have a default set
for these columns, if the values are missing?Why not post the DDLs here? Are
you using OPENXML to load the data or using some other 3rd party tools?
Anith|||CREATE TABLE [tbl_Reimbursement] (
[ReimID] [int] IDENTITY (1, 1) NOT NULL ,
[RecordType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_Reimbursement_RecordType] DEFAULT (10),
[ActionType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_tbl_Reimbursement_ActionType] DEFAULT ('A'),
[Provider_Type] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Provider_SubType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NetworkCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_tbl_Reimbursement_NetworkCode] DEFAULT (' '),
[EffectiveDate] [datetime] NOT NULL CONSTRAINT
[DF_tbl_Reimbursement_EffectiveDate] DEFAULT ('6/1/2004'),
[ExpirationDate] [datetime] NOT NULL CONSTRAINT
[DF_tbl_Reimbursement_ExpirationDate] DEFAULT ('12/31/2099'),
[ReimDesc] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SubmitDate] [datetime] NULL ,
[SubmitUsr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExportID] [int] NULL ,
[ExportDate] [datetime] NULL ,
[ApproveDate] [datetime] NULL ,
[ApproveUsr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreateDate] [datetime] NULL CONSTRAINT [DF_tbl_Reimbursement_CreateDate]
DEFAULT (getdate()),
[CreateUsr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExportSeq] [int] NULL CONSTRAINT [DF_tbl_Reimbursement_ExportSeq] DEFAULT
(1),
[LastModDate] [datetime] NULL ,
[LastModUsr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReimLocked] [bit] NULL ,
[ReimLockedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Reimbursement] PRIMARY KEY CLUSTERED
(
[ReimID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
) ON [PRIMARY]
GO
insert into tbl_Reimbursement (Provider_Type, Provider_SubType, ReimDesc,
Status, CreateUsr, LastModDate, LastModUsr, ApproveDate, ApproveUsr)
select * from OPENXML (@.idoc,'/reimbursement/tbl_Reimbursement/Type10Rec',1)
with (Provider_Type varchar(6),
Provider_SubType varchar(2),
ReimDesc varchar(70),
Status varchar(10),
CreateUsr varchar(50),
LastModDate datetime,
LastModUsr varchar(50),
ApproveDate datetime,
ApproveUsr varchar(50))
"Anith Sen" wrote:

> What is the datatype of the columns in the table? Do you have a default se
t
> for these columns, if the values are missing?Why not post the DDLs here? A
re
> you using OPENXML to load the data or using some other 3rd party tools?
> --
> Anith
>
>|||Your XML, as you posted in your initial post, is not well formed. It has no
closing tags for reimbursement node. The XML string has only eight values,
so it will insert values for only eight corresponding columns mentioned in
the INSERT statement. If the other columns are nullable, NULLs will enter
those columns. Here is the repro:
DECLARE @.x VARCHAR(500), @.idoc INT
SET @.x = '
<reimbursement>
<tbl_Reimbursement>
<Type10Rec Provider_Type="INSTIT" Provider_SubType="11" ReimDesc=""
Status="PMA" CreateUsr="Test" LastModDate="" LastModUsr="" ApproveDate=""/>
</tbl_Reimbursement>
</reimbursement>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.x
INSERT INTO tbl
( Provider_Type, Provider_SubType, ReimDesc, Status,
CreateUsr, LastModDate, LastModUsr, ApproveDate, ApproveUsr )
SELECT * -- use column names
FROM OPENXML ( @.idoc, '/reimbursement/tbl_Reimbursement/Type10Rec', 1 )
WITH ( Provider_Type VARCHAR( 6 ),
Provider_SubType VARCHAR( 2 ),
ReimDesc VARCHAR( 70 ),
Status VARCHAR( 10 ),
CreateUsr VARCHAR( 50 ),
LastModDate DATETIME,
LastModUsr VARCHAR( 50 ),
ApproveDate DATETIME,
ApproveUsr VARCHAR( 50 ) )
EXEC sp_xml_removedocument @.idoc
Anith

Null Values in SQL query

I have datatable field with values "ABC", " ", and NULL
When I say "select * from tbl_abc where myfield <>"ABC", the query returns
the value " ". However, the field with value = NULL is ignored.
Is there any way, we get the null value also in the query results?
Thanks.
select *
from tbl_abc
where myfield <> 'ABC'
OR myfield IS NULL
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
news:53082B03-92E6-42AF-8241-6B4B55DE613E@.microsoft.com...
> I have datatable field with values "ABC", " ", and NULL
> When I say "select * from tbl_abc where myfield <>"ABC", the query returns
> the value " ". However, the field with value = NULL is ignored.
> Is there any way, we get the null value also in the query results?
> Thanks.
|||myfield <> 'ABC' means myfield is not null. So, why should
myfield IS NULL to be specified explicitely?
"Adam Machanic" wrote:

> select *
> from tbl_abc
> where myfield <> 'ABC'
> OR myfield IS NULL
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
> news:53082B03-92E6-42AF-8241-6B4B55DE613E@.microsoft.com...
>
>
|||"rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
news:0B7BB9AC-B436-4DB6-AE20-4D05A4204ABB@.microsoft.com...
> myfield <> 'ABC' means myfield is not null.
No, it doesn't.
NULL is not equal to anything. Including NULL. In addition, NULL is not
not-equal to anything. Including NULL. The result of any comparison
involving NULL is unknown.
(NULL = <anything>) is unknown.
And (NULL <> <anything>) is also unknown.
And (NULL = NULL) is unknown too!
A predicate is only valid if it resolves to a true condition. (myfield <>
'ABC'), to resolve to true, must be true. If myfield is NULL, it resolves
to unknown, not true -- and therefore the row is not returned.
This is called "three-valued logic" and is quite confusing for a lot of
people just starting with SQL. It's one reason that I recommend that NULLs
should be used as little as possible. Instead of using NULLs, I advocate
the use of "missing value tokens" -- well defined tokens that you can put in
for missing values when you'd otherwise use a NULL. For instance, an empty
string, or the string 'Value Undefined'. These can be domain-specific; so
if you're dealing with postal codes, your unknown value token might be
'00000'; if you were dealing with an author biography, your token might be,
'Biography not on record'.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

Null Values in SQL query

I have datatable field with values "ABC", " ", and NULL
When I say "select * from tbl_abc where myfield <>"ABC", the query returns
the value " ". However, the field with value = NULL is ignored.
Is there any way, we get the null value also in the query results?
Thanks.select *
from tbl_abc
where myfield <> 'ABC'
OR myfield IS NULL
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
news:53082B03-92E6-42AF-8241-6B4B55DE613E@.microsoft.com...
> I have datatable field with values "ABC", " ", and NULL
> When I say "select * from tbl_abc where myfield <>"ABC", the query returns
> the value " ". However, the field with value = NULL is ignored.
> Is there any way, we get the null value also in the query results?
> Thanks.|||myfield <> 'ABC' means myfield is not null. So, why should
myfield IS NULL to be specified explicitely?
"Adam Machanic" wrote:
> select *
> from tbl_abc
> where myfield <> 'ABC'
> OR myfield IS NULL
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
> news:53082B03-92E6-42AF-8241-6B4B55DE613E@.microsoft.com...
> > I have datatable field with values "ABC", " ", and NULL
> >
> > When I say "select * from tbl_abc where myfield <>"ABC", the query returns
> > the value " ". However, the field with value = NULL is ignored.
> >
> > Is there any way, we get the null value also in the query results?
> >
> > Thanks.
>
>|||"rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
news:0B7BB9AC-B436-4DB6-AE20-4D05A4204ABB@.microsoft.com...
> myfield <> 'ABC' means myfield is not null.
No, it doesn't.
NULL is not equal to anything. Including NULL. In addition, NULL is not
not-equal to anything. Including NULL. The result of any comparison
involving NULL is unknown.
(NULL = <anything>) is unknown.
And (NULL <> <anything>) is also unknown.
And (NULL = NULL) is unknown too!
A predicate is only valid if it resolves to a true condition. (myfield <>
'ABC'), to resolve to true, must be true. If myfield is NULL, it resolves
to unknown, not true -- and therefore the row is not returned.
This is called "three-valued logic" and is quite confusing for a lot of
people just starting with SQL. It's one reason that I recommend that NULLs
should be used as little as possible. Instead of using NULLs, I advocate
the use of "missing value tokens" -- well defined tokens that you can put in
for missing values when you'd otherwise use a NULL. For instance, an empty
string, or the string 'Value Undefined'. These can be domain-specific; so
if you're dealing with postal codes, your unknown value token might be
'00000'; if you were dealing with an author biography, your token might be,
'Biography not on record'.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--

Null Values in SQL query

I have datatable field with values "ABC", " ", and NULL
When I say "select * from tbl_abc where myfield <>"ABC", the query returns
the value " ". However, the field with value = NULL is ignored.
Is there any way, we get the null value also in the query results?
Thanks.select *
from tbl_abc
where myfield <> 'ABC'
OR myfield IS NULL
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
news:53082B03-92E6-42AF-8241-6B4B55DE613E@.microsoft.com...
> I have datatable field with values "ABC", " ", and NULL
> When I say "select * from tbl_abc where myfield <>"ABC", the query returns
> the value " ". However, the field with value = NULL is ignored.
> Is there any way, we get the null value also in the query results?
> Thanks.|||myfield <> 'ABC' means myfield is not null. So, why should
myfield IS NULL to be specified explicitely?
"Adam Machanic" wrote:

> select *
> from tbl_abc
> where myfield <> 'ABC'
> OR myfield IS NULL
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
> news:53082B03-92E6-42AF-8241-6B4B55DE613E@.microsoft.com...
>
>|||"rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
news:0B7BB9AC-B436-4DB6-AE20-4D05A4204ABB@.microsoft.com...
> myfield <> 'ABC' means myfield is not null.
No, it doesn't.
NULL is not equal to anything. Including NULL. In addition, NULL is not
not-equal to anything. Including NULL. The result of any comparison
involving NULL is unknown.
(NULL = <anything> ) is unknown.
And (NULL <> <anything> ) is also unknown.
And (NULL = NULL) is unknown too!
A predicate is only valid if it resolves to a true condition. (myfield <>
'ABC'), to resolve to true, must be true. If myfield is NULL, it resolves
to unknown, not true -- and therefore the row is not returned.
This is called "three-valued logic" and is quite confusing for a lot of
people just starting with SQL. It's one reason that I recommend that NULLs
should be used as little as possible. Instead of using NULLs, I advocate
the use of "missing value tokens" -- well defined tokens that you can put in
for missing values when you'd otherwise use a NULL. For instance, an empty
string, or the string 'Value Undefined'. These can be domain-specific; so
if you're dealing with postal codes, your unknown value token might be
'00000'; if you were dealing with an author biography, your token might be,
'Biography not on record'.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--