Re: Sorting characters with accents

I certainly don't disagree that having QCCSID set to 65535 is a problem, but I don't believe you will find it's a problem in this specific case.
National language sorting is controlled primarily through language ID (LANGID) and sort sequence (SRTSEQ). These are both job attributes in addition to system values. And in the case of interactive SQL, also session attributes. Proper CCSID tagging is certainly critical to ensuring character data integrity is maintained, but doesn't directly come into play with sorting preferences.

Bruce
Bruce Vining Services
507-206-4178

--- On Thu, 1/8/09, Vern Hamberg <vhamberg@xxxxxxxxxxx> wrote:

From: Vern Hamberg <vhamberg@xxxxxxxxxxx>
Subject: Re: Sorting characters with accents
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date: Thursday, January 8, 2009, 9:12 AM

David

The 65535 is your first problem - it should be changed immediately to the CCSID for your national language version. I almost think that if you changed this, your sort might already be fixed.

Second, Bruce was talking about LANGID - try DSPSYSVAL QLANGID to see what it is.

Vern

David FOXWELL wrote:

Bruce,

That order would be perfect.

But it's not changing anything for me. DSPSYSVAL QCCSID gives 65535.

-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx

[mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Bruce Vining

Envoyé : jeudi 8 janvier 2009 15:28
À : Midrange Systems Technical Discussion
Objet : RE: Sorting characters with accents

Set your LANGID to FRA (to match I assume your CCSID of 297 given your

codepoints) and sort sequence to *LANGIDUNQ. This will approximate the order
you have in your list. I say approximate due to ê collating after é and è in
my quick test. You could create your own table to sort in your desired order
though (or find a more appropriate LANGID).

Bruce Bruce Vining Services 507-206-4178

--- On Thu, 1/8/09, David FOXWELL <David.FOXWELL@xxxxxxxxx> wrote:

From: David FOXWELL <David.FOXWELL@xxxxxxxxx>
Subject: RE: Sorting characters with accents
To: "Midrange Systems Technical Discussion"

<midrange-l@xxxxxxxxxxxx>

Date: Thursday, January 8, 2009, 8:11 AM

Have I a chance in hell of getting this order : ?

a 81
à 7C
c 83
ç E0
e 85
ê 52
é C0
è D0

-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx

[mailto:midrange-l-bounces@xxxxxxxxxxxx]

De la part de Vern Hamberg
Envoyé : jeudi 8 janvier 2009 14:49
À : Midrange Systems Technical Discussion Objet : Re: Sorting characters

with accents

Here's what I see -

The first sort is done according to the actual hex value of the character

in AZ

- notice that it is in order by the hex numeric value - numbers come

before letters - the order is 0123456789ABCDEF.

Now the second sort is done according to the character representation of

the hex value - so letters come before numbers, in the normal EBCDIC order.
Letters before numbers.

In embedded SQL you can set the sort sequence - set option srtseq=*JOB or

*HEX or *JOB or *JOBRUN or *LANGIDSHR or *LANGIDUNQ - you can even name a sort
table.

This is an option you set at the top of the source code and applies to

everything in a single code unit - so if you need both case-sensitive and
case-insensitive options, you have to do it with

2 modules - a great case for ILE!!

Eh?
Vern

David FOXWELL wrote:

Hi,

With this request : SELECT AZ , hex( AZ ) FROM az ORDER BY AZ

I'm getting
AZ HEX ( AZ )
ê 52
à 7C
a 81
c 83
e 85
é C0
è D0
ç E0

If I use ORDER BY hex(AZ)
I get
AZ HEX ( AZ )
é C0
è D0
ç E0
ê 52
à 7C
a 81
c 83
e 85

Can anyone tell me how the sorting works?
Thanks

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,

visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a

moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,

visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a

moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,

visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a

moment to review the archives at http://archive.midrange.com/midrange-l.