トランスフォーメーション > ルックアップトランスフォーメーション > ルックアップSQLオーバーライド

ルックアップSQLオーバーライド

マッピングにルックアップトランスフォーメーションが含まれる場合、マッピングタスクでは、ルックアップトランスフォーメーションで設定するフィールドおよびプロパティに基づいてルックアップオブジェクトを照会します。マッピングタスクは、データの最初の行がルックアップトランスフォーメーションに入ると、デフォルトのルックアップクエリを実行します。ルックアップトランスフォーメーションでリレーショナルルックアップを実行すると、デフォルトのクエリをオーバーライドできます。

キャッシュされたルックアップおよびキャッシュされていないルックアップに対して、デフォルトのクエリをオーバーライドすることができます。デフォルトのクエリを表示するには、マッピングタスクを実行します。デフォルトのクエリがログファイルに表示されます。

キャッシュされたルックアップに対するルックアップSQLオーバーライド

キャッシュされたルックアップでは、デフォルトのSQLクエリをオーバーライドして、ORDER BY句の変更、WHERE句の追加、またはデータの変換を行うことができます。マッピングは、オーバーライド文を使用してルックアップキャッシュを構築します。

デフォルトのクエリには、マッピング内のすべてのルックアップフィールドを含むSELECT文が含まれています。また、SELECT文には、すべてのカラムをルックアップトランスフォーメーションの表示と同じ順序で並べたORDER BY句も含まれています。

ORDER BY句の変更、WHERE句の追加、またはキャッシュする前にルックアップデータの変換を行う場合は、デフォルトのクエリをオーバーライドします。例えば、データベース関数を使用して、マッピングで使用されるフィールドのデータ型およびフォーマットと一致するようにルックアップテーブルのデータ型またはフォーマットを調整できます。または、デフォルトのクエリをオーバーライドして複数のテーブルを照会できます。

デフォルトのクエリは、ルックアップトランスフォーメーションの［詳細］タブでオーバーライドします。［ルックアップSQLオーバーライド］フィールドに、SELECT文をすべて入力します。クエリの各カラムにはエイリアスを使用します。ORDER BY句を変更する場合は、クエリの末尾に「--」を追加して、マッピングタスクによって生成されるORDER BY句を抑制します。

例

ルックアップトランスフォーメーションは、Microsoft SQL ServerのテーブルALC_ORDER_DETAILSから次のフィールドを返します。

テーブルALC_ORDER_DETAILSの各戻りフィールド（［ORDERID］、［PRODUCTID］、［UNITPRICE］、［QUANTITY］、［DISCOUNT］）が一覧表示されたルックアップトランスフォーメーションの［戻りフィールド］タブ。

トランスフォーメーションは、以下のルックアップ条件を使用します。

ORDERID=in_ORDERID

マッピングタスクを実行すると、ログファイルに次のクエリが表示されます。

LKPDP_1> DBG_21097 [2018-11-07 14:11:33.509] Lookup Transformation [lkp_ALC_ORDER_DETAILS]: Default sql to create lookup cache: SELECT PRODUCTID,UNITPRICE,QUANTITY,DISCOUNT,ORDERID FROM "icsauto"."ALC_ORDER_DETAILS" ORDER BY ORDERID,PRODUCTID,UNITPRICE,QUANTITY,DISCOUNT

ORDER BY句をオーバーライドしてPRODUCTIDの順に並べ替えるには、［詳細］タブの［ルックアップSQLオーバーライド］フィールドに次のクエリを入力します。

SELECT PRODUCTID AS PRODUCTID, UNITPRICE AS UNITPRICE, QUANTITY AS QUANTITY, DISCOUNT AS DISCOUNT, ORDERID AS ORDERID FROM "icsauto"."ALC_ORDER_DETAILS" ORDER BY PRODUCTID --

マッピングタスクを再度実行すると、ログファイルに次のクエリが表示されます。

LKPDP_1> DBG_21312 [2018-11-07 14:14:36.734] Lookup Transformation [lkp_ALC_ORDER_DETAILS]: Lookup override sql to create cache: SELECT PRODUCTID AS PRODUCTID, UNITPRICE AS UNITPRICE, QUANTITY AS QUANTITY, DISCOUNT AS DISCOUNT, ORDERID AS ORDERID FROM "icsauto"."ALC_ORDER_DETAILS" ORDER BY PRODUCTID -- ORDER BY ORDERID,PRODUCTID,UNITPRICE,QUANTITY,DISCOUNT

キャッシュされていないルックアップに対するルックアップSQLオーバーライド

キャッシュされていないルックアップのデフォルトのSQLクエリ（WHERE句やORDER BY句など）をオーバーライドできます。マッピングタスクにより、キャッシュされていないルックアップのオーバーライド文からキャッシュが構築されることはありません。

マッピングタスクを実行すると、タスクにより、ルックアップ条件に基づいて、ルックアップフィールド、出力フィールド、およびWHERE句を含むデフォルトのSELECT文が生成されます。ルックアップトランスフォーメーションが未接続のルックアップである場合、SELECT文にはルックアップフィールドとリターンフィールドが含まれますが、マッピングタスクによってルックアップ条件からWHERE句が生成されることはありません。

デフォルトのクエリは、ルックアップトランスフォーメーションの［詳細］タブでオーバーライドします。［ルックアップSQLオーバーライド］フィールドに、SELECT文をすべて入力します。クエリの各カラムにはエイリアスを使用します。ORDER BY句を変更する場合は、クエリの末尾に「--」を追加してマッピングタスクによって生成されるORDER BY句を抑止します。

WHERE句で受信フィールドを参照するには、フィールド名の前と後に「?」を追加してパラメータバインディングを設定します。クエリデータは、フィールド内のデータの値に基づいて変化します。次の例には、Nameフィールドを参照するWHERE文が含まれています。

SELECT EMPLOYEE.NAME as NAME, max(EMPLOYEE.ID) as ID from EMPLOYEE WHERE EMPLOYEE.NAME=?NAME1?

SQL文に関数を追加する場合は、returnデータ型がALIASカラムのデータ型に一致している必要があります。例えば、IDのデータ型はMAX関数のreturn型に一致します。

SELECT EMPLOYEE.NAME as NAME, MAX(EMPLOYEE.ID) as ID FROM EMPLOYEE

注: キャッシュを使用しないルックアップに対して、SQLオーバーライド内のサブクエリを使用することはできません。

ルックアップクエリのオーバーライドのガイドライン

ルックアップクエリをオーバーライドするときに、一定のルールとガイドラインが適用されます。

次のガイドラインを使用します。

•リレーショナルルックアップの場合は、ルックアップSQLクエリをオーバーライドできます。
•データベースで必要とされる構文を使用して、SELECT文をすべて入力します。
•データベース予約語はすべて引用符で囲んでください。
•SELECT文のルックアップフィールドと戻りフィールドをすべて含めます。

SELECT文のフィールド数が増減すると、マッピングタスクに失敗します。

•クエリの各カラムにはエイリアスを使用します。

カラムのエイリアスを使用しないと、マッピングタスクに失敗して以下のエラーが表示されます。

Failed to initialize transformation [<Lookup Transformation Name>]

•クエリにFROM句を含める必要があります。
•ORDER BY句をオーバーライドするには、クエリの末尾に「--」を追加します。

オーバーライドでORDER BY句を入力しても、マッピングタスクはORDER BY句を生成します。そのため、クエリの末尾にダッシュを2つ（--）入力することで生成したORDER BY句を抑止する必要があります。

•ORDER BY句に複数のカラムが含まれている場合は、ルックアップ条件のフィールドと同じ順序でカラムを入力します。
•マッピングタスクでSQL ELTの最適化が使用されている場合、またはマッピングタスクがSQL ELTモードのマッピングに基づいている場合、ORDER BY句のオーバーライドや、生成されたORDER BY句のコメント表記での抑制を行うことはできません。
•複数のルックアップトランスフォーメーションでルックアップキャッシュを共有している場合、各ルックアップトランスフォーメーションで同じルックアップSQLオーバーライドを使用します。
•すべての行を返すルックアップトランスフォーメーションを設定する際に、マッピングタスクはソートされたキーを使用してルックアップキャッシュを構築します。トランスフォーメーションがルックアップのすべての行を取得する際に、マッピングタスクはソートされた順序でキーを使用してデータキャッシュを構築します。行がソートされていなければ、マッピングタスクはキャッシュからすべての行を取得できません。データがキーに対してソートされていない場合は、予期しない結果になる可能性があります。
•ルックアップSQLオーバーライドにパラメータを含めることはできません。
•ルックアップSQLオーバーライドとルックアップソースフィルタを同じトランスフォーメーションに設定する場合、マッピングタスクはフィルタを無視します。