Collation Issue

In SQL Server 2005 with SQL_Latin1_General_CP1_CS_AS collation, if you create a table:

create table TEST_TABLE

(
ID NVARCHAR(9) NOT NULL,
DESCRIPTION NVARCHAR(30),
);

Run the following insert statement from our Unicode application through ODBC on a system with U. S. English as the default code page:

insert into TEST_TABLE (ID, DESCRIPTION) values ('中文', '123');

The row was added properly. Now if you select * from TEST_TABLE, the row is properly returned to our app with the Chinese characters intact.

However, if you put the Chinese characters as part of the where clause:

select * from TEST_TABLE where ID = '中文';

It would not return anything.

On the other hand, if this is done on Oracle 10g or Access database, the row is returned properly with the Chinese characters intact.

So far we found two ways to fix it, but both involve tremendous risk for us:

1. Change the database collation to Chinese.However, this defeats the purpose of using Unicode as only one language (Chinese in this case) can be used with such configuration.In case we need to mix Chinese, Japanese and Korean, such configuration will not work.

2. Prefix the Unicode strings with N-prefix:

select * from TEST_TABLE where ID = N'中文'

This works perfectly but we have a few hundreds of such strings in our code and would be a nightmare to convert each one of them.

Is there any other solution for SQL Server 2005?

I am not sure that you will be able to accomplish what you are trying. You can change the collation at the column level, rather than the database level if you need to....or, you can add the COLLATE clause to each of your queries. Otherwise, I don't think that there is a really good way to get around it.

Tim

|||

Unfortunately, there are a few things with SQL Server that could use improvement.

UniCode strings 'should' always be prefixed with [ N ] -and especially if the sting contains UniCode characters not supported in the current codepage.

As the saying goes, "I feel your pain". You have have a bit of work to find the 'few hundreds of such strings' and makes the necessary changes.

sqlsql

Collate

Hi,
Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL/NULL do in the following code?
CREATE TABLE [TABLE1] (
[ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL )
Why do I need COLLATE? What does COLLATION mean in table creation?
Thanks a lot!
Mike
Collate sets the order that items are compared with. It determines how an
order by is handled. It also determines whether two items are the same or
different. The collation you have there states that "APPLE" = "Apple" A
different collation will make these different.
This is one of the annoying quirks of the tool to create SQL. Since
individual columns can have their own collation, the tool exports all of the
collations even if the collation is the default for the database.
Unless you have a good reason to use a specific collation, I would delete
the whole collate clause.
Russel Loski, MCSD.Net
"Michael" wrote:

> Hi,
> Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL/NULL do in the following code?
> CREATE TABLE [TABLE1] (
> [ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL )
> Why do I need COLLATE? What does COLLATION mean in table creation?
> Thanks a lot!
> Mike
>
|||Thanks a lot!
RLoski wrote:[vbcol=seagreen]
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of the
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:
|||RLoski,
While generating your script, use unicode option. Then it will not generate
the collate... statements. so you don't need to go and delete them manually.
Venkat
"RLoski" wrote:
[vbcol=seagreen]
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of the
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:

Collate

Hi,
Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL/NULL do in the following code?
CREATE TABLE [TABLE1] (
[ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL )
Why do I need COLLATE? What does COLLATION mean in table creation?
Thanks a lot!
MikeCollate sets the order that items are compared with. It determines how an
order by is handled. It also determines whether two items are the same or
different. The collation you have there states that "APPLE" = "Apple" A
different collation will make these different.
This is one of the annoying quirks of the tool to create SQL. Since
individual columns can have their own collation, the tool exports all of the
collations even if the collation is the default for the database.
Unless you have a good reason to use a specific collation, I would delete
the whole collate clause.
Russel Loski, MCSD.Net
"Michael" wrote:

> Hi,
> Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL/NULL do in the following code?
> CREATE TABLE [TABLE1] (
> [ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT
> NULL )
> Why do I need COLLATE? What does COLLATION mean in table creation?
> Thanks a lot!
> Mike
>|||Thanks a lot!
RLoski wrote:[vbcol=seagreen]
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of t
he
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:
>|||RLoski,
While generating your script, use unicode option. Then it will not generate
the collate... statements. so you don't need to go and delete them manually.
Venkat
"RLoski" wrote:
[vbcol=seagreen]
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of t
he
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:
>

Collate

Hi,
Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL/NULL do in the following code?
CREATE TABLE [TABLE1] (
[ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL )
Why do I need COLLATE? What does COLLATION mean in table creation?
Thanks a lot!
MikeThanks a lot!
RLoski wrote:
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of the
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:
> > Hi,
> >
> > Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> > NULL/NULL do in the following code?
> >
> > CREATE TABLE [TABLE1] (
> > [ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> > NULL )
> >
> > Why do I need COLLATE? What does COLLATION mean in table creation?
> >
> > Thanks a lot!
> > Mike
> >
> >|||RLoski,
While generating your script, use unicode option. Then it will not generate
the collate... statements. so you don't need to go and delete them manually.
Venkat
"RLoski" wrote:
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of the
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:
> > Hi,
> >
> > Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> > NULL/NULL do in the following code?
> >
> > CREATE TABLE [TABLE1] (
> > [ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> > NULL )
> >
> > Why do I need COLLATE? What does COLLATION mean in table creation?
> >
> > Thanks a lot!
> > Mike
> >
> >

Collapse/hide a cell

How would I go about collapsing a table cell if its contents are NULL.
I have two cols. Left side holds number and the right side holds legal stuff
(LongText). When the left side is NULL then the right side is going to be a
title for a new section, and I would like it to be positioned more to the
left and not aligned with the right col. And I'd like to do this without
screwing around with my SQL tableYou should be able to enter a formula in the two visibilty property boxes of
the cell. It would look something like this:
iif(Fields!myField.Value="",True,False) <- for the Visibilty Property
iif(Fields!myField.Value="",myToggleItem,"") <- for the Toggle Item Propery
Hope this helps..
--
Ben Sullins
www.kingofthegreens.com
"JeffreyWowk" wrote:
> How would I go about collapsing a table cell if its contents are NULL.
> I have two cols. Left side holds number and the right side holds legal stuff
> (LongText). When the left side is NULL then the right side is going to be a
> title for a new section, and I would like it to be positioned more to the
> left and not aligned with the right col. And I'd like to do this without
> screwing around with my SQL table
>|||I find that using 'is nothing' works in most cases:
iif(Fields!myField.Value= is nothing,True,False)
--
U. Tokklas
"Ben Sullins" wrote:
> You should be able to enter a formula in the two visibilty property boxes of
> the cell. It would look something like this:
> iif(Fields!myField.Value="",True,False) <- for the Visibilty Property
> iif(Fields!myField.Value="",myToggleItem,"") <- for the Toggle Item Propery
> Hope this helps..
> --
> Ben Sullins
> www.kingofthegreens.com
> "JeffreyWowk" wrote:
> > How would I go about collapsing a table cell if its contents are NULL.
> >
> > I have two cols. Left side holds number and the right side holds legal stuff
> > (LongText). When the left side is NULL then the right side is going to be a
> > title for a new section, and I would like it to be positioned more to the
> > left and not aligned with the right col. And I'd like to do this without
> > screwing around with my SQL table
> >

Code Question - Making a field Null

Hi,
I need to change some code and make a field always show the value NULL
moving forward. I need to keep the field there becuase at the end
result the value will be imported another way.
Currently, the field is coded like this: ISNULL(email_addr,'')
How can I change this to always just be NULL?
Thank you,
RayYou can update the column value to be NULL:
UPDATE MyTable
SET email_addr = NULL;
Then you simply select the column without using ISNULL:
SELECT email_addr
FROM MyTable;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Code Question - Making a field Null

Hi,
I need to change some code and make a field always show the value NULL
moving forward. I need to keep the field there becuase at the end
result the value will be imported another way.
Currently, the field is coded like this: ISNULL(email_addr,'')
How can I change this to always just be NULL?
Thank you,
Ray
You can update the column value to be NULL:
UPDATE MyTable
SET email_addr = NULL;
Then you simply select the column without using ISNULL:
SELECT email_addr
FROM MyTable;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Code - Integer, Nothing vs Zero

The piece of code below attempts to convert minutes to a time string.
The problem I am having is if the value of Minutes is NULL, it treats it as
zero.
Has anyone got any suggestions ?
Thanks
Steve
---
Public Function MinutesToTime(ByVal Minutes AS Integer)
On Error GoTo errorhandler
Dim mins
Dim hrs
If IsNothing(Minutes) Then
MinutesToTime = ""
Else
hrs = Fix(Minutes / 60)
mins = Minutes - (hrs * 60)
If mins < 10 Then mins = "0" & mins
MinutesToTime = hrs & ":" & mins
End If
Exit Function
errorhandler:
MinutesToTime = "##:##"
End FunctionFirst off this is a function, you should be explicit on your return values.
You have two DIM's without specifing the type... then you have a check for
NULL (if nothing) you return a string... so which is it?
What happens if the minutes is actually 0 - what would you return? Would it
be an empty string or "00:00" ' If that is the case, then why not, for NULL
values, return "00:00" - the same as if you specified 0 minutes?
=-Chris
"SteveH" <SteveH@.discussions.microsoft.com> wrote in message
news:05257274-EDEA-438A-B6E5-054E122BC5C1@.microsoft.com...
> The piece of code below attempts to convert minutes to a time string.
> The problem I am having is if the value of Minutes is NULL, it treats it
> as
> zero.
> Has anyone got any suggestions ?
> Thanks
> Steve
> ---
> Public Function MinutesToTime(ByVal Minutes AS Integer)
> On Error GoTo errorhandler
> Dim mins
> Dim hrs
> If IsNothing(Minutes) Then
> MinutesToTime = ""
> Else
> hrs = Fix(Minutes / 60)
> mins = Minutes - (hrs * 60)
> If mins < 10 Then mins = "0" & mins
> MinutesToTime = hrs & ":" & mins
> End If
> Exit Function
> errorhandler:
> MinutesToTime = "##:##"
> End Function
>|||Thanks Chris for your reply
I am wanting to pass in an integer representing minutes and return a string
that looks like a time as per the following examples.
67 returns "1:07"
0 returns "0:00"
NULL returns ""
My problems is that when NULL is passed in I am currently getting "0:00"
instead of "".
I have tried using IsNull function but I get a compilation error "Name
'IsNull' is not declared."
"Chris Conner" wrote:
> First off this is a function, you should be explicit on your return values.
> You have two DIM's without specifing the type... then you have a check for
> NULL (if nothing) you return a string... so which is it?
> What happens if the minutes is actually 0 - what would you return? Would it
> be an empty string or "00:00" ' If that is the case, then why not, for NULL
> values, return "00:00" - the same as if you specified 0 minutes?
> =-Chris
> "SteveH" <SteveH@.discussions.microsoft.com> wrote in message
> news:05257274-EDEA-438A-B6E5-054E122BC5C1@.microsoft.com...
> > The piece of code below attempts to convert minutes to a time string.
> >
> > The problem I am having is if the value of Minutes is NULL, it treats it
> > as
> > zero.
> >
> > Has anyone got any suggestions ?
> >
> > Thanks
> > Steve
> > ---
> >
> > Public Function MinutesToTime(ByVal Minutes AS Integer)
> > On Error GoTo errorhandler
> >
> > Dim mins
> > Dim hrs
> >
> > If IsNothing(Minutes) Then
> > MinutesToTime = ""
> > Else
> > hrs = Fix(Minutes / 60)
> > mins = Minutes - (hrs * 60)
> >
> > If mins < 10 Then mins = "0" & mins
> >
> > MinutesToTime = hrs & ":" & mins
> > End If
> >
> > Exit Function
> >
> > errorhandler:
> > MinutesToTime = "##:##"
> >
> > End Function
> >
>
>|||Reporting Services doesn't support 'code reuse'
so I would reccomend just doing this in Microsoft Access; it would be a
simple format string there; right?
-Aaron
SteveH wrote:
> Thanks Chris for your reply
> I am wanting to pass in an integer representing minutes and return a string
> that looks like a time as per the following examples.
> 67 returns "1:07"
> 0 returns "0:00"
> NULL returns ""
> My problems is that when NULL is passed in I am currently getting "0:00"
> instead of "".
> I have tried using IsNull function but I get a compilation error "Name
> 'IsNull' is not declared."
> "Chris Conner" wrote:
> > First off this is a function, you should be explicit on your return values.
> > You have two DIM's without specifing the type... then you have a check for
> > NULL (if nothing) you return a string... so which is it?
> > What happens if the minutes is actually 0 - what would you return? Would it
> > be an empty string or "00:00" ' If that is the case, then why not, for NULL
> > values, return "00:00" - the same as if you specified 0 minutes?
> >
> > =-Chris
> >
> > "SteveH" <SteveH@.discussions.microsoft.com> wrote in message
> > news:05257274-EDEA-438A-B6E5-054E122BC5C1@.microsoft.com...
> > > The piece of code below attempts to convert minutes to a time string.
> > >
> > > The problem I am having is if the value of Minutes is NULL, it treats it
> > > as
> > > zero.
> > >
> > > Has anyone got any suggestions ?
> > >
> > > Thanks
> > > Steve
> > > ---
> > >
> > > Public Function MinutesToTime(ByVal Minutes AS Integer)
> > > On Error GoTo errorhandler
> > >
> > > Dim mins
> > > Dim hrs
> > >
> > > If IsNothing(Minutes) Then
> > > MinutesToTime = ""
> > > Else
> > > hrs = Fix(Minutes / 60)
> > > mins = Minutes - (hrs * 60)
> > >
> > > If mins < 10 Then mins = "0" & mins
> > >
> > > MinutesToTime = hrs & ":" & mins
> > > End If
> > >
> > > Exit Function
> > >
> > > errorhandler:
> > > MinutesToTime = "##:##"
> > >
> > > End Function
> > >
> >
> >
> >|||Finally found a solution and thought I would post it here for anyone who has
the same problem.
datatype needed to be Nullable(Of Integer) instead of Integer
Public Function MinutesToTime(ByVal Minutes As Nullabe(Of Integer)) AS
String
then use HasValue instead of IsNothing
If Minutes.HasValue Then
and Minutes.Value
"SteveH" wrote:
> Thanks Chris for your reply
> I am wanting to pass in an integer representing minutes and return a string
> that looks like a time as per the following examples.
> 67 returns "1:07"
> 0 returns "0:00"
> NULL returns ""
> My problems is that when NULL is passed in I am currently getting "0:00"
> instead of "".
> I have tried using IsNull function but I get a compilation error "Name
> 'IsNull' is not declared."
> "Chris Conner" wrote:
> > First off this is a function, you should be explicit on your return values.
> > You have two DIM's without specifing the type... then you have a check for
> > NULL (if nothing) you return a string... so which is it?
> > What happens if the minutes is actually 0 - what would you return? Would it
> > be an empty string or "00:00" ' If that is the case, then why not, for NULL
> > values, return "00:00" - the same as if you specified 0 minutes?
> >
> > =-Chris
> >
> > "SteveH" <SteveH@.discussions.microsoft.com> wrote in message
> > news:05257274-EDEA-438A-B6E5-054E122BC5C1@.microsoft.com...
> > > The piece of code below attempts to convert minutes to a time string.
> > >
> > > The problem I am having is if the value of Minutes is NULL, it treats it
> > > as
> > > zero.
> > >
> > > Has anyone got any suggestions ?
> > >
> > > Thanks
> > > Steve
> > > ---
> > >
> > > Public Function MinutesToTime(ByVal Minutes AS Integer)
> > > On Error GoTo errorhandler
> > >
> > > Dim mins
> > > Dim hrs
> > >
> > > If IsNothing(Minutes) Then
> > > MinutesToTime = ""
> > > Else
> > > hrs = Fix(Minutes / 60)
> > > mins = Minutes - (hrs * 60)
> > >
> > > If mins < 10 Then mins = "0" & mins
> > >
> > > MinutesToTime = hrs & ":" & mins
> > > End If
> > >
> > > Exit Function
> > >
> > > errorhandler:
> > > MinutesToTime = "##:##"
> > >
> > > End Function
> > >
> >
> >
> >

Coalesce with Sum

I am having a problem with syntax. I am trying to sum a column where some of the values will be null and because I want to include the rows where the column may be null I am attempting to coalesce to zero.

Below is my sample:

SELECT *

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY q.StudentID

Having

(SUM(Coalesce(q.AbsenceValue),0) = 0.00)

COALESCE(SUM(q.AbsenceValue) = 0.00,0)

I have tried using the coalesce statement a couple of ways with no resolution, pls help!!

Change to this:

COALESCE( q.AbsenceValue, 0)

|||Ok, but how does that incorporate summing the column?|||

Try something like this: (in case you need the student name from your student table)

SELECT w.StudentID, w.StudentName, SUM(Coalesce(q.AbsenceValue,0) ) AS sumAbsenceValue

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY w.StudentID, w.StudentName

But you don't need to do the coalesce: SUM and AVG will skip the NULL value in the caculation.

The follwing should return the same result:

SELECT w.StudentID, w.StudentName, SUM(q.AbsenceValue) AS sumAbsenceValue

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY w.StudentID, w.StudentName

|||

Thanks for putting me on the right track. I actually got the result I needed by modifying your first example a little.

SELECT w.StudentID, SUM(Coalesce(q.AbsenceValue,0) ) AS sumAbsenceValue

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY w.StudentID

Having SUM(Coalesce(q.AbsenceValue,0) ) = 0.00

This gets me the desired result. I still needed to compare the result of the sum so that it equalled 0.00.

Thanks for setting me straight, I was about an inch from pulling hairs .

Coalesce Question

Hi.
I'm using Coalesce to change a NULL returned value from a Select statement.
Here's my code:
SELECT TOP 1 @.StartDate = Coalesce(Discount_Effect_Date, '01/01/1980') FROM
MyTable WHERE RTrim(Subtable_Id) = 'I03U'
Print @.StartDate
When I have a row in MyTable where the Subtable_Id = 'I03U' I get the
@.StartDate
printed out.
When I don't have a row in MyTable where the Subtable_Id = 'I03U' I don't
get the @.StartDate printed out. I was expecting to see 01/01/1980.
What am I missing?
TIA.
RitaRitaG wrote:
> Hi.
> I'm using Coalesce to change a NULL returned value from a Select statement
.
> Here's my code:
> SELECT TOP 1 @.StartDate = Coalesce(Discount_Effect_Date, '01/01/1980') FRO
M
> MyTable WHERE RTrim(Subtable_Id) = 'I03U'
> Print @.StartDate
> When I have a row in MyTable where the Subtable_Id = 'I03U' I get the
> @.StartDate
> printed out.
> When I don't have a row in MyTable where the Subtable_Id = 'I03U' I don't
> get the @.StartDate printed out. I was expecting to see 01/01/1980.
> What am I missing?
> TIA.
> Rita
The problem is that if the SELECT doesn't return any rows then the
assignment will never be made. For single value assignments use SET
instead of SELECT.
SET @.startdate =
(SELECT COALESCE(MIN(discount_effect_date),'1980
0101')
FROM mytable
WHERE RTRIM(subtable_id) = 'I03U');
I assume you meant to retrieve the minimum date but in the query you
posted you specified TOP without ORDER BY so in fact your results may
be unpredictable.
The YYYYMMDD format I've used is better for dates because it's
unambiguous and doesn't depend on any of your regional connection
settings.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Coalesce works on individual columns, for each row returned by your query.
If no row is returned, then there is no column value for coalesce to work
on. Coalesce is only useful when you have rows returned, but a particular
column is null, and you want to return a default value instead of the null.
You need to either change your SQL to insure that a row is always returned
(probably not what you want to do) or check to see if any rows were returned
(which appears to be your goal anyway).
Simply checking to see if @.StartDate is null AFTER your select may
accomplish what you want.
If you post the rest of your code, folks may be able to give some better
advice. Since this code is out of context, I can only guess at what you are
doing with it.
"RitaG" <RitaG@.discussions.microsoft.com> wrote in message
news:F739466D-0D03-4843-A46B-E9F3BF0AD5AB@.microsoft.com...
> Hi.
> I'm using Coalesce to change a NULL returned value from a Select
statement.
> Here's my code:
> SELECT TOP 1 @.StartDate = Coalesce(Discount_Effect_Date, '01/01/1980')
FROM
> MyTable WHERE RTrim(Subtable_Id) = 'I03U'
> Print @.StartDate
> When I have a row in MyTable where the Subtable_Id = 'I03U' I get the
> @.StartDate
> printed out.
> When I don't have a row in MyTable where the Subtable_Id = 'I03U' I don't
> get the @.StartDate printed out. I was expecting to see 01/01/1980.
> What am I missing?
> TIA.
> Rita|||"RitaG" <RitaG@.discussions.microsoft.com> wrote in message
news:F739466D-0D03-4843-A46B-E9F3BF0AD5AB@.microsoft.com...
> Hi.
> I'm using Coalesce to change a NULL returned value from a Select
> statement.
> Here's my code:
> SELECT TOP 1 @.StartDate = Coalesce(Discount_Effect_Date, '01/01/1980')
> FROM
> MyTable WHERE RTrim(Subtable_Id) = 'I03U'
> Print @.StartDate
> When I have a row in MyTable where the Subtable_Id = 'I03U' I get the
> @.StartDate
> printed out.
> When I don't have a row in MyTable where the Subtable_Id = 'I03U' I don't
> get the @.StartDate printed out. I was expecting to see 01/01/1980.
> What am I missing?
> TIA.
> Rita
Your WHERE clause only allows data to be printed out WHERE your Subtable ID
= I03U.
The other issue I see here is the use of your COALESCE... While this should
work for what you are doing, an ISNULL(Discount_Effect_Date, '01/01/1980')
may work better for you. COALESCE is generally used to find the first
non-null value in a list of columns. For example COALESCE(payrate_salary,
payrate_daily, payrate_hourly)
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks to all for your responses.
The dates are all the same that satisfy the WHERE clause so I could have
used a Distinct but I think Top 1 is faster.
The SET @.startdate =
(SELECT COALESCE(MIN(discount_effect_date),'1980
0101')
FROM mytable
WHERE RTRIM(subtable_id) = 'I03U');
will work for me. Thanks for the "YYYYMMDD format" suggestion.
Learn something every day! :-)
"David Portas" wrote:

> RitaG wrote:
> The problem is that if the SELECT doesn't return any rows then the
> assignment will never be made. For single value assignments use SET
> instead of SELECT.
> SET @.startdate =
> (SELECT COALESCE(MIN(discount_effect_date),'1980
0101')
> FROM mytable
> WHERE RTRIM(subtable_id) = 'I03U');
> I assume you meant to retrieve the minimum date but in the query you
> posted you specified TOP without ORDER BY so in fact your results may
> be unpredictable.
> The YYYYMMDD format I've used is better for dates because it's
> unambiguous and doesn't depend on any of your regional connection
> settings.
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

coalesce does not seem to work

Hi,

I have the following table with some sample values, I want to return the first non null value in that order. COALESCE does not seem to work for me, it does not return the 3rd record. I need to include this in my select statement. Any urgent help please.

Mobile Business Private

NULL 345 NULL
4646 65464 65765
NULL 564
654654 564 6546

I want the following as my results:

Number

345
4646
564
654654

Select COALESCE(Mobile,Business,Private) as Number from Table returns:

345
4646

654654

(this is a test to see if private returns & it did with is not null but then how do i include in my select statement to show any one of the 3 fields)

select mobile,business,private where private is not null returns:

65765
564
6546

thanks

As you mentioned, COALESCE returns the first Non NULL value. You result is not what you want but the COALESCE is correct. You have a blank cell in your table. It is not NULL. You can use a CASE statement to check either NULL or blank to get the result you want. Or you can make sure your missing value cells are NULL.

HTH.

Coalesce and CASE

I'd like to expand a coalesce function to account for Case 3, which says tha
t
if Date2 is null, then stay with null. As you see the following only
satisfies Case 1 and Case 2. Any ideas appreciated. I think that a fancy
CASE statement might do it, but I can't figure it out. Thanks, --Bob
Select COALESCE(Date1, Date2) as DateX From tableXYZ
Desired
output
Date1 Date2 DateX
Case 1 11/08/05 11/14/05 11/08/05
Case 2 null 11/14/05 11/14/05
Case 3 11/08/05 null nullSELECT CASE WHEN date2 IS NULL
THEN CAST( NULL AS DATETIME)
ELSE COALESCE (date1, date2) END
AS date_x
FROM Foobar;
The CAST() can be important in this kind of CASE expression.|||SELECT 'DateX'=
CASE
WHEN Date2 IS NULL THEN NULL
WHEN Date1 IS NULL THEN Date2
ELSE Date1
END
FROM TableXYZ
This works if you always want Date1 returned for the value of DateX when
neither are NULL.
"BobS" wrote:

> I'd like to expand a coalesce function to account for Case 3, which says t
hat
> if Date2 is null, then stay with null. As you see the following only
> satisfies Case 1 and Case 2. Any ideas appreciated. I think that a fancy
> CASE statement might do it, but I can't figure it out. Thanks, --Bob
> Select COALESCE(Date1, Date2) as DateX From tableXYZ
> Desired
> output
> Date1 Date2 DateX
> Case 1 11/08/05 11/14/05 11/08/05
> Case 2 null 11/14/05 11/14/05
> Case 3 11/08/05 null null
>
>

COALESCE an integer?

Hi guys
I've got a table with a field called PMon_Total. At the moment, a lot have
rows have this set to null.
I want to substitute this NULL for a 0 so that I can count all the
PMon_Total fields, however, I can't get coalesce to work.
Here's my select...
SELECT ProjectID, SessionID, SessionDate, PMon_Total FROM Sessions WHE...
Works fine if I try and grab PMon_Total values from C#, as long as they
arent null! However, if I put
SELECT ProjectID, SessionID, SessionDate, COALESCE(PMon_Total,0) FROM
Sessions WHE...
my code give me a System.IndexOutOfRange exception. How can I coalesce
PMon_Total, so I know that any nulls will show as 0?!
Cheers
Dan> I want to substitute this NULL for a 0 so that I can count all the
> PMon_Total fields, however, I can't get coalesce to work.
I'm not sure what this means. Why would counting require you to change
nulls to zero? COUNT(*) will count rows whether NULL or not.

> my code give me a System.IndexOutOfRange exception.
That doesn't look like a SQL Server error. Can you tell us the exact
error message and error number please. Did you try the query in Query
Analyzer? Post some code to reproduce the problem if you can. I suspect
this is a client-side error.
David Portas
SQL Server MVP
--|||try
SELECT ProjectID, SessionID, SessionDate, isnull(PMon_Total,0) FROM
Sessions WHE...
Regards
R.D
--Knowledge gets doubled when shared
"dhnriverside" wrote:

> Hi guys
> I've got a table with a field called PMon_Total. At the moment, a lot have
> rows have this set to null.
> I want to substitute this NULL for a 0 so that I can count all the
> PMon_Total fields, however, I can't get coalesce to work.
> Here's my select...
> SELECT ProjectID, SessionID, SessionDate, PMon_Total FROM Sessions WHE...
> Works fine if I try and grab PMon_Total values from C#, as long as they
> arent null! However, if I put
> SELECT ProjectID, SessionID, SessionDate, COALESCE(PMon_Total,0) FROM
> Sessions WHE...
> my code give me a System.IndexOutOfRange exception. How can I coalesce
> PMon_Total, so I know that any nulls will show as 0?!
> Cheers
>
> Dan|||It's weird. I've just tried isnull() and that gave me the error as well.
Here's the C# code I'm using (where sDate = '2005-04-01' and eDate =
'2005-10-13')...
sql = "SELECT ProjectID, SessionID, SessionDate, PMon_Total FROM Sessions
WHERE (SessionDate > '" + sDate + "') AND (SessionDate < '" + eDate + "')
ORDER BY ProjectID";
SqlConnection dbConn = new
SqlConnection(ConfigurationSettings.AppSettings.Get("System_ConnectionString
"));
dbConn.Open();
SqlCommand prjCmd = new SqlCommand(sql, dbConn);
SqlDataReader prjDr = prjCmd.ExecuteReader();
try
{
while(prjDr.Read())
{
Response.Write(prjDr["ProjectID"].ToString() + ", " +
prjDr["SessionID"].ToString() + ", " + prjDr["SessionDate"].ToString() + "|"
);
if(Convert.ToInt32(prjDr["ProjectID"].ToString()) > projID)
{
projC++;
projID = Convert.ToInt32(prjDR["ProjectID"].ToString());
Response.Write("new!");
}
seshC++;
Response.Write(", " + prjDr["PMon_Total"].ToString() + "<br>");
}
}
finally
{
prjDr.Close();
prjDr = null;
dbConn.Close();
dbConn = null;
}
The response.write statements are purely for debugging :)
Basically, with the code like that, it works perfectly. However, If I try
sql = "SELECT ProjectID, SessionID, SessionDate, COALESCE(PMon_Total,0) FROM
Sessions WHERE (SessionDate > '" + sDate + "') AND (SessionDate < '" + eDate
+ "') ORDER BY ProjectID";
or isnull() - i get the following error...
Server Error in '/dartsIntranetV2.1' Application.
----
--
PMon_Total
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information abou
t
the error and where it originated in the code.
Exception Details: System.IndexOutOfRangeException: PMon_Total
Source Error:
An unhandled exception was generated during the execution of the current web
request. Information regarding the origin and location of the exception can
be identified using the exception stack trace below.
The error message identifies line 223, which is effectively the last line in
the loop...
Response.Write(", " + prjDr["PMon_Total"].ToString() + "<br>");
Any help much appreciated!
Cheers
Dan
----
"David Portas" wrote:

> I'm not sure what this means. Why would counting require you to change
> nulls to zero? COUNT(*) will count rows whether NULL or not.
>
> That doesn't look like a SQL Server error. Can you tell us the exact
> error message and error number please. Did you try the query in Query
> Analyzer? Post some code to reproduce the problem if you can. I suspect
> this is a client-side error.
> --
> David Portas
> SQL Server MVP
> --
>|||If you print the SQL statement you send to SQL Server (or use Profiler to ca
tch it), you can do two
things:
1. Execute in Query Analyzer to see whether the problem is in the client or
in SQL Server.
2. Post it here so we can have a look at it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"dhnriverside" <dan@.musoswire.com> wrote in message
news:EE59E12B-A568-4482-95C0-D6E7C67CFE86@.microsoft.com...
> It's weird. I've just tried isnull() and that gave me the error as well.
> Here's the C# code I'm using (where sDate = '2005-04-01' and eDate =
> '2005-10-13')...
> sql = "SELECT ProjectID, SessionID, SessionDate, PMon_Total FROM Sessions
> WHERE (SessionDate > '" + sDate + "') AND (SessionDate < '" + eDate + "')
> ORDER BY ProjectID";
>
> SqlConnection dbConn = new
> SqlConnection(ConfigurationSettings.AppSettings.Get("System_ConnectionStri
ng"));
> dbConn.Open();
> SqlCommand prjCmd = new SqlCommand(sql, dbConn);
> SqlDataReader prjDr = prjCmd.ExecuteReader();
> try
> {
> while(prjDr.Read())
> {
> Response.Write(prjDr["ProjectID"].ToString() + ", " +
> prjDr["SessionID"].ToString() + ", " + prjDr["SessionDate"].ToString() + "
|");
> if(Convert.ToInt32(prjDr["ProjectID"].ToString()) > projID)
> {
> projC++;
> projID = Convert.ToInt32(prjDR["ProjectID"].ToString());
> Response.Write("new!");
> }
> seshC++;
> Response.Write(", " + prjDr["PMon_Total"].ToString() + "<br>");
> }
> }
> finally
> {
> prjDr.Close();
> prjDr = null;
> dbConn.Close();
> dbConn = null;
> }
> The response.write statements are purely for debugging :)
> Basically, with the code like that, it works perfectly. However, If I try
> sql = "SELECT ProjectID, SessionID, SessionDate, COALESCE(PMon_Total,0) FR
OM
> Sessions WHERE (SessionDate > '" + sDate + "') AND (SessionDate < '" + eDa
te
> + "') ORDER BY ProjectID";
> or isnull() - i get the following error...
> Server Error in '/dartsIntranetV2.1' Application.
> ----
--
> PMon_Total
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information ab
out
> the error and where it originated in the code.
> Exception Details: System.IndexOutOfRangeException: PMon_Total
> Source Error:
> An unhandled exception was generated during the execution of the current w
eb
> request. Information regarding the origin and location of the exception ca
n
> be identified using the exception stack trace below.
> The error message identifies line 223, which is effectively the last line
in
> the loop...
> Response.Write(", " + prjDr["PMon_Total"].ToString() + "<br>");
> Any help much appreciated!
> Cheers
>
> Dan
> ----
> "David Portas" wrote:
>|||This is a C# error so you may have more luck in a C# forum.
Why aren't you using the parameters collection to pass your parameters?
That at least would eliminate the possibility of a corrupt command
string due to invalid input. It also avoids any SQL injection
vulnerabilities. Example at:
http://msdn.microsoft.com/library/d.../>
ersTopic.asp
You should also consider putting the SQL in a proc since the query
appears to be static anyway.
David Portas
SQL Server MVP
--|||You need to give the column an alias,
SELECT ProjectID, SessionID, SessionDate, COALESCE(PMon_Total,0) AS
PMon_Total FROM
Sessions WHE...
That should fix it - it was being returned to c# with no column name.
"dhnriverside" <dan@.musoswire.com> wrote in message
news:13A0B95A-CBB3-40D3-9FF6-4B6762CB8A6D@.microsoft.com...
> Hi guys
> I've got a table with a field called PMon_Total. At the moment, a lot have
> rows have this set to null.
> I want to substitute this NULL for a 0 so that I can count all the
> PMon_Total fields, however, I can't get coalesce to work.
> Here's my select...
> SELECT ProjectID, SessionID, SessionDate, PMon_Total FROM Sessions WHE...
> Works fine if I try and grab PMon_Total values from C#, as long as they
> arent null! However, if I put
> SELECT ProjectID, SessionID, SessionDate, COALESCE(PMon_Total,0) FROM
> Sessions WHE...
> my code give me a System.IndexOutOfRange exception. How can I coalesce
> PMon_Total, so I know that any nulls will show as 0?!
> Cheers
>
> Dan|||> Basically, with the code like that, it works perfectly. However, If I try
> sql = "SELECT ProjectID, SessionID, SessionDate, COALESCE(PMon_Total,0)
> FROM
> Sessions WHERE (SessionDate > '" + sDate + "') AND (SessionDate < '" +
> eDate
> + "') ORDER BY ProjectID";
> or isnull() - i get the following error...
This SQL statement doesn't include an alias for COALESCE(PMon_Total,0). You
get an IndexOutOfRangeException exception when you reference
prjDr["PMon_Total"] because no column with that name exists in the result.
Try specifying PMon_Total as the alias for the COALESCE expression:
sql = "SELECT ProjectID, SessionID, SessionDate,
COALESCE(PMon_Total,0) AS PMon_Total
FROM Sessions
WHERE (SessionDate > '" + sDate + "') AND (SessionDate < '" + eDate
+ "') ORDER BY ProjectID";
Hope this helps.
Dan Guzman
SQL Server MVP
"dhnriverside" <dan@.musoswire.com> wrote in message
news:EE59E12B-A568-4482-95C0-D6E7C67CFE86@.microsoft.com...
> It's weird. I've just tried isnull() and that gave me the error as well.
> Here's the C# code I'm using (where sDate = '2005-04-01' and eDate =
> '2005-10-13')...
> sql = "SELECT ProjectID, SessionID, SessionDate, PMon_Total FROM Sessions
> WHERE (SessionDate > '" + sDate + "') AND (SessionDate < '" + eDate + "')
> ORDER BY ProjectID";
>
> SqlConnection dbConn = new
> SqlConnection(ConfigurationSettings.AppSettings.Get("System_ConnectionStri
ng"));
> dbConn.Open();
> SqlCommand prjCmd = new SqlCommand(sql, dbConn);
> SqlDataReader prjDr = prjCmd.ExecuteReader();
> try
> {
> while(prjDr.Read())
> {
> Response.Write(prjDr["ProjectID"].ToString() + ", " +
> prjDr["SessionID"].ToString() + ", " + prjDr["SessionDate"].ToString() +
> "|");
> if(Convert.ToInt32(prjDr["ProjectID"].ToString()) > projID)
> {
> projC++;
> projID = Convert.ToInt32(prjDR["ProjectID"].ToString());
> Response.Write("new!");
> }
> seshC++;
> Response.Write(", " + prjDr["PMon_Total"].ToString() + "<br>");
> }
> }
> finally
> {
> prjDr.Close();
> prjDr = null;
> dbConn.Close();
> dbConn = null;
> }
> The response.write statements are purely for debugging :)
> Basically, with the code like that, it works perfectly. However, If I try
> sql = "SELECT ProjectID, SessionID, SessionDate, COALESCE(PMon_Total,0)
> FROM
> Sessions WHERE (SessionDate > '" + sDate + "') AND (SessionDate < '" +
> eDate
> + "') ORDER BY ProjectID";
> or isnull() - i get the following error...
> Server Error in '/dartsIntranetV2.1' Application.
> ----
--
> PMon_Total
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about
> the error and where it originated in the code.
> Exception Details: System.IndexOutOfRangeException: PMon_Total
> Source Error:
> An unhandled exception was generated during the execution of the current
> web
> request. Information regarding the origin and location of the exception
> can
> be identified using the exception stack trace below.
> The error message identifies line 223, which is effectively the last line
> in
> the loop...
> Response.Write(", " + prjDr["PMon_Total"].ToString() + "<br>");
> Any help much appreciated!
> Cheers
>
> Dan
> ----
> "David Portas" wrote:
>|||Fixed!
I had a look at the query code running from Web Data Administrator, and it
was passing the results of COALESCE as a new column, named Column 1.
I changed it to COALESCE(PMon_Total, 0) AS MonTotal, and pulled that from my
code, which works perfectly.
Thanks for everyones help!
"David Portas" wrote:

> This is a C# error so you may have more luck in a C# forum.
> Why aren't you using the parameters collection to pass your parameters?
> That at least would eliminate the possibility of a corrupt command
> string due to invalid input. It also avoids any SQL injection
> vulnerabilities. Example at:
> http://msdn.microsoft.com/library/d...
etersTopic.asp
> You should also consider putting the SQL in a proc since the query
> appears to be static anyway.
> --
> David Portas
> SQL Server MVP
> --
>

Clusters with "missing" values

Hello all and a happy new year!

I used Microsoft clustering for grouping my data. Even though i already cleaned the data and have no null values i get one cluster with missing values in every attribute. (i set CLUSTER_COUNT=3 and i'm using Scalable k-means algorithm)

Does "missing" mean that the algorithm cannot group that particular tuple in another group so it consider it as missing?

Thank you in advance.

"missing" is an implicit state of any attribute, and is considered whether or not there is missing data or not. For example, a "gender" attribute could have states "Male", "Female" and "missing". An "Age" attribute would have as possible states the age value, or "missing." When clusters are initialized the centroid of the cluster is initialized as a probability of that state being in that cluster, e.g. 45% Male, 50% female, and 5% missing. In general, the initial probabilities are determined by perturbing the distributions of the values shown in the data.

That being said, you say that you are getting a cluster with missing values in every attribute - what is the support of this cluster relative to all the others? It may be possible that you are really only finding two clusters and getting a cluster that is negligable. Could you post the results of SELECT FLATTENED * FROM <model name>.CONTENT?

|||

Thank you for you answer it was very helpful!

The support of these cluster is very small comparing to the other clusters (~0,01 -0,02%)
Can i consider those data as outliers and discard them?

|||Likely - you could just name it as such

Clusters with "missing" values

Hello all and a happy new year!

I used Microsoft clustering for grouping my data. Even though i already cleaned the data and have no null values i get one cluster with missing values in every attribute. (i set CLUSTER_COUNT=3 and i'm using Scalable k-means algorithm)

Does "missing" mean that the algorithm cannot group that particular tuple in another group so it consider it as missing?

Thank you in advance.

"missing" is an implicit state of any attribute, and is considered whether or not there is missing data or not. For example, a "gender" attribute could have states "Male", "Female" and "missing". An "Age" attribute would have as possible states the age value, or "missing." When clusters are initialized the centroid of the cluster is initialized as a probability of that state being in that cluster, e.g. 45% Male, 50% female, and 5% missing. In general, the initial probabilities are determined by perturbing the distributions of the values shown in the data.

That being said, you say that you are getting a cluster with missing values in every attribute - what is the support of this cluster relative to all the others? It may be possible that you are really only finding two clusters and getting a cluster that is negligable. Could you post the results of SELECT FLATTENED * FROM <model name>.CONTENT?

|||

Thank you for you answer it was very helpful!

The support of these cluster is very small comparing to the other clusters (~0,01 -0,02%)
Can i consider those data as outliers and discard them?

|||Likely - you could just name it as such