Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

DW Sort Order differs from SQL Server Sort Order

6 posts in DataWindow Last posting was on 2009-09-11 17:13:08.0Z
Linda Dudzic Posted on 2009-09-08 17:38:25.0Z
From: "Linda Dudzic" <NOSPAM_ldudzic@bibbero.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: DW Sort Order differs from SQL Server Sort Order
Lines: 34
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa69691$1@forums-1-dub>
Date: 8 Sep 2009 10:38:25 -0700
X-Trace: forums-1-dub 1252431505 10.22.241.152 (8 Sep 2009 10:38:25 -0700)
X-Original-Trace: 8 Sep 2009 10:38:25 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89923
Article PK: 419112

We have a table with a column sku_cd defined as char(20) not null. Three of
the sku_cd values are:
50-SN16LG-AL
50-SN16LG-ALNL
50-SN16LG2

Three rows are returned by the following query (you can add an ORDER BY
sku_cd):
select * from sku where sku_cd between '50-SN16LG-AL' and '50-SN16LG2'

No rows are returned by the following query:
select * from sku where sku_cd between '50-SN16LG2' and '50-SN16LG-AL'

So, this establishes the SQL Server sort/collation order, wherein the '-'
precedes the '2'. (We are using Code Page 1252).

Now, use the first SQL query as the basis for a DataWindow. The three rows
are returned. Sort the DW using the DW Sort. The order of the rows is
reversed:
50-SN16LG2
50-SN16LG-AL
50-SN16LG-ALNL

We are using PB 11.2 and SQL Server 2005. (I also tested this on PB 6.5 and
SQL Server 6.5. No difference in behavior.) The sort order used by the
DataWindow is the same as used by Excel/Word.

This is very confusing to the users! They have to know one sort order to
specify their query arguments, but the resulting data is displayed in a
different sort order.

Has anyone noticed this behavior? If so, what is a good solution?


fisher <fisher_NO Posted on 2009-09-08 18:47:06.0Z
From: fisher <fisher_NO@SPAM_star.wckp.lodz.pl_PLEASE>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: DW Sort Order differs from SQL Server Sort Order
References: <4aa69691$1@forums-1-dub>
In-Reply-To: <4aa69691$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090908-0, 09/08/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa6a6aa$1@forums-1-dub>
Date: 8 Sep 2009 11:47:06 -0700
X-Trace: forums-1-dub 1252435626 10.22.241.152 (8 Sep 2009 11:47:06 -0700)
X-Original-Trace: 8 Sep 2009 11:47:06 -0700, vip152.sybase.com
Lines: 40
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89924
Article PK: 419111

Hi Linda,
PB uses a lexical sort. It's a known behavior, look at newsgroup archive
Datawindow sorting on 2007-02-21

Regards

Linda Dudzic wrote:
> We have a table with a column sku_cd defined as char(20) not null. Three of
> the sku_cd values are:
> 50-SN16LG-AL
> 50-SN16LG-ALNL
> 50-SN16LG2
>
> Three rows are returned by the following query (you can add an ORDER BY
> sku_cd):
> select * from sku where sku_cd between '50-SN16LG-AL' and '50-SN16LG2'
>
> No rows are returned by the following query:
> select * from sku where sku_cd between '50-SN16LG2' and '50-SN16LG-AL'
>
> So, this establishes the SQL Server sort/collation order, wherein the '-'
> precedes the '2'. (We are using Code Page 1252).
>
> Now, use the first SQL query as the basis for a DataWindow. The three rows
> are returned. Sort the DW using the DW Sort. The order of the rows is
> reversed:
> 50-SN16LG2
> 50-SN16LG-AL
> 50-SN16LG-ALNL
>
> We are using PB 11.2 and SQL Server 2005. (I also tested this on PB 6.5 and
> SQL Server 6.5. No difference in behavior.) The sort order used by the
> DataWindow is the same as used by Excel/Word.
>
> This is very confusing to the users! They have to know one sort order to
> specify their query arguments, but the resulting data is displayed in a
> different sort order.
>
> Has anyone noticed this behavior? If so, what is a good solution?
>
>


Linda Dudzic Posted on 2009-09-08 18:57:27.0Z
From: "Linda Dudzic" <NOSPAM_ldudzic@bibbero.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4aa69691$1@forums-1-dub> <4aa6a6aa$1@forums-1-dub>
Subject: Re: DW Sort Order differs from SQL Server Sort Order
Lines: 56
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa6a917$1@forums-1-dub>
Date: 8 Sep 2009 11:57:27 -0700
X-Trace: forums-1-dub 1252436247 10.22.241.152 (8 Sep 2009 11:57:27 -0700)
X-Original-Trace: 8 Sep 2009 11:57:27 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89925
Article PK: 419113

Hi fisher,

Thanks. I recall that now. How do I access the newsgroup archive?

PS. I found that if I added "collate Latin1_General_CI_AI "
to the query, SQL Server returned the rows in the same order as the
DataWindow. Apparently this has to do with our SQL Server Collation setting
of "SQL_Latin1_General_CP1_CI_AS"

It's not a good workaround since there are hundreds of queries that would
have to be modified, but it was helpful to confirm that it is a SQL Server
configuration value that can be changed...although I would want to do a lot
of testing before changing the collation on an existing database.

"fisher" <fisher_NO@SPAM_star.wckp.lodz.pl_PLEASE> wrote in message
news:4aa6a6aa$1@forums-1-dub...
> Hi Linda,
> PB uses a lexical sort. It's a known behavior, look at newsgroup archive
> Datawindow sorting on 2007-02-21
>
> Regards
> Linda Dudzic wrote:
>> We have a table with a column sku_cd defined as char(20) not null. Three
>> of the sku_cd values are:
>> 50-SN16LG-AL
>> 50-SN16LG-ALNL
>> 50-SN16LG2
>>
>> Three rows are returned by the following query (you can add an ORDER BY
>> sku_cd):
>> select * from sku where sku_cd between '50-SN16LG-AL' and '50-SN16LG2'
>>
>> No rows are returned by the following query:
>> select * from sku where sku_cd between '50-SN16LG2' and '50-SN16LG-AL'
>>
>> So, this establishes the SQL Server sort/collation order, wherein the '-'
>> precedes the '2'. (We are using Code Page 1252).
>>
>> Now, use the first SQL query as the basis for a DataWindow. The three
>> rows are returned. Sort the DW using the DW Sort. The order of the rows
>> is reversed:
>> 50-SN16LG2
>> 50-SN16LG-AL
>> 50-SN16LG-ALNL
>>
>> We are using PB 11.2 and SQL Server 2005. (I also tested this on PB 6.5
>> and SQL Server 6.5. No difference in behavior.) The sort order used by
>> the DataWindow is the same as used by Excel/Word.
>>
>> This is very confusing to the users! They have to know one sort order to
>> specify their query arguments, but the resulting data is displayed in a
>> different sort order.
>>
>> Has anyone noticed this behavior? If so, what is a good solution?
>>


fisher <fisher_NO Posted on 2009-09-08 19:27:16.0Z
From: fisher <fisher_NO@SPAM_star.wckp.lodz.pl_PLEASE>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: DW Sort Order differs from SQL Server Sort Order
References: <4aa69691$1@forums-1-dub> <4aa6a6aa$1@forums-1-dub> <4aa6a917$1@forums-1-dub>
In-Reply-To: <4aa6a917$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090908-0, 09/08/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa6b014$1@forums-1-dub>
Date: 8 Sep 2009 12:27:16 -0700
X-Trace: forums-1-dub 1252438036 10.22.241.152 (8 Sep 2009 12:27:16 -0700)
X-Original-Trace: 8 Sep 2009 12:27:16 -0700, vip152.sybase.com
Lines: 65
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89926
Article PK: 419114

Google search used to work better but recently something is not right
anymore.
http://groups.google.com/group/sybase.public.powerbuilder.datawindow/browse_thread/thread/0854a9d58e2ba3d0?hl=en#

I'm sorry I've not much SQL Server experience I just found this
http://msdn.microsoft.com/en-us/library/bb330962%28SQL.90%29.aspx#intlftrql2005_topic6

Linda Dudzic wrote:
> Hi fisher,
>
> Thanks. I recall that now. How do I access the newsgroup archive?
>
> PS. I found that if I added "collate Latin1_General_CI_AI "
> to the query, SQL Server returned the rows in the same order as the
> DataWindow. Apparently this has to do with our SQL Server Collation setting
> of "SQL_Latin1_General_CP1_CI_AS"
>
> It's not a good workaround since there are hundreds of queries that would
> have to be modified, but it was helpful to confirm that it is a SQL Server
> configuration value that can be changed...although I would want to do a lot
> of testing before changing the collation on an existing database.
>
> "fisher" <fisher_NO@SPAM_star.wckp.lodz.pl_PLEASE> wrote in message
> news:4aa6a6aa$1@forums-1-dub...
>> Hi Linda,
>> PB uses a lexical sort. It's a known behavior, look at newsgroup archive
>> Datawindow sorting on 2007-02-21
>>
>> Regards
>> Linda Dudzic wrote:
>>> We have a table with a column sku_cd defined as char(20) not null. Three
>>> of the sku_cd values are:
>>> 50-SN16LG-AL
>>> 50-SN16LG-ALNL
>>> 50-SN16LG2
>>>
>>> Three rows are returned by the following query (you can add an ORDER BY
>>> sku_cd):
>>> select * from sku where sku_cd between '50-SN16LG-AL' and '50-SN16LG2'
>>>
>>> No rows are returned by the following query:
>>> select * from sku where sku_cd between '50-SN16LG2' and '50-SN16LG-AL'
>>>
>>> So, this establishes the SQL Server sort/collation order, wherein the '-'
>>> precedes the '2'. (We are using Code Page 1252).
>>>
>>> Now, use the first SQL query as the basis for a DataWindow. The three
>>> rows are returned. Sort the DW using the DW Sort. The order of the rows
>>> is reversed:
>>> 50-SN16LG2
>>> 50-SN16LG-AL
>>> 50-SN16LG-ALNL
>>>
>>> We are using PB 11.2 and SQL Server 2005. (I also tested this on PB 6.5
>>> and SQL Server 6.5. No difference in behavior.) The sort order used by
>>> the DataWindow is the same as used by Excel/Word.
>>>
>>> This is very confusing to the users! They have to know one sort order to
>>> specify their query arguments, but the resulting data is displayed in a
>>> different sort order.
>>>
>>> Has anyone noticed this behavior? If so, what is a good solution?
>>>
>


Scott Morris Posted on 2009-09-08 19:48:21.0Z
From: "Scott Morris" <bogus@bogus.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4aa69691$1@forums-1-dub> <4aa6a6aa$1@forums-1-dub> <4aa6a917$1@forums-1-dub>
Subject: Re: DW Sort Order differs from SQL Server Sort Order
Lines: 49
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa6b505$1@forums-1-dub>
Date: 8 Sep 2009 12:48:21 -0700
X-Trace: forums-1-dub 1252439301 10.22.241.152 (8 Sep 2009 12:48:21 -0700)
X-Original-Trace: 8 Sep 2009 12:48:21 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89927
Article PK: 419116


"Linda Dudzic" <NOSPAM_ldudzic@bibbero.com> wrote in message
news:4aa6a917$1@forums-1-dub...
> Hi fisher,
>
> Thanks. I recall that now. How do I access the newsgroup archive?
>
> PS. I found that if I added "collate Latin1_General_CI_AI "
> to the query, SQL Server returned the rows in the same order as the
> DataWindow. Apparently this has to do with our SQL Server Collation
> setting of "SQL_Latin1_General_CP1_CI_AS"
>
> It's not a good workaround since there are hundreds of queries that would
> have to be modified, but it was helpful to confirm that it is a SQL Server
> configuration value that can be changed...although I would want to do a
> lot of testing before changing the collation on an existing database.

Now is a goodl time to review the sql server documentation on collations.
Collations are set at the column level - the database setting is a default
for cases when the column is created without a collation specified. In
addition, you can force any particular query to use a collation that is
different from the one specified for a column. Example below:


if object_id('tempdb..#test') is not null
drop table #test;
go

create table #test (
id integer identity(1,1)
, descr1 varchar(20) collate SQL_Latin1_General_CP1_CI_AS not null
);

insert #test (descr1)
select '50-SN16LG-AL' union all
select '50-SN16LG-ALNL' union all
select '50-SN16LG2'

select * from #test order by descr1
select * from #test order by descr1 collate Latin1_General_CS_AI
select * from #test order by descr1 collate Latin1_General_CI_AI

select * from #test where descr1 between '50-SN16LG2' and '50-SN16LG-AL'
select * from #test where descr1 collate Latin1_General_CS_AI between
'50-SN16LG2' and '50-SN16LG-AL'

select * from fn_helpcollations () where description like 'latin%' order by
description


Linda Dudzic Posted on 2009-09-11 17:13:08.0Z
From: "Linda Dudzic" <NOSPAM_ldudzic@bibbero.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4aa69691$1@forums-1-dub> <4aa6a6aa$1@forums-1-dub> <4aa6a917$1@forums-1-dub> <4aa6b505$1@forums-1-dub>
Subject: Re: DW Sort Order differs from SQL Server Sort Order
Lines: 67
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aaa8524@forums-1-dub>
Date: 11 Sep 2009 10:13:08 -0700
X-Trace: forums-1-dub 1252689188 10.22.241.152 (11 Sep 2009 10:13:08 -0700)
X-Original-Trace: 11 Sep 2009 10:13:08 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89943
Article PK: 419130

Scott,

Thanks so much for the examples!

I had figured out how to add the "collate" clause to the ORDER BY, but I
didn't realize I could add a COLLATE clause to the WHERE clause. They go
hand-in-hand when querying by the column where the sort/collation is a
problem.

(Thanks also to fisher for the link to the archives.)

Linda

"Scott Morris" <bogus@bogus.com> wrote in message
news:4aa6b505$1@forums-1-dub...
> "Linda Dudzic" <NOSPAM_ldudzic@bibbero.com> wrote in message
> news:4aa6a917$1@forums-1-dub...
>> Hi fisher,
>>
>> Thanks. I recall that now. How do I access the newsgroup archive?
>>
>> PS. I found that if I added "collate Latin1_General_CI_AI "
>> to the query, SQL Server returned the rows in the same order as the
>> DataWindow. Apparently this has to do with our SQL Server Collation
>> setting of "SQL_Latin1_General_CP1_CI_AS"
>>
>> It's not a good workaround since there are hundreds of queries that would
>> have to be modified, but it was helpful to confirm that it is a SQL
>> Server configuration value that can be changed...although I would want to
>> do a lot of testing before changing the collation on an existing
>> database.
>
> Now is a goodl time to review the sql server documentation on collations.
> Collations are set at the column level - the database setting is a default
> for cases when the column is created without a collation specified. In
> addition, you can force any particular query to use a collation that is
> different from the one specified for a column. Example below:
>
>
> if object_id('tempdb..#test') is not null
> drop table #test;
> go
>
> create table #test (
> id integer identity(1,1)
> , descr1 varchar(20) collate SQL_Latin1_General_CP1_CI_AS not null
> );
>
> insert #test (descr1)
> select '50-SN16LG-AL' union all
> select '50-SN16LG-ALNL' union all
> select '50-SN16LG2'
>
> select * from #test order by descr1
> select * from #test order by descr1 collate Latin1_General_CS_AI
> select * from #test order by descr1 collate Latin1_General_CI_AI
>
> select * from #test where descr1 between '50-SN16LG2' and '50-SN16LG-AL'
> select * from #test where descr1 collate Latin1_General_CS_AI between
> '50-SN16LG2' and '50-SN16LG-AL'
>
> select * from fn_helpcollations () where description like 'latin%' order
> by description
>
>