照合

照合は、値を並べる方法と比較する方法を指定するもので、Caché SQL と Caché オブジェクトのどちらにも欠かせない要素です。基本的な照合には、数値と文字列の 2 つがあります。

数値照合では、完全な数値に基づいて数値を並べ替えます。この並べ替えは、NULL、負の数値 (最大から最小)、ゼロ、正の数値 (最小から最大) の順に行われます。これにより、–210、–185、–54、–34、-.02、0、1、2、10、17、100、120 のように順序が生成されます。
文字列照合は、一連の文字それぞれを照合することで文字列を並べ替えます。これにより、A、AA、AAA、AAB、AB、B のように順序が生成されます。数値の場合は、–.02、–185、–210、–34、–54、0、1、10、100、120、17、2 のように順序が生成されます。

既定の文字列照合は、SQLUPPER です。この既定値は、ネームスペースごとに設定されます。SQLUPPER 照合では、すべての文字が照合のために大文字に変換され、文字列の先頭に空白文字が追加されます。この変換は、照合のみを目的としています。Caché SQL の文字列は、どの照合が適用されていても、通常は大文字と小文字で表示されます。また、文字列の長さには追加された空白文字は含まれません。

タイムスタンプは文字列であるため、現在の文字列照合に従います。ただし、タイムスタンプは ODBC 形式であるため、文字列照合は時間的な順序と一致します (先頭のゼロが指定されている場合)。

文字列式 (LEFT または SUBSTR スカラ文字列関数を使用する式など) では、結果の照合は EXACT になります。
2 つのリテラルの比較には、EXACT 照合を使用します。

ObjectScript の前後関係演算子を使用すると、2 つの値の相対的な照合順を判断できます。

照合は以下のように指定できます。

この章で後述する “SQL 照合と NLS 照合” も参照してください。

照合タイプ

照合は、フィールド/プロパティの定義またはインデックスの定義でキーワードとして指定できます。

照合は、クエリの節でフィールド名に照合関数を適用することで指定できます。照合関数を指定するときには、接頭語の % が必要になります。

照合は、以下の変換を使用して、ASCII または Unicode の昇順で実行されます。

EXACT — 文字列データに大文字と小文字の区別を強制適用します。文字列データにキャノニック数値形式の値 (123 や -.57 など) が含まれている場合の使用はお勧めできません。
MVR — (MultiValue データベース・システムとの互換性を保つためのもの)。数値文字と非数値文字の両方が含まれる文字列が対象です。MVR 照合では、文字列を部分文字列に分割します。各部分文字列には、すべての数値文字またはすべての非数値文字のいずれかが含まれます。数値部分文字列は、符号付き数値順に並べ替えられます。非数値部分文字列は、大文字と小文字が区別される ASCII 照合順に並べ替えられます (この照合は、スタジオの新規インデックス・ウィザードには表示されません)。
SQLSTRING — 末尾の空白 (スペースやタブなど) を削除して、文字列の先頭に空白を 1 つ追加します。空白 (スペース、タブなど) だけを含む任意の値を SQL 空文字列として照合します。SQLSTRING は、オプションの maxlen 整数値をサポートしています。
SQLUPPER — すべてのアルファベット文字を大文字に変換し、末尾の空白 (スペースやタブなど) を削除して、文字列の先頭にスペース文字を 1 つ追加します。このスペース文字が追加される理由は、強制的に数値を文字列として照合するためです (スペース文字は有効な数値文字ではないため)。この変換により、SQL 空文字列 ('') の値と空白 (スペースやタブなど) のみを含む値が単一のスペース文字として照合されるようにもなります。SQLUPPER は、オプションの maxlen 整数値をサポートしています。SQLUPPER 変換は、SQL の UPPER 関数の結果とは異なります。
TRUNCATE — 文字列データの大文字と小文字の区別を強制しますが、EXACT とは異なり、値を切り捨てる位置を指定できます。これは、正確なデータにインデックスを作成する際に、そのデータが添え字の最大長を超えている場合に役立ちます。%TRUNCATE(string,n) の形式で引数に正の整数を指定すると、文字列は最初の n 文字で切り捨てられます。これにより、長い文字列のインデックスの作成と並べ替えの効率が向上します。TRUNCATE に長さを指定しない場合、EXACT と同じように動作します。この動作はサポートされていますが、TRUNCATE は長さを定義している場合にのみ使用し、長さを定義していない場合は EXACT を使用することによって、定義やコードの保守がより簡単になります。
PLUS — 値を数値にします。非数値文字列値は 0 として返されます。
MINUS — 値を数値にし、その符号を変更します。非数値文字列値は 0 として返されます。

Note:

また、さまざまな従来の照合タイプもありますが、それらは使用しないことをお勧めします。

SQL クエリでは、括弧なしの %SQLUPPER Name または括弧付きの %SQLUPPER(Name) で照合関数を指定できます。照合関数で切り捨てを指定する場合には括弧が必要です (%SQLUPPER(Name,10))。

3 つの照合タイプ SQLSTRING、SQLUPPER、および TRUNCATE は、オプションの maxlen 整数値をサポートしています。maxlen を指定すると、文字列の解析は最初の n までになります。これは、長い文字列のインデックス付けや並べ替えの際に、パフォーマンスを向上させるために使用できます。maxlen は、切り詰めた文字列値を並べ替え、グループ化、または返すクエリで使用できます。

%SYSTEM.Util.Collation()Opens in a new tab メソッドを使用して照合タイプの変換を実行することもできます。

ネームスペース全体の既定の照合

各ネームスペースには、現行の文字列照合が設定されています。この文字列照合は、%Library.StringOpens in a new tab のデータ型に対して定義されています。既定値は SQLUPPER です。この既定値は変更可能です。

ネームスペースごとに照合の既定を定義できます。既定では、ネームスペースには照合が割り当てられません。これは SQLUPPER 照合を使用することを意味します。既定の照合をネームスペースに個別に割り当てられます。このネームスペースの既定の照合は、すべてのプロセスに適用され、明示的に再設定するまで Caché を再起動しても維持されます。

   SET stat=$$GetEnvironment^%apiOBJ("collation","%Library.String",.collval)
   WRITE "initial collation for ",$NAMESPACE,!
     ZWRITE collval
SetNamespaceCollation
  DO SetEnvironment^%apiOBJ("collation","%Library.String","SQLstring")
  SET stat=$$GetEnvironment^%apiOBJ("collation","%Library.String",.collnew)
    WRITE "user-assigned collation for ",$NAMESPACE,!
     ZWRITE collnew
ResetCollationDefault
  DO SetEnvironment^%apiOBJ("collation","%Library.String",.collval)
  SET stat=$$GetEnvironment^%apiOBJ("collation","%Library.String",.collreset)
     WRITE "restored collation default for ",$NAMESPACE,!
     ZWRITE collreset

ネームスペースの照合の既定を設定しないと、$$GetEnvironment は、この例の .collval のように未定義の照合変数を返します。この未定義の照合は、既定で SQLUPPER に設定されます。

Note:

データにドイツ語テキストが含まれている場合は、大文字の照合が、希望する既定どおりにならない可能性があります。これは、ドイツ語の eszett 文字 ($CHAR(223)) に小文字形式しかないためです。同意義の大文字は、“SS” という 2 文字です。大文字に変換する SQL 照合では、eszett は変換されず、変更されないまま単一の小文字として残ります。

テーブルのフィールド/プロパティ定義の照合

SQL 内に、フィールド/プロパティ定義の一部として照合を割り当てることができます。フィールドに使用するデータ型により、そのフィールドの既定の照合が決まります。文字列データ型の既定の照合は SQLUPPER です。文字列以外のデータ型は、照合の割り当てをサポートしていません。

フィールドの照合は、CREATE TABLE および ALTER TABLE で指定できます。

CREATE TABLE Sample.MyNames (
    LastName CHAR(30),
    FirstName CHAR(30) COLLATE SQLstring)

Note:

CREATE TABLE および ALTER TABLE を使用してフィールドの照合を指定するときには、接頭語の % はオプションになります (COLLATE SQLstring または COLLATE %SQLstring)。

プロパティの照合は、永続クラス定義を使用してテーブルを定義する際に指定できます。

Class Sample.MyNames Extends %Persistent [DdlAllowed]
{
Property LastName As %String;
Property FirstName As %String(COLLATION = "SQLstring");
}

Note:

クラス定義およびクラス・メソッドに照合を指定するときには、照合タイプ名に接頭語の % を使用しないでください。

ここに示す各例では、LastName フィールドは既定の照合 (大文字と小文字を区別しない SQLUPPER) を採用し、FirstName フィールドは大文字と小文字を区別する SQLSTRING で定義されています。

クラスのプロパティに対する照合を変更する場合に、そのクラスのデータが既に保存されているときは、プロパティのインデックスが無効になります。このプロパティに基づいてすべてのインデックスを再構築する必要があります。

インデックス定義の照合

CREATE INDEX コマンドでインデックスの照合を指定することはできません。インデックスには、インデックス作成対象のフィールドと同じ照合が使用されます。

クラス定義の一部として定義されたインデックスには、照合タイプを指定できます。既定では、特定のプロパティに対するインデックスは、プロパティ・データの照合タイプを使用します。例えば、%StringOpens in a new tab タイプの Name プロパティを定義したとします。

Class MyApp.Person Extends %Persistent [DdlAllowed]
{
Property Name As %String;
Index NameIDX On Name;
}

Name の照合は、SQLUPPERです (%StringOpens in a new tab の既定)。Person テーブルに、以下のデータが含まれているとします。

ID	Name
1	Jones
2	JOHNSON
3	Smith
4	jones
5	SMITH

Name のインデックスは、以下のエントリを持つことになります。

Name	ID(s)
JOHNSON	2
JONES	1、4
SMITH	3、5

SQL エンジンは、このインデックスを直接使用して、Name フィールドを使用した ORDER BY や比較演算を実行します。

インデックスに使用される既定の照合は、インデックス定義に As 節を使用することでオーバーライドできます。

Class MyApp.Person Extends %Persistent [DdlAllowed]
{
Property Name As %String;
Index NameIDX On Name As SQLstring;
}

この場合、NameIDX インデックスは、SQLSTRING (大文字と小文字を区別する) 形式で値を保存することになります。上記の例のデータを使用すると、以下のようになります。

Name	ID(s)
JOHNSON	2
Jones	1
jones	4
SMITH	5
Smith	3

この場合、SQL エンジンは、大文字と小文字を区別する照合が必要になるあらゆるクエリに、このインデックスを利用できます。

通常、インデックスの照合は変更する必要はありません。別の照合を使用する場合は、その照合をプロパティ・レベルで定義して、そのプロパティに対するインデックスが適切な照合を採用できるようにします。

インデックスが作成されているプロパティを使用してプロパティの比較を実行する場合、その比較に指定するプロパティは、対応するインデックスと同じ照合タイプにする必要があります。例えば、SELECT の WHERE 節または JOIN の ON 節での Name プロパティは、その Name プロパティに定義されたインデックスと照合を同じにする必要があります。プロパティの照合とインデックスの照合に不一致があると、インデックスの効果が低下するか、インデックスがまったく使用されなくなります。詳細は、"Caché SQL 最適化ガイド" の “インデックスの定義と構築” の章の "インデックス照合" を参照してください。

使用しているインデックスが複数のプロパティを使用するために定義されている場合は、個別に照合を指定できます。

Index MyIDX On (Name As SQLstring, Code As Exact);

クエリの照合

Caché SQL には、フィールドの照合または表示の変更に使用できる照合関数があります。

select-item 照合

クエリの select-item に照合関数を適用することで、その項目の表示を変更します。

大文字と小文字：既定では、クエリは大文字と小文字で文字列を表示します。ただし、照合タイプが SQLUPPER のフィールドに対する DISTINCT 演算または GROUP BY 演算は例外です。これらの演算子は、そのフィールドをすべて大文字で表示します。%EXACT 照合関数を使用すると、この大文字と小文字の変換を逆にして、フィールドを大文字と小文字で表示できます。すべて大文字でフィールドを表示するために、 select-item リストで %SQLUPPER 照合関数を使用しないでください。これは、%SQLUPPER は文字列の長さに空白文字を追加するためです。その代わりに、UPPER 関数を使用してください。
```
SELECT TOP 5 Name,$LENGTH(Name) AS NLen,
             %SQLUPPER(Name) AS UpCollN,$LENGTH(%SQLUPPER(Name)) AS UpCollLen,
             UPPER(Name) AS UpN,$LENGTH(UPPER(Name)) AS UpLen
FROM Sample.Person
```
文字列の切り捨て： %TRUNCATE 照合関数を使用すると、表示する文字列データの長さを制限できます。%TRUNCATE は、文字列の長さに空白文字を追加する %SQLUPPER よりもお勧めです。
```
SELECT TOP 5 Name,$LENGTH(Name) AS NLen,
             %TRUNCATE(Name,8) AS TruncN,$LENGTH(%TRUNCATE(Name,8)) AS TruncLen
FROM Sample.Person
```
照合関数や大文字小文字の変換関数は、入れ子にできない点に注意してください。
WHERE 節の比較：ほとんどの WHERE 節の述語条件の比較には、フィールド/プロパティの照合タイプが使用されます。文字列フィールドは既定で SQLUPPER に設定されているため、一般に、こうした比較では大文字と小文字が区別されません。大文字と小文字を区別するには、%EXACT 照合関数を使用します。
以下の例では、大文字と小文字に関係なく文字列 Home_City との一致が返されます。
```
SELECT Home_City FROM Sample.Person WHERE Home_City = 'albany'
```
以下の例では、大文字と小文字を区別して文字列 Home_City との一致が返されます。
```
SELECT Home_City FROM Sample.Person WHERE %EXACT(Home_City) = 'albany'
```
SQL 後続関係演算子 ( ] ) では、フィールド/プロパティの照合タイプが使用されます。
ただし、SQL 包含関係演算子 ( [ ) では、フィールド/プロパティの照合タイプと関係なく、EXACT 照合が使用されます。
```
SELECT Home_City FROM Sample.Person WHERE Home_City [ 'c' 
ORDER BY Home_City
```
%MATCHES 述語条件と %PATTERN 述語条件では、フィールド/プロパティの照合タイプと関係なく、EXACT 照合が使用されます。%PATTERN 述語には、大文字と小文字を区別するワイルドカードと、大文字と小文字を区別しないワイルドカード ('A') の両方が用意されています。
ORDER BY 節： ORDER BY 節は、文字列値の順序付けにネームスペースの既定の照合を使用します。そのため、ORDER BY では、大文字小文字に基づいた順序付けが行われません。%EXACT 照合を使用すると、大文字小文字に基づいて文字列を並べ替えできます。

DISTINCT の照合と GROUP BY の照合

既定では、これらの演算子はネームスペースの現在の演算子を使用します。ネームスペースの既定の照合は、SQLUPPER です。

DISTINCT ： DISTINCT キーワードは、重複値の削除にネームスペースの既定の照合を使用します。そのため、DISTINCT Name では、すべて大文字の値が返されます。EXACT 照合を使用すると、大文字と小文字が混ざった値を返すことができます。DISTINCT は、大文字と小文字のみが異なる重複を削除します。大文字と小文字が異なる重複を保持しながら完全な重複を削除するには、EXACT 照合を使用します。以下の例では、完全な重複 (大文字と小文字が異ならないもの) を削除して、大文字と小文字が混ざった値をすべて返します。
```
SELECT DISTINCT %EXACT(Name) FROM Sample.Person
```
UNION は、暗黙的に DISTINCT 演算を伴います。
GROUP BY ： GROUP BY 節は、重複値の削除にネームスペースの既定の照合を使用します。そのため、GROUP BY Name では、すべて大文字の値が返されます。EXACT 照合を使用すると、大文字と小文字が混ざった値を返すことができます。GROUP BY は、大文字と小文字のみが異なる重複を削除します。大文字と小文字が異なる重複を維持しながら完全な重複を削除するには、select-item ではなく、GROUP BY 節で %EXACT 照合関数を指定する必要があります。
以下の例では、大文字と小文字が混ざった値が返されます。GROUP BY は大文字小文字が異なるものも含めて重複を削除します。
```
SELECT %EXACT(Name) FROM Sample.Person GROUP BY Name
```
以下の例では、大文字と小文字が混ざった値が返されます。GROUP BY は完全な重複 (大文字小文字が異ならないもの) を削除します。
```
SELECT Name FROM Sample.Person GROUP BY %EXACT(Name)
```

従来の照合タイプ

Caché SQL は、従来の照合タイプを複数サポートしています。これらは、従来のシステムを継続してサポートするために用意されているにすぎないため、非推奨であり、新しいコードで使用することはお勧めしません。従来の照合タイプは、以下のとおりです。

ALPHAUP — 疑問符 (“?”) およびコンマ (“,”) 以外の句読点文字をすべて削除し、小文字をすべて大文字に変換します。主に、従来のグローバルのマッピングに使用されます。SQLUPPER によって置き換えられました。
STRING — 論理値を大文字に変換し、すべての句読点 (コンマを除く) および空白を削除し、文字列の先頭に空白を 1 つ追加します。空白 (スペース、タブなど) だけを含む任意の値を SQL 空文字列として照合します。SQLUPPER によって置き換えられました。
UPPER — 小文字をすべて大文字に変換します。主に、従来のグローバルのマッピングに使用されます。SQLUPPER によって置き換えられました。
SPACE — SPACE 照合は値の先頭にスペースを 1 つ追加します。それにより、値を文字列として解釈させます。SPACE 照合を指定するために、CREATE TABLE には SPACE 照合キーワードが用意されています。また、ObjectScript では %SYSTEM.UtilOpens in a new tab クラスの Collation()Opens in a new tab メソッドに SPACE オプションが用意されています。これに対応する SQL 照合関数はありません。

Note:

文字列データ型のフィールドが EXACT 照合、UPPER 照合、または ALPHAUP 照合で定義されているときに、このフィールドにクエリで %STARTSWITH 条件を適用すると、動作結果が一貫しないことがあります。%STARTSWITH に指定した substring がキャノニック形式の数値の場合 (特に負の数値や小数値の場合)、%STARTSWITH はフィールドのインデックスが作成されているかどうかによって異なる結果を生成することがあります。%STARTSWITH は、列にインデックスが作成されていない場合に、予期したとおりに動作します。列にインデックスが作成されていると、予期しない結果を生じる可能性があります。

SQL 照合と NLS 照合

前述の SQL 照合と Caché NLS 照合機能とを混同しないでください。この NLS 照合機能は、特定言語の照合要求事項に従った添え字レベルのエンコードを提供するものです。照合を提供する別個のシステムが 2 つあり、これらは製品の異なるレベルで動作します。

Caché NLS 照合の場合、現在のプロセスにプロセスレベルの照合を 1 つ、また特定のグローバルに複数の照合を指定できます。

Caché SQL 使用時に適切に機能させるには、プロセスレベルの NLS 照合は関連するすべてのグローバル (テーブルで使用されるグローバル、プロセス・プライベート・グローバルなど一時ファイルで使用されるグローバル、CACHETEMP グローバルで使用されるグローバルを含む) の NLS 照合と正確に一致することが必要です。そうでないと、クエリ・プロセッサで作成された異なる処理計画によって、結果が異なってしまう可能性があります。ORDER BY 節もしくは値域条件など、並べ替えの発生する状況では、クエリ・プロセッサが最も効率的な並べ替え方法を選択します。そして、インデックスの使用、プロセス・プライベート・グローバルでの一時ファイルの使用、ローカル配列内の並べ替え、または "]]" (前後関係) 比較の使用が行われます。これらはすべて、有効な Caché NLS 照合に従った添え字タイプの比較であるため、これらすべてのグローバル・タイプでまったく同じ NLS 照合を使用することが必要になります。

グローバルは、データベースの既定の照合で作成されます。%Library.GlobalEditOpens in a new tab クラスの Create()Opens in a new tab メソッドの使用により、異なる照合でグローバルを作成することができます。この唯一の要件は、指定の照合が組み込み (Caché 標準のものなど)、もしくは現在のロケールで使用可能な国固有照合の 1 つのいずれかであることです。"Caché 専用のシステム/ツールおよびユーティリティ" の “%Library.GlobalEdit の使用によるグローバルの照合の設定” を参照してください。