| Oracle® Fusion Middleware Oracle Business Intelligence Publisherデータ・モデリング・ガイド 12c (12.2.1.2.0) E82970-01 |
|
 前へ |
 次へ |
| Oracle® Fusion Middleware Oracle Business Intelligence Publisherデータ・モデリング・ガイド 12c (12.2.1.2.0) E82970-01 |
|
|
前へ |
次へ |
このトピックでは、SQL問合せを使用してデータ・セットを作成する方法について説明します。
この手順を使用して、SQL問合せを入力します。
SQL問合せおよび「実行計画の生成」オプションの最適化の詳細は、SQLデータ・セットのベスト・プラクティスを参照してください。
基本SQLコマンドを使用したデータ・セットの作成に加えて、より複雑なコマンドを使用してデータ・セットを作成できます。
プロシージャ・コール
この問合せタイプは、データベース・プロシージャをコールする場合に使用します。たとえば、Oracle PL/SQLの文はBEGINで始まります。このSQLデータ型を使用すると、メタデータはデータ・モデル構造のタブに表示されないため、データ構造またはデータ・フィールドを変更できません。プロシージャ・コールを使用してSQLを作成する場合、テキスト・ボックスにコードを直接入力するか、他のSQLエディタからコピーして貼り付けます。クエリー・ビルダーは、このようなタイプの問合せの変更または作成に使用できません。
非標準SQL
この問合せタイプは、次のものを組み込むことができるSQL文を発行する場合に使用します。
ネストされた結果セットを返すCursor文
たとえば、次のように入力します。
Ex:SELECT TO_CHAR(sysdate,'MM-DD-YYYY') CURRENT_DATE ,
CURSOR
(SELECT d.order_id department_id,
d.order_mode department_name ,
CURSOR
(SELECT e.cust_first_name first_name,
e.cust_last_name last_name,
e.customer_id employee_id,
e.date_of_birth hire_date
FROM customers e
WHERE e.customer_id IN (101,102)
) emp_cur
FROM orders d
WHERE d.customer_id IN (101,102)
) DEPT_CUR FROM dual
REFカーソルを返す関数
たとえば、次のように入力します。
create or replace PACKAGE REF_CURSOR_TEST AS TYPE refcursor IS REF CURSOR; pCountry VARCHAR2(10); pState VARCHAR2(20); FUNCTION GET( pCountry IN VARCHAR2, pState IN VARCHAR2) RETURN REF_CURSOR_TEST.refcursor; END;
create or replace PACKAGE BODY REF_CURSOR_TEST AS
FUNCTION GET(
pCountry IN VARCHAR2,
pState IN VARCHAR2)
RETURN REF_CURSOR_TEST.refcursor
IS
l_cursor REF_CURSOR_TEST.refcursor;
BEGIN
IF ( pCountry = 'US' ) THEN
OPEN l_cursor FOR
SELECT TO_CHAR(sysdate,'MM-DD-YYYY') CURRENT_DATE ,
d.order_id department_id,
d.order_mode department_name
FROM orders d
WHERE d.customer_id IN (101,102);
ELSE
OPEN l_cursor FOR
SELECT * FROM EMPLOYEES;
END IF;
RETURN l_cursor;
END GET;
END REF_CURSOR_TEST;
Oracle BI PublisherでREFカーソルを使用するには、次のようにします。
create SQL dataset with query as SELECT REF_CURSOR_TEST.GET(:PCNTRY,:PSTATE) AS CURDATA FROM DUAL
匿名ブロック/ストアド・プロシージャ
BI Publisherでは、PL/SQL匿名ブロックの実行がサポートされています。PL/SQLブロックで計算を実行し、結果のセットを返すことができます。BI Publisherでは、コール可能文を使用して匿名ブロックを実行します。
要件は次のとおりです。
PL/SQLブロックは、REFカーソル型の結果セットを返す必要があります
名前がxdo_cursor;のout変数を宣言する必要があります。名前を適切に宣言しないと、最初のバインド変数がout変数タイプとして扱われ、REFカーソルとバインドします。
名前がxdo_cursorのデータ・モデル・パラメータを宣言します。この名前は、プロシージャ/匿名ブロックのout変数タイプ用に予約されています。
例:
DECLARE
type refcursor is REF CURSOR;
xdo_cursor refcursor;
empno number;
BEGIN
OPEN :xdo_cursor FOR
SELECT *
FROM EMPLOYEES E
WHERE E.EMPLOYEE_ID = :P2;
COMMIT;
END;
if-else式を使用すると、条件付き問合せを実行できます。単一のデータ・セットで複数のSQL問合せを定義できますが、式の値に応じて実行時に実行される問合せは1つのみです。式はブール値を検証して返します。値がtrueの場合、SQL問合せのそのセクションが実行されます。
次のような制限があります。
式の評価では、構文: $if{、$elseif{、$else{がサポートされています
式はtrueまたはfalseを返す必要があります。
次の演算子のみサポートされています。
== <= >= < >
Example:
create sql dataset with following query
$if{ (:P_MODE == PRODUCT) }$
SELECT PRODUCT_ID
,PRODUCT_NAME
,CATEGORY_ID
,SUPPLIER_ID
,PRODUCT_STATUS
,LIST_PRICE
FROM PRODUCT_INFORMATION
WHERE ROWNUM < 5
$elsif{(:P_MODE == ORDER )}$
SELECT ORDER_ID
,ORDER_DATE
,ORDER_MODE
,CUSTOMER_ID
,ORDER_TOTAL
,SALES_REP_ID
FROM ORDERS
WHERE ROWNUM < 5
$else{
SELECT PRODUCT_ID
, WAREHOUSE_ID
,QUANTITY_ON_HAND
FROM INVENTORIES
WHERE ROWNUM < 5
}$
$endif$
非標準SQL文を使用してデータ・セットを作成すると、メタデータはデータ・モデル構造のタブに表示されないため、データ構造またはデータ・フィールドを変更できません。クエリー・ビルダーは、このようなタイプの問合せの変更または作成に使用できません。
非標準SQLデータ・セットのXML行タグを定義するには:
データ・モデルの定義でxmlRowTagName=""を使用して、非標準SQL問合せデータ・セットのxml行タグを定義します。これにより、有効なタグ名を入力できます。属性が空の場合、実行時のデフォルトはROWになります。
データセットの定義を次に示します。
<dataSet name="Q1" type="simple"> <sql dataSourceRef="bipdev4-demo" nsQuery="true" xmlRowTagName=""> ,, </sql> </dataset>
コーディングせずにSQL問合せを作成するには、クエリー・ビルダーを使用します。クエリー・ビルダーを使用すると、SQLに関する最小限の知識で、データベース・オブジェクトの検索とフィルタ処理、オブジェクトと列の選択、オブジェクト間の関連の作成、および書式設定された問合せ結果の表示を行うことができます。
この項では、クエリー・ビルダーの使用方法について説明します。この項の内容は次のとおりです。
「クエリー・ビルダー」ページは、「オブジェクト選択」ペインと設計と出力ペインに分かれています。
「オブジェクト選択」ペインには、問合せの作成に使用できるオブジェクトのリストが表示されます。現行スキーマのオブジェクトのみが表示されます。
設計と出力ペインは、次の4つのタブで構成されています。
モデル - 「オブジェクト選択」ペインから選択したオブジェクトが表示されます。
条件 - 選択した列に条件を適用できます。
SQL - 問合せが表示されます。
結果 - 問合せの結果が表示されます。
この手順を実行して問合せを作成します。
スキーマを選択します。
「スキーマ」リストには、データソースで使用可能なすべてのスキーマが表示されます。リスト内にアクセスできないものが含まれる場合もあります。
「設計」ペインにオブジェクトを追加して、列を選択します。
「オブジェクト選択」ペインには、選択したスキーマの表、ビューおよびマテリアライズド・ビューがリストされます(Oracle Databaseの場合、シノニムもリストされます)。リストからオブジェクトを選択すると、そのオブジェクトが「設計」ペインに表示されます。「設計」ペインを使用して、選択したオブジェクトを問合せで使用する方法を特定します。
「検索」フィールドを使用して、検索文字列を入力することが必要になる場合があります。データソースに100を超える表がある場合は、この検索機能を使用して、目的のオブジェクトを検索および選択する必要があります。
(省略可能)オブジェクト間の関係を確立します。
すべての重複列に一意の別名を追加します。
(省略可能)問合せ条件を作成します。
問合せを実行し、結果を表示します。
すべてのタイプの列がオブジェクトとして「設計」ペインに表示されます。
列の制限は次のとおりです。
各問合せで選択できる列の数は60以下です。
次の列タイプから選択できます。
VARCHAR2、CHAR
NUMBER
DATE, TIMESTAMP
注意:
TIMESTAMP WITH LOCAL TIMEZONEデータ型はサポートされていません。
バイナリ・ラージ・オブジェクト(Binary Large Object: BLOB)
注意:
BLOBはイメージです。クエリー・ビルダーで問合せを実行すると、BLOBは「結果」ペインに表示されませんが、問合せはデータ・モデル・エディタに保存されるときに正しく作成されます。
キャラクタ・ラージ・オブジェクト(CLOB)
データ・モデル内でCLOBデータを使用する方法の詳細は、キャラクタ・ラージ・オブジェクト(CLOB)として格納されたデータのデータ・モデル内での使用方法を参照してください。
設計ペインに追加する各オブジェクトを選択します。
オブジェクトを追加すると、データ型を表すアイコンが列名の横に表示されます。
列を選択すると、その列が「条件」タブに表示されます。「条件」タブの「表示」チェック・ボックスは、問合せ結果に列を含めるかどうかを制御します。このチェック・ボックスは、デフォルトでは選択されています。
最初の20列を選択するには、オブジェクトの左上隅にある小さいアイコンをクリックし、すべてチェックを選択します。
問合せは、[Ctrl]+[Enter]キーを押して実行することもできます。
設計ペインでオブジェクトを削除または非表示にできます。
オブジェクトを削除するには:
オブジェクトの右上隅にある「削除」をクリックします。
オブジェクト内の列を一時的に非表示にするには:
条件を使用すると、作業対象のデータをフィルタ処理して識別できます。
オブジェクト内の列を選択するときは、(次に示した)「条件」タブで条件を指定できます。これらの属性を使用して、列別名の変更、列条件の適用、列のソート、または関数の適用を行うことができます。
次の表では「条件」タブで使用可能な属性について説明します。
| 条件属性 | 説明 |
|---|---|
上矢印および下矢印 |
問合せ結果内の列の表示順序を制御します。 |
列 |
列名を表示します。 |
別名 |
列の別名(オプション)を指定します。別名とは列名の別称です。別名は、列名をより説明的にする場合や、列名を短縮する場合、あいまいな参照を回避する場合に使用されます。別名にはマルチバイト・キャラクタはサポートされないので注意してください。 |
オブジェクト |
オブジェクト名が表示されます。 |
条件 |
条件によって、問合せのWHERE句が変更されます。列条件を指定するときは、適切な演算子およびオペランドを含める必要があります。標準のSQL条件はすべてサポートされています。たとえば、次のように入力します。 >=10 ='VA' IN (SELECT dept_no FROM dept) BETWEEN SYSDATE AND SYSDATE + 15 |
ソート・タイプ |
ASC(昇順)またはDESC(降順)を選択します。 |
ソート順序 |
番号(1、2、3・・・)を入力して、選択した列が表示される順序を指定します。 |
表示 |
列を問合せ結果に含めるには、このチェック・ボックスを選択します。フィルタ処理のためにのみ列を問合せに追加する場合は、「表示」を選択する必要はありません。たとえば、次の問合せを作成する場合について説明します。 この問合せをクエリー・ビルダーで作成する手順は、次のとおりです。
|
関数 |
使用可能な引数関数は次のとおりです。
|
グループ基準 |
集計関数の使用時にグループ化に使用する列を指定します。出力に含まれる列にのみ適用できます。 |
削除 |
問合せから列を排除して、選択解除します。 |
列を選択し条件を定義すると、クエリー・ビルダーによってSQLが書き込まれます。
基になるSQLを表示するには:
「SQL」タブを選択します。
結合を作成することで、オブジェクト間の関係を作成できます。結合によって、複数の表、ビューまたはマテリアライズド・ビューの間の関連が識別されます。
結合の問合せを記述するときは、2つのオブジェクトの関係を示す条件を指定します。この条件は結合条件と呼ばれます。
結合条件によって、1つのオブジェクトの行を別のオブジェクトの行とどのように組み合せるかが指定されます。
クエリー・ビルダーは、内部結合、外部結合、左側結合および右側結合をサポートしています。
内部結合(単純結合とも呼ばれます)は、結合条件を満たす行を戻します。
外部結合は、単純結合の結果を拡張します。
外部結合は、結合条件を満たすすべての行を戻し、さらに、一方の表に結合条件を満たす行がない場合は、結合条件を満たす一部またはすべての行をもう一方の表から戻します。
問合せを構築した後にその問合せを保存します。
問合せの作成後には、「保存」をクリックしてデータ・モデル・エディタに戻ります。この問合せは、「SQL問合せ」ボックスに表示されます。「OK」をクリックするとデータ・セットが保存されます。
クエリー・ビルダーからデータ・モデル・エディタに問合せを保存しておくと、その問合せの編集にクエリー・ビルダーを使用することもできます。
問合せが変更されている場合や、問合せの作成にクエリー・ビルダーを使用していない場合には、その問合せを編集するためにクエリー・ビルダーを起動するとエラーが示されることがあります。クエリー・ビルダーで問合せを解析できないときには、テキスト・ボックス内で文を直接編集してください。
問合せの作成後、ユーザーがこの問合せにパラメータを渡して結果を限定できるようにすることが必要な場合があります。たとえば、従業員のリストで、ユーザーが特定の部門を選択できるようにする必要があります。
次のイメージに、部門表の列を示します。
データ・モデル・エディタを使用して、SQL問合せを更新します。
and and "COLUMN_NAME" in (:PARAMETER_NAME)
次に例を示します。
and "DEPARTMENT_NAME" in (:P_DEPTNAME)
P_DEPTNAMEは、次に示されているように、パラメータに選択した名前です。
字句参照を使用して、SELECT、FROM、WHERE、GROUP BY、ORDER BYまたはHAVINGの後にくる句を置換できます。
実行時にパラメータで複数の値を置換する場合は字句参照を使用します。字句参照がサポートされるのは、Oracleアプリケーションに対する問合せのみです。
SQL問合せの中に字句参照を作成するには、次の構文を使用します。
¶metername
注意:
字句参照を使用すると、フレックスフィールドを問合せに組み込むこともできます。フレックスフィールドの使用方法の詳細は、フレックスフィールドの追加を参照してください。
たとえば、パッケージをemployeeという名前で作成します。employeeパッケージの中で、where_clauseというパラメータを次のように定義します。
Package employee AS where_clause varchar2(1000); ..... Package body employee AS ..... where_clause := 'where DEPARTMENT_ID=10'; .....
SQL問合せの中で字句パラメータを参照します。このパラメータが、パッケージ内で定義されたコードで置き換えられます。次に例を示します。
select "EMPLOYEES"."EMPLOYEE_ID" as "EMPLOYEE_ID", "EMPLOYEES"."FIRST_NAME" as "FIRST_NAME", "EMPLOYEES"."LAST_NAME" as "LAST_NAME", "EMPLOYEES"."SALARY" as "SALARY", from "OE"."EMPLOYEES" "EMPLOYEES" &where_clause
SQLデータ・セットの作成ダイアログ・ボックスの「OK」をクリックすると、字句参照ダイアログ・ボックスが表示されます。これは、SQL問合せ内に入力した字句参照の値を入力するためのものです(次のイメージを参照)。字句参照の値を、PL/SQLパッケージ内で定義されているとおりに入力します。
実行時に、パッケージ内で定義されたwhere_clauseの内容で &where_clauseが置き換えられます。
クエリー・ビルダーをOracle BIサーバーに対して起動すると、クエリー・ビルダーには、カタログからのサブジェクト・エリアが表示されます。このサブジェクト・エリアをクエリー・ビルダーの作業領域にドラッグすると、列が表示されます。データ・モデルに入れる列を選択します。
データ・セットをOracle BIサーバーに対して作成するときは、次の点に注意してください。
SQLデータ・エディタまたはクエリー・ビルダーを使用して、SQL問合せをOracle BIサーバーに対して作成するときは、論理SQLが生成されます。他のデータベース・ソースの場合とは異なり、物理SQLは生成されません。
階層列はサポートされません。最も上のレベルが常に返されます。
サブジェクト・エリアの中では、表間の結合条件がすでに作成されています。したがって、クエリー・ビルダーで結合を作成する必要はありません。クエリー・ビルダーによって主キーが公開されることはありません。
データ・モデル・エディタの「リンクの作成」機能を使用してデータ・セットどうしをリンクできます。要素レベルのリンクの作成を参照してください。Oracle BIサーバーから作成したデータ・セットの場合、1つのデータ・モデルに対して2つの要素レベルのリンクという制限があります。
クエリー・ビルダーの「条件」タブに表示される機能のうち、「ソート順序」と「グループ基準」は、Oracle BIサーバーに対する問合せの場合はサポートされません。「ソート順序」に入力した場合や、「グループ基準」チェック・ボックスを選択した場合も、クエリー・ビルダーによってSQLが作成されてBI Publisherの「SQL問合せ」テキスト・ボックスに出力されますが、「データ・セット」ダイアログを閉じようとすると、問合せは検証不合格となります。
SQL問合せで取り出したデータをグループ化するには、データ・モデル・エディタの「グループ基準」機能をかわりに使用してください。サブグループの作成を参照してください。
パラメータをOracle BIサーバーに渡すとともに、「全選択可能」で「NULL値が渡されました」を選択した場合は、問合せの中で確実にnull値の処理を行う必要があります。
Oracle Fusionアプリケーション表に対する問合せを書く際にOracle Fusionアプリケーション顧客に対する特別な考慮事項が適用されます
月の名前をsysdateからto_char(sysdate,"mon")を使用して返すことはできません。この関数は月の番号を返します。月の名前を表示するには、次のいずれかのソリューションを使用します:
レイアウトの日付フィールドを、次の構文を使用して書式設定します: <?format_date:fieldname;MASK)?>
レイアウトにおける日付の書式設定の詳細は、Oracle Business Intelligence Publisherレポート・デザイナーズ・ガイドの日付の書式設定を参照してください。
月の番号に基づいて月の名前を表示するには、レイアウトで次の構文を使用します:
<?xdoxslt:month_name(month, [abbreviate?], $_XDOLOCALE)?>
このmonthは、月の数値(January = 1)で、
[abbreviate?]は、省略しない場合は0で省略する場合は1です。
たとえば、次のように入力します。
<?xdoxslt:month_name(1, 0, $_XDOLOCALE)?>
Januaryが返されます
データ・モデルに式を追加するには、次の式を使用します:
Format_date(date, format_String)
たとえば、次のように入力します。
SUBSTRING(FORMAT_DATE(G_1.SYSDATE,MEDIUM),0,3)
Novが返されます(現在のSYSTDATEがNovemberの場合)
