[SQL Server]メッセージ 4191、レベル 16、状態 9、行 366　is 操作での照合順序の競合を解決できません。

[スポンサードリンク]

IS NULL での照合順序の問題。

エラーメッセージ

メッセージ 4191、レベル 16、状態 9、行 366
is 操作での照合順序の競合を解決できません。

原因

データベースの照合順序と異なる照合順序のテーブルを結合した際に、照合順序の違うテーブルの列に対して、「IS NULL」演算子を使用した。

例えば、データベースの照合順序が、「Japanese_BIN2」だとする。
このデータベースを扱うSQLクエリで一時テーブル「#」から始まるテーブルを作成し、データを格納。
この一時テーブルをデータベースのテーブルと結合し、処理を行う。

この場合下記の記述でエラーになる。
「列名A」は一時テーブルのカラムである。

CASE WHEN 列名A IS NULL THEN '' ELSE 列名A END

1	CASE WHEN 列名A IS NULL THEN '' ELSE 列名A END

対策

CREATE TABLEで照合順序を合わせる

一時テーブルを作成するときに、CREATE TABLEの文字列項目にCOLLATE句で、データベースの照合順序に合わせる。

CREATE TABLE [#TEMP_ITEM] (
    ITEM_CD     nvarchar(18)    COLLATE Japanese_BIN2, -- 商品コード
    KYOTEN_CD   nvarchar(4)     COLLATE Japanese_BIN2, -- 生産拠点コード
    QTY         int                                    -- 数量
);

CREATE TABLE [#TEMP_ITEM] (

ITEM_CD nvarchar(18) COLLATE Japanese_BIN2, -- 商品コード

KYOTEN_CD nvarchar(4) COLLATE Japanese_BIN2, -- 生産拠点コード

QTY int -- 数量

);

※照合順序は文字列型の比較に関するものなので、intなどには付けない。

エラーの場所で照合順序を合わせる

「IS NULL」を使用している場所で照合順序を合わせる。

CASE WHEN 列名A COLLATE Japanese_CI_AS IS NULL THEN '' ELSE 列名A COLLATE Japanese_CI_AS END

1	CASE WHEN 列名A COLLATE Japanese_CI_AS IS NULL THEN '' ELSE 列名A COLLATE Japanese_CI_AS END

列名AとNULLの照合順序が違うので、列名Aのあとに「COLLATE Japanese_CI_AS」または「COLLATE Japanese_BIN2」を記述して、どちらか一方の照合順序に合わせる。

なぜ起こるのか

なぜ、一時テーブルを作成すると、照合順序の相違が発生するのか？
調査してみた。

新規にデータベースを作成したあと、

データベースのプロパティを開くと、照合順序は「Japanese_CI_AS」となっていた。

つまり、データベース作成時に、特に指定しない場合、SQL Serverのデフォルトの照合順序は「Japanese_CI_AS」となるようである。

このため、これ以外の照合順序でデータベースが作られていた場合、一時テーブルを作成するとデフォルトの「Japanese_CI_AS」で作成されるため、照合順序の相違が発生する。

[スポンサードリンク]

IT☆ぱらだいす

全てのITエンジニアの役に立ちたい