nvarchar and different collation comparison

Hi,
I have read some articles on Unicode characters, I understand that they use
as a "big international code page" that host thousands of characters. The
thing I don't understand is why I get an error when I compare two nvarchar
column having a different collation... as this:
I know that COLLATE in the comparison would correct the issue, but I want to
find a way to avoid hard coding COLLATE keywords and avoid to change both
collations of the fields. I though nvarchar was to resolve this. Someone can
explain why it doesn't do that?
Or please leave me a personnal mail or web link... Thanks
CREATE TABLE a
(
LatinAS nvarchar(50) COLLATE Latin1_General_CI_AS,
LatinAI nvarchar(50) COLLATE Latin1_General_CI_AI
)
GO
INSERT INTO a (LatinAS, LatinAI) VALUES ('Eric', 'Eric')
SELECT * FROM a WHERE LatinAS = LatinAI
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
david.parenteau@.compuware.com
TIANVARCHAR allows you to use Unicode characters. There are still differences
in character sets when you move from collation to collation. So, your
options here are pretty limited.
"David Parenteau" <DavidParenteau@.discussions.microsoft.com> wrote in
message news:5031A14F-4D36-47B8-A07F-3ABB5BF019D2@.microsoft.com...
> Hi,
> I have read some articles on Unicode characters, I understand that they
> use
> as a "big international code page" that host thousands of characters. The
> thing I don't understand is why I get an error when I compare two nvarchar
> column having a different collation... as this:
> I know that COLLATE in the comparison would correct the issue, but I want
> to
> find a way to avoid hard coding COLLATE keywords and avoid to change both
> collations of the fields. I though nvarchar was to resolve this. Someone
> can
> explain why it doesn't do that?
> Or please leave me a personnal mail or web link... Thanks
>
> CREATE TABLE a
> (
> LatinAS nvarchar(50) COLLATE Latin1_General_CI_AS,
> LatinAI nvarchar(50) COLLATE Latin1_General_CI_AI
> )
> GO
> INSERT INTO a (LatinAS, LatinAI) VALUES ('Eric', 'Eric')
> SELECT * FROM a WHERE LatinAS = LatinAI
>
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
>
> david.parenteau@.compuware.com
> TIA|||David
CREATE TABLE b
(
LatinAS nvarchar(50) COLLATE Latin1_General_CI_AS,
LatinAI nvarchar(50) COLLATE Latin1_General_CI_AI
)
GO
INSERT INTO b (LatinAS, LatinAI) VALUES ('Eric', 'Eric')
SELECT * FROM b WHERE LatinAS COLLATE Latin1_General_CI_AI = LatinAI
"David Parenteau" <DavidParenteau@.discussions.microsoft.com> wrote in
message news:5031A14F-4D36-47B8-A07F-3ABB5BF019D2@.microsoft.com...
> Hi,
> I have read some articles on Unicode characters, I understand that they
> use
> as a "big international code page" that host thousands of characters. The
> thing I don't understand is why I get an error when I compare two nvarchar
> column having a different collation... as this:
> I know that COLLATE in the comparison would correct the issue, but I want
> to
> find a way to avoid hard coding COLLATE keywords and avoid to change both
> collations of the fields. I though nvarchar was to resolve this. Someone
> can
> explain why it doesn't do that?
> Or please leave me a personnal mail or web link... Thanks
>
> CREATE TABLE a
> (
> LatinAS nvarchar(50) COLLATE Latin1_General_CI_AS,
> LatinAI nvarchar(50) COLLATE Latin1_General_CI_AI
> )
> GO
> INSERT INTO a (LatinAS, LatinAI) VALUES ('Eric', 'Eric')
> SELECT * FROM a WHERE LatinAS = LatinAI
>
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
>
> david.parenteau@.compuware.com
> TIA|||If nvarchar keep the same characters bytes for every characters in a
collation A, does the same character in collation B will be the same bytes
values?
Collation A
Characters A correspond to 265 for example.
In Collation B, does the character A will have the same bits and be 265 or
it could be something else?
Does the sort order and other things (except code page) in collations differ
also for nvarchar columns?
David
"Aaron Bertrand [SQL Server MVP]" wrote:

> NVARCHAR allows you to use Unicode characters. There are still difference
s
> in character sets when you move from collation to collation. So, your
> options here are pretty limited.
>
>
> "David Parenteau" <DavidParenteau@.discussions.microsoft.com> wrote in
> message news:5031A14F-4D36-47B8-A07F-3ABB5BF019D2@.microsoft.com...
>
>

Numer comparison

Hi,

I have table with four fields ID, Date1, Date2,Date3,Date4. Dates are in numeric farmat like 20070927. How can i get largest date (from date1 to date4) on each row. Please suggest query.

Thanks

One posible solution is to unpivot those columns and then use "group by".

Code Block

declare @.t table (

[id] int not null identity unique,

date1 int,

date2 int,

date3 int,

date4 int

)

insert into @.t(date1, date2, date3, date4) values(20070305, 20070401, 20070928, 20070704)

insert into @.t(date1, date2, date3, date4) values(20070315, 20070411, 20070228, 20070714)

select

[id],

max(dt) max_dt

from

@.t as t

unpivot

(

dt

for date_col in ([date1], [date2], [date3], [date4])

) as unpvt

group by

[id]

go

AMB|||

Bukhari wrote:

Hi,

I have table with four fields ID, Date1, Date2,Date3,Date4. Dates are in numeric farmat like 20070927. How can i get largest date (from date1 to date4) on each row. Please suggest query.

Thanks

A function like Oracle's GREATEST would come in handy here

What about

Code Block

CREATE TABLE tableA
( id INT IDENTITY PRIMARY KEY
, date1 INT
, date2 INT
, date3 INT
, date4 INT)
INSERT INTO tableA SELECT 20070927, 20070928, 20070926, 20080927
INSERT INTO tableA SELECT 20070927, 20071028, 20070926, 20060927

SELECT id, MAX(iDate) AS maxdate
FROM
(SELECT id,
CASE i
WHEN 1 THEN date1
WHEN 2 THEN date2
WHEN 3 THEN date3
WHEN 4 THEN date4
END AS iDate
FROM tableA
CROSS JOIN
(SELECT 1 AS i
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4) T) T
GROUP BY id
SELECT id,
CASE
WHEN date1 > date2 AND date1 > date3 AND date1 > date4 THEN date1
WHEN date2 > date3 AND date2 > date4 THEN date2
WHEN date3 > date4 THEN date3
ELSE date4 END AS maxdate
FROM tableA
DROP TABLE tableA

id maxdate
-- --
1 20080927
2 20071028

(2 row(s) affected)

id maxdate
-- --
1 20080927
2 20071028

(2 row(s) affected)

|||

First off, I just have to note that this is not really a relational design. Each of these dates should be stored in it's own row, and this is an easy SQL problem.

Second, the best relational solution I have found uses a subquery with a UNION ALL operator to "normalize" the date values for an aggregate:

create table dates
(
dateKey int primary key ,
date1 int,
date2 int,
date3 int,
date4 int
)

insert into dates
select 1, 20070101,20070102,20070103,20070104
union
select 2, 20070201,20070202,20070203,20070104
go

select dateKey,
(select min(date)
from (select date1 as date union all select date2 union all
select date3 union all select date4) as dates) as minDate
from dates

It is really quite fast, but if you need more performance, consider using a CLR function to do this. I have found that this technique is about the same speed with you have a few dates, or less than millions of rows to deal with (and it is certainly easier to code and deploy...)

|||

Excilent solution. Thanks very much hunchback and frank.

I will go with CASE--WHEN solution.

|||I was really struggling with it. Thanks a lot Louis.|||

I just want to mention that what the solution using "case" is doing is unpivoting. Well, all posted solutions are unpivoting some how.

AMB

|||

Can i specify criteria in Where clause? Where maxdate less than last 60 days.

Where maxdate < CONVERT(VARCHAR, DATEADD(day, - 60, GETDATE()), 112)

but maxdate is not column so it generate error, how can i use CASE WHEN statement in WHERE clause?

thanks

|||

Yes, but you need to use a derived table:

select *
from ( --your original query goes here
select dateKey,
(select min(date)
from (select date1 as date union all select date2 union all
select date3 union all select date4) as dates) as minDate
from dates) as dateQuery
where minDate > 20070101

or CTE if you are using 2005:

with dateQuery as (

select dateKey,
(select min(date)
from (select date1 as date union all select date2 union all
select date3 union all select date4) as dates) as minDate
from dates )

select *
from dateQuery
where minDate > 20070101

Or you can just repeat the subquery:

select dateKey,
(select min(date)
from (select date1 as date union all select date2 union all
select date3 union all select date4) as dates) as minDate
from dates
where (select min(date)
from (select date1 as date union all select date2 union all
select date3 union all select date4) as dates) > 20070101

But that is the least astetically pleasing...

|||

Add a "having" clause to the solution I suggested.

Code Block

declare @.t table (

[id] int not null identity unique,

date1 int,

date2 int,

date3 int,

date4 int

)

insert into @.t(date1, date2, date3, date4) values(20070305, 20070401, 20070928, 20070704)

insert into @.t(date1, date2, date3, date4) values(20070315, 20070411, 20070228, 20070114)

select

[id],

max(dt) max_dt

from

@.t as t

unpivot

(

dt

for date_col in ([date1], [date2], [date3], [date4])

) as unpvt

group by

[id]

having

max(dt) < CONVERT(CHAR(8), DATEADD(day, - 60, GETDATE()), 112)

go

AMB|||

Great Stuff, Thanks

|||

Hunchback, nice stuff. I would say that both yours and mine have merits, mine is a bit more clean in one way (I am doing a rowwise operation, so I could include most any kind of operation in mine, whereas yours includes an aggregate that would be limiting, but definitely does what was called for, and if you were doing multiple operations only on the date data, might be a lot faster.

|||

Hi Louis,

Well, I would prefer to follow what you mentioned (normalizing) in your first post to this thread.

Thanks,

Alejandro Mesa

P.S. I am the guy who asked you to sign my book (I was staying at the "Red Lion") during this year summit. I hope to see you again, to ask you to sign also the one about DMVs & DMFs.

|||

>>Well, I would prefer to follow what you mentioned (normalizing) in your first post to this thread.<<

Amen to that, but I have had to do this sort of operation for other reasons. In our etl, we join a bunch of tables together and then get the earliest update date from all of the joined rows so we can include/exclude rows for ETL.

>>also the one about DMVs & DMFs.<<

I definitely remember. I just hope I ever finish that book

|||
Just to throw another approach into the mix.

create function Util.fn_max( @.p1 int, @.p2 int)
returns int
as
begin
return (select case when @.p1>=@.p2 then @.p1 else @.p2 end as int)
end

Select Util.fn_max(Util.fn_max(Util.fn_max(date1, date2), date3), date4)

Change util to the schema that you want, aka dbo. I keep things like this in a Util schema.