ORDER BY

ソート列の指定

コンマで区切られたリストとしてソートする、単一の列、または複数の列を指定することができます。ソートは最初にリストされている列で実行され、次に同じ列内の 2 番目の列のへと続きます。

列は、列の名前、列のエイリアス、または列番号で指定できます。ORDER BY 節には、これらの任意の組み合わせを指定できます。式は、列のエイリアスまたは列番号により select-item リストに指定できます。ordering-item の最初の文字が数字の場合、Caché は列番号を指定していると見なします。それ以外は、列の名前または列のエイリアスを指定していると見なされます。列の名前と列のエイリアスでは、大文字と小文字が区別されないことに注意してください。

列の名前、列のエイリアス、または列番号は、ダイナミック SQL の ? 入力パラメータや埋め込み SQL の :var ホスト変数を使用して指定することはできません。

列名

以下の ORDER BY 節は、列の名前でソートしています。

SELECT Name,Home_State,DOB
FROM Sample.Person
ORDER BY Home_State,Name

ソート列が select-item リストにあるかどうかに関係なく、列の名前でソートできます。以下の例では、前の例と同じレコードが同じ順序で返されます。

SELECT Name,DOB
FROM Sample.Person
ORDER BY Home_State,Name

ソート列が select-item リストにない場合、列エイリアスや列番号でソートできないのは明白です。

以下の例で示されているように、ORDER BY 節では、矢印構文 (–>) 演算子を使用して、ベース・テーブルではないテーブル内のフィールドを指定できます。

SELECT Name,Company->Name AS CompName
FROM Sample.Employee ORDER BY Company->Name,Name

詳細は、"Caché SQL の使用法" の"暗黙結合" を参照してください。

列のエイリアス

以下の ORDER BY 節は、列のエイリアスでソートしています。

SELECT Name,Home_State AS HS,DOB
FROM Sample.Person
ORDER BY HS,Name

列のエイリアスは、列の名前と同じにすることができます (ただし、お勧めはしません)。列のエイリアスを指定した場合、ORDER BY は最初に列のエイリアスを参照し、次にエイリアス化されていない列の名前を参照します。あいまいさがあると、ORDER BY 節によってエラーが生成されます。ただし、列のエイリアスが、エイリアス化されている列の名前と同じ場合には、この明白なあいまいさからエラーが生成されることはありませんが、予期しない結果となる可能性があります。詳細は、以下の例を参照してください。

SELECT Name AS Moniker,Home_City AS Name
FROM Sample.Person
ORDER BY Name

エイリアスが最初に参照されるため、この例ではデータが Home_City の順に並べ替えられます。これは、意図したものと異なる可能性が高いと思われます。Name 列がエイリアス化されていない場合には、エラーが発生します。Home_City に別のエイリアスが指定された場合、ORDER BY ではエイリアスの一致が検出されず、次に列の名前がチェックされます。これにより、Name 列の順に並べられます。

以下の例に示されているように、列のエイリアスを使用して、select-item リストの式でソートできます。

SELECT Name,Age,$PIECE(AVG(Age)-Age,'.',1) AS AgeDev
FROM Sample.Employee ORDER BY AgeDev,Name

列番号

以下の ORDER BY 節は、列番号 (SELECT select-item リストで指定されているように、検索された列の数値順) でソートしています。

SELECT Name,Home_State,DOB
FROM Sample.Person
ORDER BY 2,1

SELECT リストの列に対応しない列番号を ORDER BY で使用すると、SQLCODE -5 エラーが発生します。

列番号は、SELECT 節リスト内の位置を参照します。列番号は、テーブル自体の列の位置を参照しません。整数や、またはいかなる番号でも、列番号を指定できます。標準的なトランケーション・ルールは、整数の解決に適用されます。例えば 1.99 は切り捨てにより 1 となります。

以下の例に示されているように、列番号を使用して、select-item リストの式でソートできます。

SELECT Name,Age,$PIECE(AVG(Age)-Age,'.',1)
FROM Sample.Employee ORDER BY 3,Name

照合の指定

ソートは照合順で実行されます。既定では、文字列値の順序付けは、作成時に ordering-item に対して指定された照合に基づいて行われます。Caché にはネームスペースごとに設定できる既定の文字列照合があります。文字列データ型フィールドにおける最初の照合の既定値は大文字と小文字が区別されない SQLUPPER です。したがって、通常、ORDER BY 照合では大文字と小文字は区別されません。

数値データ型フィールドの順序付けは、数値照合に基づいて行われます。式に対しては、既定照合は EXACT です。

照合関数を ordering-item フィールド名に適用することで、フィールドの既定照合をオーバーライドできます。例えば、ORDER BY %EXACT(Name) のように使用します。列エイリアスに照合関数を適用することはできません。これを行おうとすると、SQLCODE -29 エラーが発生します。

既定の昇順照合シーケンスは、空文字列 ('') よりも NULL を最小値とします。ORDER BY は、空白スペースのみで構成される文字列と空文字列を区別しません。

列に指定された照合が英数字である場合、先頭の数字は、整数順ではなく、文字照合順でソートされます。%PLUS 照合関数を使用することで、整数順に配列できます。ただし、%PLUS 照合関数は、数値以外の文字をすべて 0 として処理します。

したがって、混合数値文字列を数値順に正しくソートするには、複数の ordering-item が必要になります。例えば、Sample.Person において、住所が整数の番地、ストリート名、およびそれらを区切るスペースで構成されています。ストリート名は、2 つの要素とそれらを区切るスペースで構成されています。以下の 2 つの例を比較してください。最初の例では、住所を文字照合順でソートしています。

SELECT Name,Home_Street FROM Sample.Person
ORDER BY Home_Street

2 番目の例では、番地を整数順でソートし、ストリート名を文字照合順でソートしています。

SELECT Name,Home_Street FROM Sample.Person
ORDER BY $PIECE(%PLUS(Home_Street),' ',1),$PIECE(Home_Street,' ',2),$PIECE(Home_Street,' ',3)

%MVR 照合関数を使用して、混合数値文字列をソートすることもできます。

ASC と DESC

列識別子の前の、オプションの ASC (昇順) や DESC (降順) キーワードで指定されたように、各列のソートは昇順または降順の照合順で指定できます。ASC や DESC が指定されていない場合、ORDER BY は列を昇順でソートします。ASC または DESC キーワードは、ダイナミック SQL の ? 入力パラメータや埋め込み SQL の :var ホスト変数を使用して指定することはできません。

以下に例を示します。

SELECT A,B,C,M,E,X,J
FROM LetterTable
ORDER BY 3,7 DESC,1 ASC

SELECT 節リスト内の 3 番目にリストされた項目 (C) のデータ値を昇順でソートします。この中では、7 番目にリストされた項目 (J) の値を降順でソートします。この中では、1 番目にリストされた項目 (A) の値を昇順でソートします。

制限条件

SELECT クエリが ORDER BY 節を指定する場合、結果のデータは更新できません。したがって、後に続く DECLARE CURSOR FOR UPDATE 文を指定する場合、FOR UPDATE 節は無視され、カーソルは読み取り専用で宣言されます。

ORDER BY が UNION に適用される場合、順序付け項目は数字か単純な列名にします。式は使用できません。列名を使用する場合、列の名前付けが UNION の最初の SELECT リストで行われたものとして結果の列を参照します。

ORDER BY 節をサブクエリで使用する場合は、TOP 節と組み合わせる必要があります。これは TOP ALL 節になる場合もあります。例えば、次の FROM 節サブクエリは無効です: (SELECT DISTINCT age FROM table1 ORDER BY age)。これに対し、次の FROM 節サブクエリは有効です: (SELECT DISTINCT TOP ALL age FROM table1 ORDER BY age)。

クエリ・キャッシュ

ORDER BY 節で使用されているリテラル値ごとに、異なるクエリ・キャッシュが生成されます。ORDER BY リテラルではリテラル置換は実行されません。これは、ORDER BY は整数を使用して列番号を指定できるためです。この整数が変わると、クエリはまったく異なる結果になります。

ORDER BY と長いグローバル参照

ORDER BY の ordering-item の値は、ordering-item 数とその他の要因に応じて、(およそ) 400 から 500 文字を超えないようにしてください。ordering-item の値がこの最大長を超えている場合、ORDER BY 節を含むクエリを実行すると、以下のようなエラーが発生する場合があります。

Error: [SQLCODE: <-400>:<Fatal error occurred>] [Cache Error: <<SUBSCRIPT>%0ABMod+7^CacheSql4 ^||%sql.temp(36,0,"")>] 
[Details: <ServerLoop - Query Fetch>]  
[%msg: <Unexpected error occurred: <SUBSCRIPT>%0ABMod+7^CacheSql4 ^||%sql.temp(36,0,"")>] 

この現象は、固定の Caché システムの制限である、エンコードされるグローバル参照の最大長の制限によるものです。この問題を防ぐためには、ORDER BY 節の基礎になるフィールドの照合設定でトランケーション・レングスを使用します。例えば、以下のクエリではこの制限を超えています。

  TRY {
  ZNSPACE "SAMPLES"
  SET myquery = 3
    SET myquery(1) = "SELECT LocationCity,NarrativeSummary FROM Aviation.Event "
    SET myquery(2) = "WHERE LocationCity %Startswith 'Be' "
    SET myquery(3) = "ORDER BY NarrativeSummary"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
     IF rset.%SQLCODE=0 { WRITE !,"Executed query",! }
     ELSE { SET badSQL=##class(%Exception.SQL).%New(,rset.%SQLCODE,,rset.%Message)
            THROW badSQL }
  DO rset.%Display()
  WRITE !,"End of data"
  RETURN
  }
  CATCH exp { WRITE "In the CATCH block",!
              IF 1=exp.%IsA("%Exception.SQL") {
                WRITE "SQLCODE: ",exp.Code,!
                WRITE "Message: ",exp.Data,! }
              ELSE { WRITE "Not an SQL exception",! }
              RETURN
  }

maxlen 切り捨て長を含む照合関数を追加すると、このプログラムを正常に実行できます。

  TRY {
  ZNSPACE "SAMPLES"
  SET myquery = 3
    SET myquery(1) = "SELECT LocationCity,NarrativeSummary FROM Aviation.Event "
    SET myquery(2) = "WHERE LocationCity %Startswith 'Be' "
    SET myquery(3) = "ORDER BY %SqlUpper(NarrativeSummary,400)"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
     IF rset.%SQLCODE=0 { WRITE !,"Executed query",! }
     ELSE { SET badSQL=##class(%Exception.SQL).%New(,rset.%SQLCODE,,rset.%Message)
            THROW badSQL }
  DO rset.%Display()
  WRITE !,"End of data"
  RETURN
  }
  CATCH exp { WRITE "In the CATCH block",!
              IF 1=exp.%IsA("%Exception.SQL") {
                WRITE "SQLCODE: ",exp.Code,!
                WRITE "Message: ",exp.Data,! }
              ELSE { WRITE "Not an SQL exception",! }
              RETURN
  }

Caché は、このフィールドの照合値を 400 文字で切り捨てます。フィールドのコンテンツが 400 文字内で一意でない場合は、データの順序が若干変わる場合がありますが、このようになる可能性はほとんどありません。このようになった場合は、切り捨てる値を大きくすることで表示されるデータの順序が変わることを防ぐことができます。ただし、長すぎると、<SUBSCRIPT> エラーが発生します。

また、エンコードされるグローバル参照の最大長には、グローバル名の長さも含まれます。添え字だけではありません。