NULL in dimensions: February 2012

Saturday, February 25, 2012

NULL Values

I imported an excel spreadsheet and it loaded a ton of records with NULL
values. When I try to delete them, it tells me To many rows will be
affected.
what is a SQL statement that I can type that will delete all the records
that has NULL in a particular field?DELETE FROM YourTable
WHERE YourColumn IS NULL
... and quit using EM to edit your data... it's not designed for that and
will definitely cause you problems in the long run (like this one)
"johnfli" <john@.here.com> wrote in message
news:OuGGCt7qEHA.3712@.TK2MSFTNGP15.phx.gbl...
> I imported an excel spreadsheet and it loaded a ton of records with NULL
> values. When I try to delete them, it tells me To many rows will be
> affected.
> what is a SQL statement that I can type that will delete all the records
> that has NULL in a particular field?
>
>|||what would you propose instead?
Thank you for your assistance.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OR333v7qEHA.3700@.TK2MSFTNGP15.phx.gbl...
> DELETE FROM YourTable
> WHERE YourColumn IS NULL
> ... and quit using EM to edit your data... it's not designed for that and
> will definitely cause you problems in the long run (like this one)
>
> "johnfli" <john@.here.com> wrote in message
> news:OuGGCt7qEHA.3712@.TK2MSFTNGP15.phx.gbl...
> > I imported an excel spreadsheet and it loaded a ton of records with NULL
> > values. When I try to delete them, it tells me To many rows will be
> > affected.
> > what is a SQL statement that I can type that will delete all the records
> > that has NULL in a particular field?
> >
> >
> >
>|||"johnfli" <john@.here.com> wrote in message
news:OgX4Ly7qEHA.3868@.TK2MSFTNGP15.phx.gbl...
> what would you propose instead?
> Thank you for your assistance.
Script your data modifications and run them from QA.|||I don't use sql that much, so I have no idea on how to write scripts to
import data from other files.
if you could assist me with that, that would be great.
I have a workbook with several sheets.
All sheets are the same format.
what would the script be? I have the table in sql all setup (there are
only 10 fields)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eVf3az7qEHA.556@.tk2msftngp13.phx.gbl...
> "johnfli" <john@.here.com> wrote in message
> news:OgX4Ly7qEHA.3868@.TK2MSFTNGP15.phx.gbl...
> > what would you propose instead?
> >
> > Thank you for your assistance.
> Script your data modifications and run them from QA.
>|||Try this article:
http://support.microsoft.com/?id=321686
"johnfli" <john@.here.com> wrote in message
news:uFZ04B8qEHA.348@.TK2MSFTNGP15.phx.gbl...
> I don't use sql that much, so I have no idea on how to write scripts to
> import data from other files.
> if you could assist me with that, that would be great.
> I have a workbook with several sheets.
> All sheets are the same format.
> what would the script be? I have the table in sql all setup (there are
> only 10 fields)
>

Null values

Hi,

How can I use "Derived Column" to check if a Datetime value is null or not and if null to insert 00/00/00 instead. ?

The background being that while using a "Derived Column" to change a Column from a (DT_DATE) to a (DT_DBTIMESTAMP) everytime I get a null value it see's it as a error.

And the column in particular has ~ 37 K blank / null fields so Im getting a lot of errors

So far I have tried to use something like

ISNULL([Column 34])

SELECT ISNULL(ID, '00/00/0000') FROM [Column 34]

SELECT ISNULL(au_id, '00/00/0000 00:00') AS ssn
FROM [Column 34

but none seems to work [Column 34] being the offending column.

What a normally use is just a simple "(DT_DBTIMESTAMP)[Column 34]"
in the expression column, which seems to work well, but here I get alot of errors

Any ideas?

Try

ISNULL([Column 34]) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)[Column 34]

Or something like that.

... ? ... : ... is the if then else operator! C++ stylee!!!

-Jamie

|||

You would use an expression like:

ISNULL([Column 34]) ? (DT_DBTIMESTAMP)"00/00/00" : (DT_DBTIMESTAMP)[Column 34]

Except, that will fail due to a conversion error, since 00/00/00 is not a valid DT_DBTIMESTAMP. You will need to use a valid date instead, I think.

Null Values

I have run a report and then grouped values. However some of these values have a 'null' or empty value. How do i change this so that the group name is not blank.

I have tried the following but this does not help:

IF {Subset.SCHOOLCAT} = '' THEN 'other'

It simply blanks out all group names.

I am doing this report on a HEAT System and i know that the reason for this null field is that we have two types of customers and one customer type does not have this field in its tables so it returns the value as blank.

I just need to replace this empty value with a string for consistency value but cannot seem to do it.

Regards

PIF {Subset.SCHOOLCAT} = '' THEN 'other'

Instead of the above code, try the following code

IF IsNull({Subset.SCHOOLCAT}) THEN 'other'|||Many thanks - that seemed to work.

Cheers

T

Null values

How can I replace null value of a column in a query
with something like 'TEST'
for eg. if I run the query
select contract_num, prodid from product
if the contract_num is null, it should return 'TEST'
instead of NULL. how can I do so?
Thank you,
-Loretta
On Tue, 17 Aug 2004 15:24:44 -0700, Loretta wrote:

>How can I replace null value of a column in a query
>with something like 'TEST'
>for eg. if I run the query
>select contract_num, prodid from product
>if the contract_num is null, it should return 'TEST'
>instead of NULL. how can I do so?
>Thank you,
>-Loretta
Hi Loretta,
SELECT COALESCE(contract_num, 'TEST')
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
This helps, thanks much!
-Loretta

>--Original Message--
>On Tue, 17 Aug 2004 15:24:44 -0700, Loretta wrote:
>
>Hi Loretta,
>SELECT COALESCE(contract_num, 'TEST')
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>

NULL values

I have a text box on a web form in visual studio.net that a user enters a
number in.
If they blank out the text box then I want to put a null value back into the
database field.
I want to return the value to NULL like txtWaterDischargeRate =
System.DBNull.Value instead of Nothing but I get an error
Can't convert NULL to integer
How can I enter a null in the database field on MS SQL Server?
Thanks
Hi
I am not sure what you are trying to do and I'm not a webform expert, but
you should be able to set may want to try something like checking the field's
data length in the application and passing DBNULL.value to the stored
procedure.
Alternatively you may be able to use a special value (say -1) and use
NULLIF(@.parameter,-1) in your insert statement.
John
"Craig" wrote:

> I have a text box on a web form in visual studio.net that a user enters a
> number in.
> If they blank out the text box then I want to put a null value back into the
> database field.
> I want to return the value to NULL like txtWaterDischargeRate =
> System.DBNull.Value instead of Nothing but I get an error
> Can't convert NULL to integer
> How can I enter a null in the database field on MS SQL Server?
> Thanks
>
>
>
|||Sample code.....
With DataSetLocationsForm1.tblLocations(0)
.WaterDischargeCost = txtWaterDischargeCost.Text
.MoreFields = txtOtherTextboxes.Text
End With
SqlDataAdapter1.Update(DataSetLocationsForm1)
The WaterDischargeCost field in the database is an type real. If they blank
out the text box and click the SAVE button on an existing record I get an
error "Can't convert NULL to integer"
I want to put a null value back into the database field
How do I do this?
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9FE6B6DA-00CE-448A-A740-2E950BC1AC57@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not sure what you are trying to do and I'm not a webform expert, but
> you should be able to set may want to try something like checking the
> field's
> data length in the application and passing DBNULL.value to the stored
> procedure.
> Alternatively you may be able to use a special value (say -1) and use
> NULLIF(@.parameter,-1) in your insert statement.
> John
> "Craig" wrote:
|||Hi Craig,
Thanks for your post.
From your descriptions, I understood you would like to know how to how to
handle NULL with SQL Server in VS.NET. If I have misunderstood your
concern, please feel free to point it out.
To better answer this question, I would recommand you raise the question in
ADO.NET or VS.NET managed newsgroup.
microsoft.public.dotnet.framework.adonet
microsoft.public.dotnet.framework
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi
This is almost certainly because you are passing as string "NULL" and not
NULL itself.
Try something like (although the syntax may not be correct!)
IF txtWaterDischargeCost.length > 0
..WaterDischargeCost = txtWaterDischargeCost.Text
ELSE
..WaterDischargeCost = System.DBNull
You can use SQL profiler to see the SQL statement sent to the database and
verify what the command actually is.
John
"Craig" wrote:

> Sample code.....
> With DataSetLocationsForm1.tblLocations(0)
> .WaterDischargeCost = txtWaterDischargeCost.Text
> .MoreFields = txtOtherTextboxes.Text
> End With
> SqlDataAdapter1.Update(DataSetLocationsForm1)
> The WaterDischargeCost field in the database is an type real. If they blank
> out the text box and click the SAVE button on an existing record I get an
> error "Can't convert NULL to integer"
> I want to put a null value back into the database field
> How do I do this?
> Thanks
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9FE6B6DA-00CE-448A-A740-2E950BC1AC57@.microsoft.com...
>
>
|||If the column WaterDischargeCost is nullable (minoccurs = 0), then you can
use:
DataSetLocationsForm1.tblLocations(0).SetWaterDisc hargeCostNull()
to let the dataset set the value as null.
This works!!!!
"Craig" <NoSpam@.hotmail.com> wrote in message
news:O6UVrbCqFHA.3160@.TK2MSFTNGP14.phx.gbl...
> Sample code.....
> With DataSetLocationsForm1.tblLocations(0)
> .WaterDischargeCost = txtWaterDischargeCost.Text
> .MoreFields = txtOtherTextboxes.Text
> End With
> SqlDataAdapter1.Update(DataSetLocationsForm1)
> The WaterDischargeCost field in the database is an type real. If they
> blank out the text box and click the SAVE button on an existing record I
> get an error "Can't convert NULL to integer"
> I want to put a null value back into the database field
> How do I do this?
> Thanks
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9FE6B6DA-00CE-448A-A740-2E950BC1AC57@.microsoft.com...
>

NULL Values

I imported an excel spreadsheet and it loaded a ton of records with NULL
values. When I try to delete them, it tells me To many rows will be
affected.
what is a SQL statement that I can type that will delete all the records
that has NULL in a particular field?
DELETE FROM YourTable
WHERE YourColumn IS NULL
... and quit using EM to edit your data... it's not designed for that and
will definitely cause you problems in the long run (like this one)
"johnfli" <john@.here.com> wrote in message
news:OuGGCt7qEHA.3712@.TK2MSFTNGP15.phx.gbl...
> I imported an excel spreadsheet and it loaded a ton of records with NULL
> values. When I try to delete them, it tells me To many rows will be
> affected.
> what is a SQL statement that I can type that will delete all the records
> that has NULL in a particular field?
>
>
|||what would you propose instead?
Thank you for your assistance.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OR333v7qEHA.3700@.TK2MSFTNGP15.phx.gbl...
> DELETE FROM YourTable
> WHERE YourColumn IS NULL
> ... and quit using EM to edit your data... it's not designed for that and
> will definitely cause you problems in the long run (like this one)
>
> "johnfli" <john@.here.com> wrote in message
> news:OuGGCt7qEHA.3712@.TK2MSFTNGP15.phx.gbl...
>
|||"johnfli" <john@.here.com> wrote in message
news:OgX4Ly7qEHA.3868@.TK2MSFTNGP15.phx.gbl...
> what would you propose instead?
> Thank you for your assistance.
Script your data modifications and run them from QA.
|||I don't use sql that much, so I have no idea on how to write scripts to
import data from other files.
if you could assist me with that, that would be great.
I have a workbook with several sheets.
All sheets are the same format.
what would the script be? I have the table in sql all setup (there are
only 10 fields)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eVf3az7qEHA.556@.tk2msftngp13.phx.gbl...
> "johnfli" <john@.here.com> wrote in message
> news:OgX4Ly7qEHA.3868@.TK2MSFTNGP15.phx.gbl...
> Script your data modifications and run them from QA.
>
|||Try this article:
http://support.microsoft.com/?id=321686
"johnfli" <john@.here.com> wrote in message
news:uFZ04B8qEHA.348@.TK2MSFTNGP15.phx.gbl...
> I don't use sql that much, so I have no idea on how to write scripts to
> import data from other files.
> if you could assist me with that, that would be great.
> I have a workbook with several sheets.
> All sheets are the same format.
> what would the script be? I have the table in sql all setup (there are
> only 10 fields)
>

Null value problem

Hi
I sent the following query
'Update TableA SET F1 = F1 + F2'
and where the value of F2 was Null then the value of F1 became Null even
where the original value of F1 was NOT Null
Is that the way it suppose to work?
Thank you,
Samuel
Samuel Shulman wrote:

>Hi
>I sent the following query
>'Update TableA SET F1 = F1 + F2'
> and where the value of F2 was Null then the value of F1 became Null even
>where the original value of F1 was NOT Null
>Is that the way it suppose to work?
>
>
Yes.
If you want to leave the value of F1 unchanged (assuming
F1 and F2 are numeric types), try
UPDATE TableA SET F1 = F1 + COALESCE(F2,0)
or
UPDATE TableA SET F1 = F1 + F2
WHERE F2 IS NOT NULL
Steve Kass
Drew University

>Thank you,
>Samuel
>
>|||Yes NULL + any other value = NULL (depending on CONCAT_NULL_YIELDS_NULL
setting of course)
select NULL + 'ABC' -- this will return NULL
do this instead for strings
Update TableA SET F1 = coalesce(F1,'') + coalesce(F2,'') or
for numeric values
Update TableA SET F1 = coalesce(F1,0') + coalesce(F2,0)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Samuel Shulman wrote:
> Hi
> I sent the following query
> 'Update TableA SET F1 = F1 + F2'
> and where the value of F2 was Null then the value of F1 became Null even
> where the original value of F1 was NOT Null
> Is that the way it suppose to work?
> Thank you,
> Samuel|||What data types are using? Are you trying to concatenate or ADD
Post a "real " example.
If you were trying to add, then NULL + 1 , will result in NULL , you could
get around the problem by using the ISNULL function.
Therefore you could do something like 'Update TableA SET F1 = ISNULL(F1,0) +
ISNULL(F2,0)'
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OaAFwvKiGHA.4080@.TK2MSFTNGP03.phx.gbl...
> Hi
> I sent the following query
> 'Update TableA SET F1 = F1 + F2'
> and where the value of F2 was Null then the value of F1 became Null even
> where the original value of F1 was NOT Null
> Is that the way it suppose to work?
> Thank you,
> Samuel
>|||Samuel Shulman (samuel.shulman@.ntlworld.com) writes:
> I sent the following query
> 'Update TableA SET F1 = F1 + F2'
> and where the value of F2 was Null then the value of F1 became Null even
> where the original value of F1 was NOT Null
> Is that the way it suppose to work?
Yes. NULL is an unknown value, and it doesn't become less unkonown because
you add it with a known value.
If you know that in the specific context that you are working in that a
NULL is the same thing as 0, you can say
UPDATE TableA SET F1 = F1 + coalesce(F2, 0)
The function coalesce() takes a list of expressions as argument, and returns
the the first non-NULL value in the list, or NULL if all are NULL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes coalesce() is the way to go.
Best Regards
Vadivel
http://vadivel.blogspot.com
"Samuel Shulman" wrote:

> Hi
> I sent the following query
> 'Update TableA SET F1 = F1 + F2'
> and where the value of F2 was Null then the value of F1 became Null even
> where the original value of F1 was NOT Null
> Is that the way it suppose to work?
> Thank you,
> Samuel
>
>|||Just in addition to what others have noted...
If NULL really means 0 or '', then make your column not null and define the
appropriate default.
NULLs have a behavior all their own, and you either have to prevent the NULL
values up front, or code for them after the fact.
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OaAFwvKiGHA.4080@.TK2MSFTNGP03.phx.gbl...
> Hi
> I sent the following query
> 'Update TableA SET F1 = F1 + F2'
> and where the value of F2 was Null then the value of F1 became Null even
> where the original value of F1 was NOT Null
> Is that the way it suppose to work?
> Thank you,
> Samuel
>

Null value issue

Hello everyone,

I try to get correct data in CR9, using simple SQl statment. But I am missing some records, I think it is because they may have 'null' value.
I have two fields:
math (value Y or NULL or N)
and
read (value Y or NULL or N),

I would like to see all records where math='Y' or read='Y'.

I am getting combination of Y Y or NULL Y or Y Y and I can not get combination of Y NULL. I tryed IsNull function and InStr, but seems it is doesn't change the result. What am I doing wrong?
Any help would be appreciated!
:wave:Are you limiting the data through sql or through record selection in Crystal Reports?|||Actually, I am suppose to do it through ASP pages, but doing so I am missing some records, so than I limited the data through the record selection in Crystal and I was getting the same result(..still missing records...) The same SELECT statement works fine in SQL...
:confused:|||Dear Raisa,

ur query shd not be a proble.pls chk whether the parameters are taken carefully:i selection formula orRegards,

Ashwin|||I think I got it! I use IsNull and InStr functions, checking for NULL and "" values.

Thanks for help!
:p

NULL Value in Aggregate Function

When I run a query, I get the following warning message
and I would like to know whether it is a problem or not ?
Warning: Null value is eliminated by an aggregate or other
SET operation.
ThanksWhether or not the warning message is a problem depends on the results you
expect. Consider the example below:
CREATE TABLE #Table1(Col1 int NULL)
INSERT INTO #Table1 VALUES(10)
INSERT INTO #Table1 VALUES(NULL)
SELECT AVG(Col1) FROM #Table1
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:02f401c529ca$0df65ff0$a401280a@.phx.gbl...
> When I run a query, I get the following warning message
> and I would like to know whether it is a problem or not ?
> Warning: Null value is eliminated by an aggregate or other
> SET operation.
> Thanks|||Hi,
Most aggregate functions eliminate null values in calculations; one
exception is the COUNT function. When using the COUNT function against a
column containing null values, the null values will be eliminated from the
calculation. However, if the COUNT function uses an asterisk, it will
calculate all rows regardless of null values being present.
Again depending upon the aggregate function you are using, you might have
received this warning message. You need to understand if the aggregate
function is giving the required result set. You can use ISNULL function to
convert NULL to the desired values.
--
Thanks
Yogish|||Thank you the reply from both of you.
In my select statement, aggregrate function SUM(Balance)
is used.
From the query result, I find that some of them are NULL
and some are $0. I am still looking into the reason why
some of them are NULL.
From my understanding, for NULL + $0, it will be $0. And
all balance are NULL will give me a NULL result. I
believe that the query still OK.
Thanks again.
>--Original Message--
>Hi,
>Most aggregate functions eliminate null values in
calculations; one
>exception is the COUNT function. When using the COUNT
function against a
>column containing null values, the null values will be
eliminated from the
>calculation. However, if the COUNT function uses an
asterisk, it will
>calculate all rows regardless of null values being
present.
>Again depending upon the aggregate function you are
using, you might have
>received this warning message. You need to understand if
the aggregate
>function is giving the required result set. You can use
ISNULL function to
>convert NULL to the desired values.
>--
>Thanks
>Yogish
>.
>|||> From my understanding, for NULL + $0,
No, NULL + 0 is UNKNOWN. For convenience, the SQL language designers decided to not return UNK or
NULL when you aggregate over rows where one or more have NULL in the column. They decided to skip
(ignore) the ones with NULL. And give you a reminder (this warning).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:033801c529d5$f9747920$a401280a@.phx.gbl...
> Thank you the reply from both of you.
> In my select statement, aggregrate function SUM(Balance)
> is used.
> From the query result, I find that some of them are NULL
> and some are $0. I am still looking into the reason why
> some of them are NULL.
> From my understanding, for NULL + $0, it will be $0. And
> all balance are NULL will give me a NULL result. I
> believe that the query still OK.
> Thanks again.
>>--Original Message--
>>Hi,
>>Most aggregate functions eliminate null values in
> calculations; one
>>exception is the COUNT function. When using the COUNT
> function against a
>>column containing null values, the null values will be
> eliminated from the
>>calculation. However, if the COUNT function uses an
> asterisk, it will
>>calculate all rows regardless of null values being
> present.
>>Again depending upon the aggregate function you are
> using, you might have
>>received this warning message. You need to understand if
> the aggregate
>>function is giving the required result set. You can use
> ISNULL function to
>>convert NULL to the desired values.
>>--
>>Thanks
>>Yogish
>>.

NULL Value in Aggregate Function

When I run a query, I get the following warning message
and I would like to know whether it is a problem or not ?
Warning: Null value is eliminated by an aggregate or other
SET operation.
ThanksWhether or not the warning message is a problem depends on the results you
expect. Consider the example below:
CREATE TABLE #Table1(Col1 int NULL)
INSERT INTO #Table1 VALUES(10)
INSERT INTO #Table1 VALUES(NULL)
SELECT AVG(Col1) FROM #Table1
Hope this helps.
Dan Guzman
SQL Server MVP
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:02f401c529ca$0df65ff0$a401280a@.phx.gbl...
> When I run a query, I get the following warning message
> and I would like to know whether it is a problem or not ?
> Warning: Null value is eliminated by an aggregate or other
> SET operation.
> Thanks|||Hi,
Most aggregate functions eliminate null values in calculations; one
exception is the COUNT function. When using the COUNT function against a
column containing null values, the null values will be eliminated from the
calculation. However, if the COUNT function uses an asterisk, it will
calculate all rows regardless of null values being present.
Again depending upon the aggregate function you are using, you might have
received this warning message. You need to understand if the aggregate
function is giving the required result set. You can use ISNULL function to
convert NULL to the desired values.
Thanks
Yogish|||Thank you the reply from both of you.
In my select statement, aggregrate function SUM(Balance)
is used.
From the query result, I find that some of them are NULL
and some are $0. I am still looking into the reason why
some of them are NULL.
From my understanding, for NULL + $0, it will be $0. And
all balance are NULL will give me a NULL result. I
believe that the query still OK.
Thanks again.

>--Original Message--
>Hi,
>Most aggregate functions eliminate null values in
calculations; one
>exception is the COUNT function. When using the COUNT
function against a
>column containing null values, the null values will be
eliminated from the
>calculation. However, if the COUNT function uses an
asterisk, it will
>calculate all rows regardless of null values being
present.
>Again depending upon the aggregate function you are
using, you might have
>received this warning message. You need to understand if
the aggregate
>function is giving the required result set. You can use
ISNULL function to
>convert NULL to the desired values.
>--
>Thanks
>Yogish
>.
>|||> From my understanding, for NULL + $0,
No, NULL + 0 is UNKNOWN. For convenience, the SQL language designers decided
to not return UNK or
NULL when you aggregate over rows where one or more have NULL in the column.
They decided to skip
(ignore) the ones with NULL. And give you a reminder (this warning).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:033801c529d5$f9747920$a401280a@.phx.gbl...[vbcol=seagreen]
> Thank you the reply from both of you.
> In my select statement, aggregrate function SUM(Balance)
> is used.
> From the query result, I find that some of them are NULL
> and some are $0. I am still looking into the reason why
> some of them are NULL.
> From my understanding, for NULL + $0, it will be $0. And
> all balance are NULL will give me a NULL result. I
> believe that the query still OK.
> Thanks again.
>
> calculations; one
> function against a
> eliminated from the
> asterisk, it will
> present.
> using, you might have
> the aggregate
> ISNULL function to

NULL Value in Aggregate Function

When I run a query, I get the following warning message
and I would like to know whether it is a problem or not ?
Warning: Null value is eliminated by an aggregate or other
SET operation.
Thanks
Whether or not the warning message is a problem depends on the results you
expect. Consider the example below:
CREATE TABLE #Table1(Col1 int NULL)
INSERT INTO #Table1 VALUES(10)
INSERT INTO #Table1 VALUES(NULL)
SELECT AVG(Col1) FROM #Table1
Hope this helps.
Dan Guzman
SQL Server MVP
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:02f401c529ca$0df65ff0$a401280a@.phx.gbl...
> When I run a query, I get the following warning message
> and I would like to know whether it is a problem or not ?
> Warning: Null value is eliminated by an aggregate or other
> SET operation.
> Thanks
|||Hi,
Most aggregate functions eliminate null values in calculations; one
exception is the COUNT function. When using the COUNT function against a
column containing null values, the null values will be eliminated from the
calculation. However, if the COUNT function uses an asterisk, it will
calculate all rows regardless of null values being present.
Again depending upon the aggregate function you are using, you might have
received this warning message. You need to understand if the aggregate
function is giving the required result set. You can use ISNULL function to
convert NULL to the desired values.
Thanks
Yogish
|||Thank you the reply from both of you.
In my select statement, aggregrate function SUM(Balance)
is used.
From the query result, I find that some of them are NULL
and some are $0. I am still looking into the reason why
some of them are NULL.
From my understanding, for NULL + $0, it will be $0. And
all balance are NULL will give me a NULL result. I
believe that the query still OK.
Thanks again.

>--Original Message--
>Hi,
>Most aggregate functions eliminate null values in
calculations; one
>exception is the COUNT function. When using the COUNT
function against a
>column containing null values, the null values will be
eliminated from the
>calculation. However, if the COUNT function uses an
asterisk, it will
>calculate all rows regardless of null values being
present.
>Again depending upon the aggregate function you are
using, you might have
>received this warning message. You need to understand if
the aggregate
>function is giving the required result set. You can use
ISNULL function to
>convert NULL to the desired values.
>--
>Thanks
>Yogish
>.
>
|||> From my understanding, for NULL + $0,
No, NULL + 0 is UNKNOWN. For convenience, the SQL language designers decided to not return UNK or
NULL when you aggregate over rows where one or more have NULL in the column. They decided to skip
(ignore) the ones with NULL. And give you a reminder (this warning).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:033801c529d5$f9747920$a401280a@.phx.gbl...[vbcol=seagreen]
> Thank you the reply from both of you.
> In my select statement, aggregrate function SUM(Balance)
> is used.
> From the query result, I find that some of them are NULL
> and some are $0. I am still looking into the reason why
> some of them are NULL.
> From my understanding, for NULL + $0, it will be $0. And
> all balance are NULL will give me a NULL result. I
> believe that the query still OK.
> Thanks again.
> calculations; one
> function against a
> eliminated from the
> asterisk, it will
> present.
> using, you might have
> the aggregate
> ISNULL function to

Null value handling in SSIS package

Hi All

I am facing this problem while loading data from text file into Table.

Scenario is -

There are chances of having spaces for null values in text file.

when i m trying to rum my SSIS package this is getting failed.

How can i avoid this problem? i want null values to be inserted if ther is spaces for that field in text file.

Thanks,

Anshu

Hi,

You can use a derived column transformation and check to see if your input column value has all spaces... trim and followed by len and if len == 0, set the column value to null.

Hope this helps...

NULL value gives unexpected query result

With the query:
SELECT * FROM T1
WHERE F1 LIKE N'%V1%'
AND NOT F2 LIKE N'%V2%'
and F1 is like V1:
If F2 is like V2, then EOF.
If F2 is not like V2, then the record is selected.
But if F2 is NULL then the result is EOF. If F2 is NULL, and is therefore
not like V2, I would have expected the record to be selected.
Please advise. If this is the correct result, is there another way to
construct the statement to give a record if V2 is NULL?Roger,
per default setting, any judgement other than "is (not) null" evaluates to
null when the column in question has the null value. If F2 is null, it is
neither like V2 nor not like V2. It is uncertain. Therefore it does not
satisfy your condition to return the record.
So your query should be:
SELECT * FROM T1
WHERE F1 LIKE N'%V1%'
AND (NOT F2 LIKE N'%V2%' or F2 is null)
Quentin
"Roger Withnell" <RogerWithnell@.discussions.microsoft.com> wrote in message
news:9677F81D-DBB0-4B50-9FC0-9F4BDDB94F0C@.microsoft.com...
> With the query:
> SELECT * FROM T1
> WHERE F1 LIKE N'%V1%'
> AND NOT F2 LIKE N'%V2%'
> and F1 is like V1:
> If F2 is like V2, then EOF.
> If F2 is not like V2, then the record is selected.
> But if F2 is NULL then the result is EOF. If F2 is NULL, and is therefore
> not like V2, I would have expected the record to be selected.
> Please advise. If this is the correct result, is there another way to
> construct the statement to give a record if V2 is NULL?|||Many thanks for this.
An alternative would be to make all NULL fields blank ie a zero length string.
Would you advise this or not?
"Quentin Ran" wrote:
> Roger,
> per default setting, any judgement other than "is (not) null" evaluates to
> null when the column in question has the null value. If F2 is null, it is
> neither like V2 nor not like V2. It is uncertain. Therefore it does not
> satisfy your condition to return the record.
> So your query should be:
> SELECT * FROM T1
> WHERE F1 LIKE N'%V1%'
> AND (NOT F2 LIKE N'%V2%' or F2 is null)
> Quentin
>
> "Roger Withnell" <RogerWithnell@.discussions.microsoft.com> wrote in message
> news:9677F81D-DBB0-4B50-9FC0-9F4BDDB94F0C@.microsoft.com...
> > With the query:
> > SELECT * FROM T1
> > WHERE F1 LIKE N'%V1%'
> > AND NOT F2 LIKE N'%V2%'
> >
> > and F1 is like V1:
> >
> > If F2 is like V2, then EOF.
> >
> > If F2 is not like V2, then the record is selected.
> >
> > But if F2 is NULL then the result is EOF. If F2 is NULL, and is therefore
> > not like V2, I would have expected the record to be selected.
> >
> > Please advise. If this is the correct result, is there another way to
> > construct the statement to give a record if V2 is NULL?
>
>|||Roger Withnell a Ã©crit :
> Many thanks for this.
> An alternative would be to make all NULL fields blank ie a zero length string.
> Would you advise this or not?
Absolutly not !!!!
An empty string does not say the same thing that nothing at all.
NULL is not a value. It is a symbolic marker wich say there is no value.
Always a NULL marker in an expression does the expression to be evaluate
to UNKNOWN value wich is neither true nor false.
Specials operators and function are available to make NULL marker
valuate, like COALESCE, CASE, IS NULL, IS NOT NULL, NULLIF...
You must use it.
What will be the result of this query :
SELECT AVG(AGE)
FROM employees
if you have marked all the AGE unknown values to 1900-01-01 ?
A +
> "Quentin Ran" wrote:
>> Roger,
>> per default setting, any judgement other than "is (not) null" evaluates to
>> null when the column in question has the null value. If F2 is null, it is
>> neither like V2 nor not like V2. It is uncertain. Therefore it does not
>> satisfy your condition to return the record.
>> So your query should be:
>> SELECT * FROM T1
>> WHERE F1 LIKE N'%V1%'
>> AND (NOT F2 LIKE N'%V2%' or F2 is null)
>> Quentin
>>
>> "Roger Withnell" <RogerWithnell@.discussions.microsoft.com> wrote in message
>> news:9677F81D-DBB0-4B50-9FC0-9F4BDDB94F0C@.microsoft.com...
>> With the query:
>> SELECT * FROM T1
>> WHERE F1 LIKE N'%V1%'
>> AND NOT F2 LIKE N'%V2%'
>> and F1 is like V1:
>> If F2 is like V2, then EOF.
>> If F2 is not like V2, then the record is selected.
>> But if F2 is NULL then the result is EOF. If F2 is NULL, and is therefore
>> not like V2, I would have expected the record to be selected.
>> Please advise. If this is the correct result, is there another way to
>> construct the statement to give a record if V2 is NULL?
>>
FrÃ©dÃ©ric BROUARD, MVP SQL Server, expert bases de donnÃ©es et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modÃ©lisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

NULL value gives unexpected query result

Null value for Web Service task input

I'm attempting to use the Web Service task to call a method provided by a vendor. The inputs are all simple types so that helps a lot, but for one input it's a string array. If I enter a single string value for a given id using the array dialog, the method works fine.

However if I try to change the input to use a variable to provide the value it fails. I'm assuming that you can't create a variable to return a string array. Please let me know if this is the case or if I'm missing something obvious ;-)

In addition, is there a way to specify a null value for an input? Currently there doesn't appear to be a way to do this in SP2. I tried leaving the string empty which didn't work and I tried using a variable with expression "NULL(DT_WSTR, 1252)" but that failed too.

You should be able to store an array of strings in a variable typed as Object. Not sure how well that will pass to the web service though.|||

Thanks for your reply John. As you suggested, I changed the variable type to object and assigned a string array to it. I was skeptical that it would work, but when I used that in the Web Service it worked! So many thanks to you!

Also I discovered a way to pass null values for parameters which is a bit unorthodox. You check the variable option for the input, but you don't select a variable and then that parameter is excluded from the request.

However there does appear to be one final obstacle which is that the timeout setting doesn't appear to work. Someone with a similar issue posted it at http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277689 and I have validated it.

Null Value

Hello,

I have SQL 2005 stored procedure to generate parameters in the report.

one of the stored procedure parameters need to be null (one of the @. in the SP)

How can I send null value to the stored procedure ?

Currently I create a new report parameters with default value NULL that it is internal (hide from the user) and I point the parameters in the SP need to be null to this parameters.

This look not a nice solution.

any other solution ?

Have you tried "=Nothing" as the value of the SP parameter?

null value

I have a char field which I check the "allow null" property and when I tried
to input an empty string that field, it always give me the following msg:
"You tried to assign Null value to a variable that isn't a Variant data
type"
But if I run it in an update query and I don't get the same message. I am
using SQL2000 and access 97 query. Please help. Thanks._eric clapton wrote:
> I have a char field which I check the "allow null" property and when
> I tried to input an empty string that field, it always give me the
> following msg:
> "You tried to assign Null value to a variable that isn't a Variant
> data type"
> But if I run it in an update query and I don't get the same message.
> I am using SQL2000 and access 97 query. Please help. Thanks.
You can't input a zero-length string from the GUI because Access automatically
strips trailing blanks. The append query doesn't do this which is why it works.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

null value

I have a char field which I check the "allow null" property and when I tried
to input an empty string that field, it always give me the following msg:
"You tried to assign Null value to a variable that isn't a Variant data
type"
But if I run it in an update query and I don't get the same message. I am
using SQL2000 and access 97 query. Please help. Thanks._eric clapton wrote:
> I have a char field which I check the "allow null" property and when
> I tried to input an empty string that field, it always give me the
> following msg:
> "You tried to assign Null value to a variable that isn't a Variant
> data type"
> But if I run it in an update query and I don't get the same message.
> I am using SQL2000 and access 97 query. Please help. Thanks.
You can't input a zero-length string from the GUI because Access automatical
ly
strips trailing blanks. The append query doesn't do this which is why it wo
rks.
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

null value

I have a char field which I check the "allow null" property and when I tried
to input an empty string that field, it always give me the following msg:
"You tried to assign Null value to a variable that isn't a Variant data
type"
But if I run it in an update query and I don't get the same message. I am
using SQL2000 and access 97 query. Please help. Thanks.
_eric clapton wrote:
> I have a char field which I check the "allow null" property and when
> I tried to input an empty string that field, it always give me the
> following msg:
> "You tried to assign Null value to a variable that isn't a Variant
> data type"
> But if I run it in an update query and I don't get the same message.
> I am using SQL2000 and access 97 query. Please help. Thanks.
You can't input a zero-length string from the GUI because Access automatically
strips trailing blanks. The append query doesn't do this which is why it works.
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Null value

I have a database for a credit society which gives loans in that there are 2 fields for a Member table , loan amount taken and guarantor alongwith the other fields like age,address etc .
A person may just be a member and may have not taken any loan yet , so i have kept loan amount as NULL , but when a person takes a loan he must have a guarantor . But if I dont keep the guarantor field as NULL it will cause problem when a person becomes fresh member and has not yet taken loan

Can I keep the guarantor field NULL? Pleez clear out my vague concept about NULLS and help me out

Quote:

Originally Posted by nairdeepthi2000

Can I keep the guarantor field NULL? Pleez clear out my vague concept about NULLS and help me out

Yes you can have 2 null columns.
But it is better not to for search purposes.
If you don't want to allow nulls you can put 0 in loan column and '' string in guarantor column as default. This way when new record is inserted and these two columns were not mentioned they will insert defaults instead.

But still you have to make sure in your trigger when record is inserted\updated and if loan number is > 0 then guarantor should be not null and > ''

Good Luck.|||Hi

Thank u so much, u were a great help

Quote:

Originally Posted by iburyak

But still you have to make sure in your trigger when record is inserted\updated and if loan number is > 0 then guarantor should be not null and > ''

Good Luck.

Null User Problem

Here is what we have going on...

we have 2 servers, the web server (A), and a SQL cluster (B). When a user visits a website on A there is some stored procedures that get called on B. We would like those stored procedures to be executed using the "network users" login. We try to implement this by doing the following:


the connection string looks like this
<add key="ConnectionString" value="Workstation ID=UTMPLMDBT;Packet Size=4096;Integrated Security=SSPI;Data Source=XXX.XXX.XXX.XXX,xxxx;Persist Security Info=False;Initial Catalog=XXXX" /
we added this in the web.config
<authentication mode="Windows" />
<identity impersonate="true"/>

-The web server is running IIS 6 on windows 2003 server
-We currently do not used Active Directory
-SQL cluster is running SQL 2000 on windows 2003 server

The error we are currently getting is:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

This error occurs when we try sqlConn.Open()

Any help would be greatly appreciated,
TreyHere is some other information...I wrote the following program...


 Dim authUserName As String
 Dim aspUserName As String
 authUserName = User.Identity.Name
 aspUserName = Principal.WindowsIdentity.GetCurrent.Name
 authUserPrincipalLabel.Text = "You are: " & authUserName
 aspPrincipalLabel.Text = "This page runs as: " & aspUserName

it displays:

You are:MYDomain\144205
This page runs as: MYDomain\144205

So I'm not sure what is going wrong|||This:

::We would like those stored procedures to be executed using the "network users" login

and this:

::<authentication mode="Windows" />
::<identity impersonate="true"/
contradicts. The statement will get executed using the identity of the user making the call, IF the page is protected, null (imho - am I correct here?) if anonymous access is allowed (or: the asp.net worker process identity, which is NOT network_users and a local account, unless reconfigured, which you did not say you did).|||When I said,
::We would like those stored procedures to be executed using the "network users" login
I mean I want to log into SQL using the users network username (ex) Mydomain\Trey
and password and execute the the procedure.

anonymous access is not allowed (not checked) in or IIS server.

I'm gettng kinda lost...how do I do this?

Thanks,
Trey|||OK after much reading...I was wondering if I can try the following

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/secmod/html/secmod15.asp

without using active directory?

Thanks,
Trey|||::OK after much reading...

NOW you deserve a good answer :-) And this is not meant sarcastic. You proapbly learned a lot about how the WIndows Security System works, and this is sort of exactly what you need.

Could you describe the server side network setup?

Basically, what you can do is:

* One Server: create a custom asp.net account, give it rights in the database, do NOT use impersonation. Voila.
* Multiple Servers: no impersonation, no trusted connection, turn the server to "mixed mode", use username and password.

Unter 99% of the cases the real user should NOT map through to the database.|||Ok...what we did is created a minimum privileged domain account, "Me"

Added this to the web.config


<identity impersonate="true" userName="MyDomain\Me"password="Something" />

Just a note I am not going to leave the Username and Password in clear text.

And we connected successfully. Whoop!

So I tried something else... I changed the web.config file back to:


<identity impersonate="true" />

and logged in as the new "Me" account
I got the account null error again. I guess I'm getting lost on what is going on in the back end.

The reason I would like to use the real user is for auditing purposes. So we know who is doing every select, insert, ect. Would it be better just to pass the users info as a value in the stored procedure?

Thanks,
Trey

null user login failed

I'm new to Crystal Reports and ASP and I'm sure i'm missing something simple here.

I've got a SQL server database, A Crystal Report, a VB application and an ASP Application

In the VB app i call the crystal report and log on to the database with the intergrated security

However in the ASP app i try the same and it has me log on as a null user.

what have i forgotten to do that will let the intergrated security work between SQL Server and ASPyou need to use impersonation in IIS so that IIS uses the credentials of the user, rather than of the nt account the IIS service is running as.|||Thank you i knew it was something simple i was missing

Null to Zero in Select Statement?

How can I do a null to 0 in a select statement? I tried the NZ() function but it is not part of SQL.

Thanks very much,you can use CASE stmt ...chk BOL for for more info

hth|||Easier, ISNULL(). If the field that might be null is an int called MyField:

SELECT IsNull(MyField,0) FROM table|||or COALESCE is the other option. i'm not always sure when ISNULL or COALESCE is more appropriate

cs|||COALESCE allows as many arguments as you want, that is:

COALESCE(v1,v2,v3,v4,0)

Will return 0 if all of v1 through v4 are null, otherwise the first non-null argument...|||that's right, I remember now. COALESCE is good if you want to pass in multiple values and get a zero if all are null, but not useful if you want to substitute another value for the null. I had forgotten about the multiple argument thing.

NULL to Zero

Is there any function that would change a NULL value to zero? I am writing a
view that need to calculate the net inventory by subtracting the export qty
from import qty. However, in case of no export, 1-Null=Null. Thanks.[posted and mailed, please reply in news]

John Q (johnq@.hkayp.org) writes:
> Is there any function that would change a NULL value to zero? I am
> writing a view that need to calculate the net inventory by subtracting
> the export qty from import qty. However, in case of no export,
> 1-Null=Null. Thanks.

coalesce(val1, val2, ..., valn)

returns the first non-NULL value in the list.

(There is also a function isnull() which has a clearer name, but is
restricted to two parameters only. coalesce() is from the ANSI standard.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

Thank you very much. This function is very useful to me.

Best Regards,
Frederick

"Erland Sommarskog" <sommar@.algonet.se> bl
news:Xns93EC5BCD76E8Yazorman@.127.0.0.1 g...
> [posted and mailed, please reply in news]
> John Q (johnq@.hkayp.org) writes:
> > Is there any function that would change a NULL value to zero? I am
> > writing a view that need to calculate the net inventory by subtracting
> > the export qty from import qty. However, in case of no export,
> > 1-Null=Null. Thanks.
> coalesce(val1, val2, ..., valn)
> returns the first non-NULL value in the list.
> (There is also a function isnull() which has a clearer name, but is
> restricted to two parameters only. coalesce() is from the ANSI standard.)
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

NULL SQL 2000

Maybe I'm doing something weird... but here's the problem I'm having.

I made a small ASP.NET (C#) site on my local machine that connects to SQL 2000. When I test the application it runs great. Everything gets stored into the database and the tables that it is suppose to.

I upload the site to a hosting company and when I run it, I get all sorts of error with the database. (I also copied the database from my local machine to the server via Enterprise Manager.)

For example, when I run the registration page - I get the error message:

"Cannot insert the value NULL into column 'user_id', table 'xxx.xxxx.tbl_users'; column does not allow nulls. INSERT fails. The statement has been terminated. "

I checked the table properties and my columns are set to allow null values. With the exception of having a primary key set to a column for indentity purpose (user_id).

The weird part is that I checked to see if the data are being stored into the column... and it is! Everything that was sent from the form is being stored into the table.

So I don't understand the problem I am having. Can anyone shed some light for me?

Below is the function I've written that inserts the data into the table.

===========================
C# Function to insert data
===========================

// Store Data to String

string strFirst = reg_fname.Text.ToString().Trim();
string strLast = reg_lname.Text.ToString().Trim();
string strEmail = reg_email.Text.ToString().Trim();
string strZip = reg_zip.Text.ToString().Trim();
string strCountry = reg_country.Text.ToString().Trim();

// Open Connection to Database

string strConnection = ConfigurationSettings.AppSettings["xxxxx"];

string strSQL = "INSERT INTO tbl_registration (first_name, last_name, email, zip, country) VALUES ('"+ strFirst.Replace("'", "") +"', '"+ strLast.Replace("'", "") +"', '"+ strEmail.Replace("'", "") +"', '"+ strPassword.Replace("'", "") +"', '"+ strZip.Replace("'", "") +"', '"+ strCountry.Replace("'", "") +"')";

// Execute Connection and INSERT Data

SqlConnection oConnection = new SqlConnection(strConnection);
oConnection.Open();
SqlCommand oCommand = new SqlCommand(strSQL, oConnection )oCommand.ExecuteNonQuery();

oConnection.Close();How is the user_id column populated? You mentioned "identity purposes." Is the column marked as an IDENTITY in SQL?

Regarding your command, consider using parameterized commands:


string sqlStmt;
SqlCommand cmd;sqlStmt = "INSERT INTO (first_name, last_name, email, zip, country) VALUES (@.first_name, @.last_name, @.email, @.zip, @.country)";
cmd = new SqlCommand(sqlStmt, oConnection);
cmd.Parameters.Add("@.first_name", strFirst);
cmd.Parameters.Add("@.last_name", strLast);
cmd.Parameters.Add("@.email", strPassword);
cmd.Parameters.Add("@.zip", strZip);
cmd.Parameters.Add("@.country", strCountry);
cmd.ExecuteNonQuery();

Note that if any of the parameters contain any illegal characters, such as a single quote, the parameter will be "fixed", so the str.Replace("''", "'") is not required.|||Also, are there triggers involved? Sometimes, the error message is referring to something that your trigger was trying to do, and not the proc you were explicitly executing... :)

NULL s in database

I am considering not to allow null values in the database.

what are pros and cons ?first, set all field in sqlserver to 'not nullable'
then, in data layer, check whether data inserted are null or not, if any of data is null, refuse to submit data and return false to higher layer|||

erdsah88:
I am considering not to allow null values in the database.
what are pros and cons ?

You cannot do that in a web application because users will go to sites that will not force them to use the site. The only web application you can use more not null tables is HR application in your company's intranet. Hope this helps.

|||

erdsah88:
I am considering not to allow null values in the database.
what are pros and cons ?

This isn't an easy question, as I suspect you've already figured out. The basic question is whether your application needs to make a distinction between default values (zero for numbers, empty strings, etc.) and missing information.

You need to carefully consider whether you need to know if data ismissing. For example, without nulls, does a zero-length string in theMiddleName field for a person mean that you don't have the middle nameor that the person doesn't have a middle name. Do you care about whichit is?

If you don't need to know when information is missing versus non-existant, it is much easier to disallow nulls. This will eliminate much of the need to do conversions between nulls and .NET data types that don't allow nulls. If you don't have the person's income, just put zero in that field and be done with it.

For an interesting take on using nulls, see Adam Cogan'sRules to better SQL Server Databases, rules 100 and 101.

Hope this helps!
Don

NULL Returned for Populated Column

Here is a strange one:

The following partitioned table (partitioned for 31 days) with a single nonclustered, nonunique index is behaving improperly on a select. A standard select using the index returns all NULL values for altitude yet if I do a select for the TOP x rows ordered by reporttime desc for not null altitudes, I see the values stored in the column. There are over 5,000,000 rows in the table with a relatively even distribution over each partition (except partition 1 which is kept empty).

CREATE TABLE [dbo].[APRSTrack](
[CallsignSSID] [varchar](9) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[ReportTime] [datetime] NOT NULL,
[Latitude] [float] NOT NULL,
[Longitude] [float] NOT NULL,
[Icon] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Course] [smallint] NULL,
[Speed] [int] NULL,
[Altitude] [int] NULL
) ON [onemonth]([ReportTime])

CREATE NONCLUSTERED INDEX [IX_APRSTrack] ON [dbo].[APRSTrack]
(
[CallsignSSID] ASC,
[ReportTime] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90, ONLINE = OFF) ON [onemonth]([ReportTime])

The failing select statement is:

SELECT *
FROM [Ham Radio].[dbo].[APRSTrack]
where callsignssid = 'EA3ABN-9' and reporttime >= '2006-06-06'
order by reporttime

and the working select statement is:

select top 100 *
from APRSTrack
where (not (altitude is null)) and reporttime >= '2006-06-06'
order by reporttime desc

The execution plan for the first lookup is:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.2047.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="2.67004" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00992778" StatementText="SELECT *
 FROM [Ham Radio].[dbo].[APRSTrack]
where callsignssid = 'EA3ABN-9' and reporttime >= '2006-06-06'
order by reporttime" StatementType="SELECT">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan CachedPlanSize="60">
<RelOp AvgRowSize="54" EstimateCPU="1.11608E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.67004" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00992778">
<OutputList>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Latitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Longitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Icon" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Course" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Speed" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Altitude" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Column="PtnIds1007" />
<ColumnReference Column="Bmk1000" />
</OuterReferences>
<PartitionId>
<ColumnReference Column="PtnIds1007" />
</PartitionId>
<RelOp AvgRowSize="38" EstimateCPU="2.67004E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.67004" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0032852">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
<ColumnReference Column="PtnIds1007" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="PtnIds1007" />
<ScalarOperator ScalarString="RangePartitionNew([Ham Radio].[dbo].[APRSTrack].[ReportTime],(1),'2006-05-07 00:00:00.000','2006-05-08 00:00:00.000','2006-05-09 00:00:00.000','2006-05-10 00:00:00.000','2006-05-11 00:00:00.000','2006-05-12 00:00:00.000','2006-05-13 00:00:00.000','2006-05-14 00:00:00.000','2006-05-15 00:00:00.000','2006-05-16 00:00:00.000','2006-05-17 00:00:00.000','2006-05-18 00:00:00.000','2006-05-19 00:00:00.000','2006-05-20 00:00:00.000','2006-05-21 00:00:00.000','2006-05-22 00:00:00.000','2006-05-23 00:00:00.000','2006-05-24 00:00:00.000','2006-05-25 00:00:00.000','2006-05-26 00:00:00.000','2006-05-27 00:00:00.000','2006-05-28 00:00:00.000','2006-05-29 00:00:00.000','2006-05-30 00:00:00.000','2006-05-31 00:00:00.000','2006-06-01 00:00:00.000','2006-06-02 00:00:00.000','2006-06-03 00:00:00.000','2006-06-04 00:00:00.000','2006-06-05 00:00:00.000','2006-06-06 00:00:00.000')">
<Intrinsic FunctionName="RangePartitionNew">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-07 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-08 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-09 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-10 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-11 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-12 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-13 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-14 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-15 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-16 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-17 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-18 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-19 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-20 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-21 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-22 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-23 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-24 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-25 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-26 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-27 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-28 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-29 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-30 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-31 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-01 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-02 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-03 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-04 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-05 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-06 00:00:00.000'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="34" EstimateCPU="0.000159937" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.67004" LogicalOp="Index Seek" NodeId="2" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00328494">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Bmk1000" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</DefinedValue>
</DefinedValues>
<Object Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Index="[IX_APRSTrack]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="'EA3ABN-9'">
<Const ConstValue="'EA3ABN-9'" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
<StartRange ScanType="GE">
<RangeColumns>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="'2006-06-06 00:00:00.000'">
<Const ConstValue="'2006-06-06 00:00:00.000'" />
</ScalarOperator>
</RangeExpressions>
</StartRange>
</SeekPredicate>
</SeekPredicates>
<PartitionId>
<ColumnReference Column="ConstExpr1012">
<ScalarOperator ScalarString="(32)">
<Const ConstValue="(32)" />
</ScalarOperator>
</ColumnReference>
</PartitionId>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="35" EstimateCPU="0.000251393" EstimateIO="0.003125" EstimateRebinds="1.67004" EstimateRewinds="0" EstimateRows="1" LogicalOp="RID Lookup" NodeId="7" Parallel="false" PhysicalOp="RID Lookup" EstimatedTotalSubtreeCost="0.00663141">
<OutputList>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Latitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Longitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Icon" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Course" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Speed" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Altitude" />
</OutputList>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Latitude" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Longitude" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Icon" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Course" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Speed" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Altitude" />
</DefinedValue>
</DefinedValues>
<Object Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" TableReferenceId="-1" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Column="Bmk1000" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Bmk1000]">
<Identifier>
<ColumnReference Column="Bmk1000" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<PartitionId>
<ColumnReference Column="PtnIds1007" />
</PartitionId>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<ParameterList>
<ColumnReference Column="@.2" ParameterCompiledValue="'2006-06-06'" />
<ColumnReference Column="@.1" ParameterCompiledValue="'EA3ABN-9'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

Any ideas?

Pete Loveall
AME Corp.

This is definitely an issue with the nonclustered index. I changed the index to a clustered index and everything works fine. In fact, the table originally had a clustered index just on reporttime and IX_APRSTrack was a nonclustered index. When I dropped the reporttime clustered index, all entries added after that point were improperly displayed in any query using the nonclustered index. Once I changed the nonclustered index to a clustered index, every row displays properly now including those that did not display properly previously.

SQL 2005 query has some definite issues with nonclustered indexes. Some of those issues may be related to partioned indexes on partitioned tables, but the nonclustered seeks are definitely wrong. The indexes and databases check just fine using CHECKDB. I also notice that doing a search using a nonclustered index which should be confined to one or two partitions is actually done across all partitions (see the execution plan above). The same SELECT done against a clustered index restricts the lookup to the relavent partitions.

This table has a high rate of insertions which make the clustered index undesireable. However, if that is what must be done until Microsoft fixes the query generator, we'll limp along.

Microsoft, are you listening?

Pete Loveall
AME Corp.

Null result returned even though IS NOT NULL specified.

Hi guys,

I've got a query on a particular table returning an odd result:

SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStation

This query returns the rows I'd expect plus a null row. This doesn't happen in databases at other sites, or in other tables at this site. The following query behaves as I'd expect returning only non-null AccountNumbers.

SELECT DISTINCT AccountNumber
FROM Suppliers
WHERE AccountNumber Is Not Null
ORDER BY AccountNumber

I can't reproduce these results on another site on a table of the same structure, or on another table at this site.

Any suggestions as to what might be going on?

Pertinent info:
--
select @.@.Version

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
dbcc checkdb
Abridged result:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'POS'.
--
SELECT * INTO #Inv FROM Invoice

SELECT DISTINCT WorkStation
FROM #Inv
WHERE WorkStation Is Not Null
ORDER BY WorkStation

Does not reproduce this problem (and so is a probable fix) but the questions remains, what causes this?

TIA,
Karl.are you sure it's null, and not perhaps an empty string instead? try this --SELECT sum(case when WorkStation is null
then 1 end) as nulls
, sum(case when WorkStation = ''
then 1 end) as empties
, sum(case when WorkStation > ''
then 1 end) as somethings
, count(*) as total_rows
FROM Invoice and let's see what kind of totals you get|||Hi Rudy,

Thanks for your reply

are you sure it's null, and not perhaps an empty string instead? try this --SELECT sum(case when WorkStation is null
then 1 end) as nulls
, sum(case when WorkStation = ''
then 1 end) as empties
, sum(case when WorkStation > ''
then 1 end) as somethings
, count(*) as total_rows
FROM Invoice and let's see what kind of totals you get

Returns:
nulls empties somethings total_rows
---- ---- ---- ----
25212 NULL 2660565 2685777

(1 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.

It would seem that the nulls are slipping into somewhere they shouldn't be for you too :)|||It would seem that the nulls are slipping into somewhere they shouldn't be for you too :)no, what makes you say that?

25212 + 0 + 2660565 = 2685777

so everything is accounted for

you said earlier that this query returns a NULL row --SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStationmay i ask how do you know it does this? where are you running the query, and how do you detect the NULL?|||no, what makes you say that?

An incorrect assumption on my part. Moving right along :)

you said earlier that this query returns a NULL row --SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStationmay i ask how do you know it does this? where are you running the query, and how do you detect the NULL?

This error was reported by the client who uses this database (the app they use uses ADO) but I've been using Query Analyzer to see it.

My method of null detection and confirmation is twofold:
- Occular examination (it's the first record and there's only ~20 rows)
- SELECT WorkStation
FROM Invoice
WHERE WorkStation = 'NULL' returns no rows.

-Karl.|||of course WHERE WorkStation = 'NULL' returns 0 rows

the correct syntax is WHERE WorkStation IS NULL|||Thanks for your time Rudy,

of course WHERE WorkStation = 'NULL' returns 0 rows

the correct syntax is WHERE WorkStation IS NULL

My intention for that second query is to show the 'NULL' result from the first query was in fact a null, and not a string containing the phrase 'NULL' as the two appear identical in the result pane.|||I know this has been somewhat covered, but just for gits and shiggles, what happens if you do this?

SELECT DISTINCT WorkStation
FROM Invoice
WHERE IsNull(WorkStation, '') <> ''
ORDER BY WorkStation|||Thanks for your reply Teddy,

That query returns the results I hoped that I'd get from the original one. It returns a list of workstations and contains no blank, or null, records.

I think I'm just going to:
-select the data into a temp table
-drop the original table
-select the data the original table
as I believe this will cease the behaviour, but it does mean we never find out why it occurred...|||If the query I posted returns your intended results, I would highly suggest you make sure you don't have empty strings in your workstation column. What that clause does is first convert null values to an empty string, and then filter by anything that doesn't have an empty string. I would be curious if you had some fields with just spaces in them as well, as there is an environmental setting that would automagically trim those spaces to ''.

Example:

-- Create a scratch table
CREATE TABLE #NullVsEmpty (test_column char(1))

-- Dump two rows into scratch table. One is empty, one is null
INSERT INTO #NullVsEmpty VALUES ('')
INSERT INTO #NullVsEmpty VALUES (NULL)

-- Display whats in scratch table for reference
select * from #NullVsEmpty

-- Count where test_column is not null
SELECT COUNT(*) FROM #NullVsEmpty WHERE test_column IS NOT NULL

-- Count where test_column is not EQUAL TO an empty string.
-- Note this returns 0 because Null cannot be directly compared to a scalar value
SELECT COUNT(*) FROM #NullVsEmpty WHERE test_column <> ''

-- Convert test_column to a finite value and then do comparison
SELECT COUNT(*) FROM #NullVsEmpty WHERE ISNULL(test_column, '') <> ''

DROP TABLE #NullVsEmpty

Null response

I don't, generally, allow nullsunless I have a darn good reason.
The biggest reason for NOT using NULLs is that they are so meaningless. You
have no idea what it means if someone enters a NULL. Does it mean the value
isn't known now, the value can never be known, the value isn't relevant, or
something else.
Earlier today there was a post where someone was adding numbers and wanted
NULL to be considered 0. Someone used the example of a bank account balance,
and said a 0 would really mean no money, but NULL would mean no bank
account. That is not completely true. NULL could also mean that you are just
so disorganized you haven't balanced your statement in years, and you have
absolutely no idea what your balance is. It also could mean you haven't
looked in the last day and just don't know right now.
NULL means unknown, and there are so many reasons a value could be unknown
that I like to avoid it if possible. Plus the fact that NULLs are never
equal to anything, but they are also never NOT equal to anything, so you end
up with tri-valued logic. This makes your coding much more complex.
So what's your point?
-- Carol wrote: --
I don't, generally, allow nullsunless I have a darn good reason.
The biggest reason for NOT using NULLs is that they are so meaningless. You
have no idea what it means if someone enters a NULL. Does it mean the value
isn't known now, the value can never be known, the value isn't relevant, or
something else.
Earlier today there was a post where someone was adding numbers and wanted
NULL to be considered 0. Someone used the example of a bank account balance,
and said a 0 would really mean no money, but NULL would mean no bank
account. That is not completely true. NULL could also mean that you are just
so disorganized you haven't balanced your statement in years, and you have
absolutely no idea what your balance is. It also could mean you haven't
looked in the last day and just don't know right now.
NULL means unknown, and there are so many reasons a value could be unknown
that I like to avoid it if possible. Plus the fact that NULLs are never
equal to anything, but they are also never NOT equal to anything, so you end
up with tri-valued logic. This makes your coding much more complex.

Null replacement on Excel Query

Hello everyone,

i'm using an excel source where i get my excel rows using a query, I'd like to replace possible null values with some other data(a zero value or a empty string for example), that's because i'm performing a transformation into a sql server table wich doesn't accept null values for some columns.

Is there any function to convert a null value to another one? I used the sql server's CASE function, but it didn't work. Any suggestions?

thanks a lot.

Have you tried using Data Conversion transform...

you could use an expression like

IsNull(Column) ? ValueifNull : ValueifNotNull

See some examples here:

http://msdn2.microsoft.com/en-us/library/ms141184.aspx

|||Thanks a lot, the code and the article helped a lot.

I used an Derived Column Transformation to parse null values to zeros with the expression. The good thing is that i don't loose the mapping to the old columns. that's great.

Additionally this article helps to understand Derived Column Transformation:
http://msdn2.microsoft.com/en-us/library/ms141069.aspx

regards amigo|||

So, you got it!

That's great.

Null Reference Exception

I cannot seem to get the crystal reports to work in asp.net

I set the databindings source property and the report shows in the designer a little weird. In the Initialize component, i call the BindData() and when i run the app i get a NullReferenceException.

I did exactly what a walkthrough from msdn said to do and its not working.

I also tried setting a breakpoint on InitializeComponent to see what was happening, but it errors before it gets called.

Any suggestions about this NullReferenceExceptionStill need help on this one

NULL Records

I am having a problem with our sql database. We keep getting NULL records
in some of our tables. It has not been a problem until today when we got two
NULL records and now I am having a problem deleting them. I am getting an
error saying KEY Column information is insufficient or incomplete. Too many
rows affected by update. I know I am getting this since both of the records
are the same, but I can't find out how to delete them. I tried to write a
script in the QA, but the column that I need to search under is called KEY.
Is this my problem, or could it be something else. Thanks for the help.DELETE FROM <table_name> WHERE <column_name> IS NULL
<column_name> is the column with the NULL values.
--
Jacco Schalkwijk
SQL Server MVP
"rick" <rick@.discussions.microsoft.com> wrote in message
news:9D293DE1-2FFC-4231-B9FB-5FB470911D63@.microsoft.com...
>I am having a problem with our sql database. We keep getting NULL records
> in some of our tables. It has not been a problem until today when we got
> two
> NULL records and now I am having a problem deleting them. I am getting an
> error saying KEY Column information is insufficient or incomplete. Too
> many
> rows affected by update. I know I am getting this since both of the
> records
> are the same, but I can't find out how to delete them. I tried to write a
> script in the QA, but the column that I need to search under is called
> KEY.
> Is this my problem, or could it be something else. Thanks for the help.|||That was the code I was writing, but it kept giving me an error say syntax
was wrong after KEY. Where KEY is the column name. Thanks
"Jacco Schalkwijk" wrote:
> DELETE FROM <table_name> WHERE <column_name> IS NULL
> <column_name> is the column with the NULL values.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "rick" <rick@.discussions.microsoft.com> wrote in message
> news:9D293DE1-2FFC-4231-B9FB-5FB470911D63@.microsoft.com...
> >I am having a problem with our sql database. We keep getting NULL records
> > in some of our tables. It has not been a problem until today when we got
> > two
> > NULL records and now I am having a problem deleting them. I am getting an
> > error saying KEY Column information is insufficient or incomplete. Too
> > many
> > rows affected by update. I know I am getting this since both of the
> > records
> > are the same, but I can't find out how to delete them. I tried to write a
> > script in the QA, but the column that I need to search under is called
> > KEY.
> > Is this my problem, or could it be something else. Thanks for the help.
>
>|||Is the column that you have in your table called KEY?
In that case you have to use delimiters around your column name because KEY
is a reserved keyword in T-SQL. Delimiters can be either square brackets
([..]) or double quotes ("..."). For example
DELETE FROM <table_name> WHERE [KEY] IS NULL
--
Jacco Schalkwijk
SQL Server MVP
"rick" <rick@.discussions.microsoft.com> wrote in message
news:63A09A7D-F4DD-4C8F-8FD8-2FBD9D632B10@.microsoft.com...
> That was the code I was writing, but it kept giving me an error say syntax
> was wrong after KEY. Where KEY is the column name. Thanks
> "Jacco Schalkwijk" wrote:
>> DELETE FROM <table_name> WHERE <column_name> IS NULL
>> <column_name> is the column with the NULL values.
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "rick" <rick@.discussions.microsoft.com> wrote in message
>> news:9D293DE1-2FFC-4231-B9FB-5FB470911D63@.microsoft.com...
>> >I am having a problem with our sql database. We keep getting NULL
>> >records
>> > in some of our tables. It has not been a problem until today when we
>> > got
>> > two
>> > NULL records and now I am having a problem deleting them. I am getting
>> > an
>> > error saying KEY Column information is insufficient or incomplete. Too
>> > many
>> > rows affected by update. I know I am getting this since both of the
>> > records
>> > are the same, but I can't find out how to delete them. I tried to
>> > write a
>> > script in the QA, but the column that I need to search under is called
>> > KEY.
>> > Is this my problem, or could it be something else. Thanks for the
>> > help.
>>|||Jacco your a hero. That was what was wrong. I just didn't know how to make
SQL think KEY was a column and not something else. So thank you very much.
One last thing. Do you think it is a problem that I have a column named KEY.
Is that why I could be getting these null records. Thanks again.
"Jacco Schalkwijk" wrote:
> Is the column that you have in your table called KEY?
> In that case you have to use delimiters around your column name because KEY
> is a reserved keyword in T-SQL. Delimiters can be either square brackets
> ([..]) or double quotes ("..."). For example
> DELETE FROM <table_name> WHERE [KEY] IS NULL
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "rick" <rick@.discussions.microsoft.com> wrote in message
> news:63A09A7D-F4DD-4C8F-8FD8-2FBD9D632B10@.microsoft.com...
> > That was the code I was writing, but it kept giving me an error say syntax
> > was wrong after KEY. Where KEY is the column name. Thanks
> >
> > "Jacco Schalkwijk" wrote:
> >
> >> DELETE FROM <table_name> WHERE <column_name> IS NULL
> >>
> >> <column_name> is the column with the NULL values.
> >>
> >> --
> >> Jacco Schalkwijk
> >> SQL Server MVP
> >>
> >>
> >> "rick" <rick@.discussions.microsoft.com> wrote in message
> >> news:9D293DE1-2FFC-4231-B9FB-5FB470911D63@.microsoft.com...
> >> >I am having a problem with our sql database. We keep getting NULL
> >> >records
> >> > in some of our tables. It has not been a problem until today when we
> >> > got
> >> > two
> >> > NULL records and now I am having a problem deleting them. I am getting
> >> > an
> >> > error saying KEY Column information is insufficient or incomplete. Too
> >> > many
> >> > rows affected by update. I know I am getting this since both of the
> >> > records
> >> > are the same, but I can't find out how to delete them. I tried to
> >> > write a
> >> > script in the QA, but the column that I need to search under is called
> >> > KEY.
> >> > Is this my problem, or could it be something else. Thanks for the
> >> > help.
> >>
> >>
> >>
>
>|||I don't think that the fact that your column is named KEY is the cause of
the NULL values. But you can avoid NULL values and duplicate values
altogether by creating a Primary Key on the column. That will probably cause
the application that tries to insert the NULL values to crash, but then at
least you know where they come from and you can try to fix the code.
--
Jacco Schalkwijk
SQL Server MVP
"rick" <rick@.discussions.microsoft.com> wrote in message
news:F0F08F87-46BF-472E-8005-66532ACBD8A6@.microsoft.com...
> Jacco your a hero. That was what was wrong. I just didn't know how to
> make
> SQL think KEY was a column and not something else. So thank you very
> much.
> One last thing. Do you think it is a problem that I have a column named
> KEY.
> Is that why I could be getting these null records. Thanks again.
> "Jacco Schalkwijk" wrote:
>> Is the column that you have in your table called KEY?
>> In that case you have to use delimiters around your column name because
>> KEY
>> is a reserved keyword in T-SQL. Delimiters can be either square brackets
>> ([..]) or double quotes ("..."). For example
>> DELETE FROM <table_name> WHERE [KEY] IS NULL
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "rick" <rick@.discussions.microsoft.com> wrote in message
>> news:63A09A7D-F4DD-4C8F-8FD8-2FBD9D632B10@.microsoft.com...
>> > That was the code I was writing, but it kept giving me an error say
>> > syntax
>> > was wrong after KEY. Where KEY is the column name. Thanks
>> >
>> > "Jacco Schalkwijk" wrote:
>> >
>> >> DELETE FROM <table_name> WHERE <column_name> IS NULL
>> >>
>> >> <column_name> is the column with the NULL values.
>> >>
>> >> --
>> >> Jacco Schalkwijk
>> >> SQL Server MVP
>> >>
>> >>
>> >> "rick" <rick@.discussions.microsoft.com> wrote in message
>> >> news:9D293DE1-2FFC-4231-B9FB-5FB470911D63@.microsoft.com...
>> >> >I am having a problem with our sql database. We keep getting NULL
>> >> >records
>> >> > in some of our tables. It has not been a problem until today when
>> >> > we
>> >> > got
>> >> > two
>> >> > NULL records and now I am having a problem deleting them. I am
>> >> > getting
>> >> > an
>> >> > error saying KEY Column information is insufficient or incomplete.
>> >> > Too
>> >> > many
>> >> > rows affected by update. I know I am getting this since both of the
>> >> > records
>> >> > are the same, but I can't find out how to delete them. I tried to
>> >> > write a
>> >> > script in the QA, but the column that I need to search under is
>> >> > called
>> >> > KEY.
>> >> > Is this my problem, or could it be something else. Thanks for the
>> >> > help.
>> >>
>> >>
>> >>
>>|||Thanks again, I will try that.
"Jacco Schalkwijk" wrote:
> I don't think that the fact that your column is named KEY is the cause of
> the NULL values. But you can avoid NULL values and duplicate values
> altogether by creating a Primary Key on the column. That will probably cause
> the application that tries to insert the NULL values to crash, but then at
> least you know where they come from and you can try to fix the code.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "rick" <rick@.discussions.microsoft.com> wrote in message
> news:F0F08F87-46BF-472E-8005-66532ACBD8A6@.microsoft.com...
> > Jacco your a hero. That was what was wrong. I just didn't know how to
> > make
> > SQL think KEY was a column and not something else. So thank you very
> > much.
> > One last thing. Do you think it is a problem that I have a column named
> > KEY.
> > Is that why I could be getting these null records. Thanks again.
> >
> > "Jacco Schalkwijk" wrote:
> >
> >> Is the column that you have in your table called KEY?
> >>
> >> In that case you have to use delimiters around your column name because
> >> KEY
> >> is a reserved keyword in T-SQL. Delimiters can be either square brackets
> >> ([..]) or double quotes ("..."). For example
> >>
> >> DELETE FROM <table_name> WHERE [KEY] IS NULL
> >>
> >> --
> >> Jacco Schalkwijk
> >> SQL Server MVP
> >>
> >>
> >> "rick" <rick@.discussions.microsoft.com> wrote in message
> >> news:63A09A7D-F4DD-4C8F-8FD8-2FBD9D632B10@.microsoft.com...
> >> > That was the code I was writing, but it kept giving me an error say
> >> > syntax
> >> > was wrong after KEY. Where KEY is the column name. Thanks
> >> >
> >> > "Jacco Schalkwijk" wrote:
> >> >
> >> >> DELETE FROM <table_name> WHERE <column_name> IS NULL
> >> >>
> >> >> <column_name> is the column with the NULL values.
> >> >>
> >> >> --
> >> >> Jacco Schalkwijk
> >> >> SQL Server MVP
> >> >>
> >> >>
> >> >> "rick" <rick@.discussions.microsoft.com> wrote in message
> >> >> news:9D293DE1-2FFC-4231-B9FB-5FB470911D63@.microsoft.com...
> >> >> >I am having a problem with our sql database. We keep getting NULL
> >> >> >records
> >> >> > in some of our tables. It has not been a problem until today when
> >> >> > we
> >> >> > got
> >> >> > two
> >> >> > NULL records and now I am having a problem deleting them. I am
> >> >> > getting
> >> >> > an
> >> >> > error saying KEY Column information is insufficient or incomplete.
> >> >> > Too
> >> >> > many
> >> >> > rows affected by update. I know I am getting this since both of the
> >> >> > records
> >> >> > are the same, but I can't find out how to delete them. I tried to
> >> >> > write a
> >> >> > script in the QA, but the column that I need to search under is
> >> >> > called
> >> >> > KEY.
> >> >> > Is this my problem, or could it be something else. Thanks for the
> >> >> > help.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Saturday, February 25, 2012

NULL Values

Null values

Null Values

Null values

NULL values

NULL Values

Null value problem

Null value issue

NULL Value in Aggregate Function

NULL Value in Aggregate Function

NULL Value in Aggregate Function

Null value handling in SSIS package

NULL value gives unexpected query result

NULL value gives unexpected query result

NULL value gives unexpected query result

Null value for Web Service task input

Null Value

null value

null value

null value

Null value

Null User Problem

null user login failed

Null to Zero in Select Statement?

NULL to Zero

NULL SQL 2000

NULL s in database

NULL Returned for Populated Column

Null result returned even though IS NOT NULL specified.

Null response

Null replacement on Excel Query

Null Reference Exception

NULL Records

NULL in dimensions

Blog Archive

About Me