SQL問合せを使用したデータセットの作成
次のトピックでは、SQL問合せを使用してデータセットを作成する方法について説明します。
標準以外のSQLデータセットの作成
基本的なSQLコマンドを使用してデータセットを作成する以外に、より複雑なコマンドを使用してデータセットを作成できます。
プロシージャ・コール
この問合せタイプを使用して、データベース・プロシージャをコールします。 たとえば、Oracle PL/SQL文はBEGINで始まります。 このSQLデータ型を使用する場合、メタデータはデータ・モデル構造タブに表示されないため、データ構造またはデータ・フィールドは変更できません。 プロシージャ・コールを使用してSQLを作成するには、テキスト・ボックスにコードを直接入力するか、別のSQLエディタからコピー・アンド・ペーストします。 問合せビルダーを使用してこれらのタイプの問合せを変更または作成することはできません。
非標準SQL
この問合せタイプを使用して、次のようなSQL文を発行します:
-
ネストされた結果セットを返すカーソル文
たとえば:
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;パブリッシャで
REFカーソルを使用するには:create SQL dataset with query as SELECT REF_CURSOR_TEST.GET(:PCNTRY,:PSTATE) AS CURDATA FROM DUAL
-
匿名ブロック/ストアド・プロシージャ
「パブリッシャ」は、PL/SQL無名ブロックの実行をサポートしています。 PL/SQLブロックで計算を実行し、結果セットを返すことができます。 「パブリッシャ」は、コール可能な文を使用して無名ブロックを実行します。
要件は次のとおりです:
-
PL/SQLブロックは、タイプ
REFカーソルの結果セットを返す必要があります -
out変数は、
xdo_cursor;という名前で宣言する必要があります。 名前を正しく宣言しない場合、最初のバインド変数は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式を使用すると、条件付き問合せを実行できます。 1つのデータセットに複数の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問合せを作成します。 クエリー・ビルダーを使用すると、最小限のSQLのナレッジで、データベース・オブジェクトの検索およびフィルタ適用、オブジェクトおよび列の選択、オブジェクト間の関係の作成および問合せ結果の表示を行うことができます。
この項では、問合せビルダーの使用方法について説明します。次のトピックで構成されます:
問合せビルダーの概要
「問合せビルダー」ページは、「オブジェクト選択」ペインと設計および出力ペインに分かれています。
-
「オブジェクト選択」ペインには、問合せを作成できるオブジェクトのリストが表示されます。 現行スキーマのオブジェクトのみが表示されます。
-
設計ペインと出力ペインは、次の4つのタブで構成されています:
-
「モデル」 - 「オブジェクト選択」ペインから選択したオブジェクトを表示します。
-
「条件」 - 選択した列に条件を適用できます。
-
SQL - 問合せを表示します。
-
「結果」 - 問合せの結果が表示されます。
-
サポートされている列タイプ
すべてのタイプの列は、オブジェクトとして設計ペインに表示されます。 各問合せで選択できる列の数は60以下です。
| サポートされている列タイプ | 制限事項 |
|---|---|
| VARCHAR2, CHAR | NA |
| NUMBER | NA |
| DATE, TIMESTAMP | TIMESTAMP WITH LOCAL TIMEZONEデータ型はサポートされていません。
|
| バイナリ・ラージ・オブジェクト(BLOB) | BLOBは、イメージ、テキストまたはXMLデータです。 クエリー・ビルダーで問合せを実行すると、BLOBは結果ペインに表示されませんが、問合せはデータ・モデル・エディタに保存すると正しく作成されます。 BIJDBCドライバの制限により、BLOBデータはOracle BI EEデータ・ソースではサポートされていません。
イメージ・データ型でBLOBデータ列を使用する場合は、RTFテンプレートを使用します。 |
| キャラクタ・ラージ・オブジェクト(CLOB) | 「パブリッシャ」では、Oracle BI EEデータ・ソースのCLOB列の問合せはサポートされていません。 |
デザイン・ペインへのオブジェクトの追加
設計ペインに追加する各オブジェクトを選択します。
-
オブジェクトを追加する際、データ型を表すアイコンが各列名の横に表示されます。
-
列を選択すると、「条件」タブに表示されます。 「条件」タブの「表示」チェック・ボックスは、問合せ結果に列を含めるかどうかを制御します。 デフォルトでは、このチェックボックスはオンになっています。
-
最初の20列を選択するには、オブジェクトの左上隅にある小さいアイコンをクリックし、「すべてチェック」を選択します。
-
CTRL + ENTERキーを押して問合せを実行することもできます。
- オブジェクトを選択します。
- 問合せに含める各列のチェック・ボックスを選択します。
- 問合せを実行して結果を表示するには、「結果」を選択します。
設計ペインでのオブジェクトの削除または非表示
設計ペインでオブジェクトを削除または非表示にできます。
オブジェクトを削除するには:
-
オブジェクトの右上隅にある「除去」をクリックします。
オブジェクト内の列を一時停止するには:
- 「列の表示/非表示」をクリックします。
問合せ条件
条件を使用すると、操作するデータをフィルタおよび識別できます。
オブジェクト内の列を選択すると、「条件」タブで条件を指定できます。 列の別名を変更したり、列条件を適用したり、列をソートしたり、関数を適用できます。
| 条件属性 | 説明 |
|---|---|
|
条件 |
条件によって、問合せのWHERE句が変更されます。 列条件を指定する場合は、適切な演算子とオペランドを含める必要があります。 すべての標準SQL条件がサポートされています。 たとえば: >=10 ='VA' IN (SELECT dept_no FROM dept) BETWEEN SYSDATE AND SYSDATE + 15 |
|
関数 |
関数を指定します。 使用可能な引数関数には次が含まれます:
|
|
グループ別 |
集計関数の使用時にグループ化に使用する列を指定します。 出力に含まれる列にのみ適用できます。 |
列を選択し条件を定義すると、問合せビルダーによってSQLが書き込まれます。 基礎となるSQLを表示するには、SQLタブを選択します。
オブジェクト間の関係の作成
結合を作成することで、オブジェクト間の関係を作成できます。 結合によって、複数の表、ビューまたはマテリアライズド・ビューの関係が識別されます。
結合条件について
結合問合せを記述する場合は、2つのオブジェクト間の関係を示す条件を指定します。 この条件は結合条件と呼ばれます。
結合条件によって、1つのオブジェクトの行を別のオブジェクトの行とどのように組み込むかが指定されます。
問合せビルダーは、内部結合、外部結合、左側結合および右側結合をサポートしています。
-
単純結合とも呼ばれ、結合条件を満たす行を戻します。
-
外部結合は、単純結合の結果を拡張します。
外部結合は、結合条件を満たすすべての行を返し、一方の表から、もう一方の行が結合条件を満たさない行の一部またはすべてを返します。
問合せの保存
問合せの作成後に保存します。
問合せを作成したら、「保存」をクリックしてデータ・モデル・エディタに戻ります。 問合せがSQL問合せボックスに表示されます。 OKをクリックしてデータセットを保存します。
保存済問合せの編集
問合せをクエリー・ビルダーからデータ・モデル・エディタに保存する際、クエリー・ビルダーを使用して問合せを編集することもできます。
問合せを変更したか、問合せビルダーを使用して作成しなかった場合、問合せを編集するための問合せビルダーの起動時にエラーが発生する可能性があります。 問合せビルダーで問合せを解析できない場合は、テキスト・ボックスでステートメントを直接編集できます。
カスタマイズされた、または高度な問合せを、クエリー・ビルダーを使用して編集することはできません。
保存された問合せを編集するには:
- SQLデータセットを選択します。
- ツールバーで、「選択したデータセットの編集」をクリックして「データセットの編集」ダイアログを起動します。
- 「問合せビルダー」をクリックして、問合せをクエリー・ビルダーにロードします。
- 問合せを編集し、「保存」をクリックします。
問合せへのバインド変数の追加
問合せの作成後、結果を制限するためにユーザーにパラメータを渡せる場合があります。
バインド変数を問合せに追加するには:
SQL問合せへの字句参照の追加
字句参照を使用すると、SELECT、FROM、WHERE、GROUP BY、ORDER BYまたはHAVINGの後の句を置換できます。
実行時にパラメータにより複数の値を置換する場合、字句参照を使用します。 字句参照を使用して、問合せにフレックスフィールドを含めることもできます。 字句参照は、Fusion Applications Suite内のアプリケーションに対する問合せでのみサポートされます。
次の構文を使用して、SQL問合せに字句参照を作成します:
¶metername
- 問合せを作成する前に、問合せの字句参照ごとにPL/SQLデフォルト・パッケージにパラメータを定義します。 データ・エンジンは、これらの値を使用して字句パラメータを置き換えます。
- データ・モデル・エディタのプロパティ・ページで、「Oracle DBデフォルト・パッケージ」を指定します。
- データ・モデル・エディタで、PL/SQLパッケージを呼び出す「データの前」イベント・トリガーを作成します。
- 字句参照を含むSQL問合せを作成します。
- OKをクリックしてSQL問合せを閉じると、パラメータの入力を求められます。
たとえば、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サーバーに対するSQL問合せの定義について
このトピックでは、「Oracle BIサーバー」に対してSQL問合せを定義するときに覚えておく必要がある点について説明します。
Oracle BIサーバーに対してデータセットを作成する場合は、次の点に注意してください:
-
SQLデータ・エディタまたはクエリー・ビルダーを使用して「Oracle BIサーバー」に対してSQL問合せを作成すると、他のデータベース・ソースと同様に物理SQLではなく、論理SQLが生成されます。
-
階層列はサポートされていません。 最も高いレベルが常に返されます。
-
サブジェクト領域内では、表間の結合条件がすでに作成されているため、問合せビルダーで結合を作成する必要はありません。 問合せビルダーでは主キーは公開されません。
データ・モデル・エディタの「リンクの作成」関数を使用して、データセットをリンクできます。 「要素レベル・リンクの作成」を参照してください。 「Oracle BIサーバー」から作成されたデータセットの場合、1つのデータ・モデルに対して2つの要素レベルのリンクの制限があります。
-
クエリー・ビルダーでは、「条件」タブに表示される関数「ソート順」および「分類方法」は、「Oracle BIサーバー」に対する問合せではサポートされていません。 ソート順を入力するか、グループ化基準チェック・ボックスを選択すると、クエリー・ビルダーによってSQLが作成され、「パブリッシャ」 SQL問合せテキスト・ボックスに書き込まれますが、データセット・ダイアログを閉じようとすると、問合せは検証に失敗します。
SQL問合せによって取得されたデータにグループ化を適用するには、かわりにデータ・モデル・エディタの「グループ化基準」関数を使用できます。 「サブグループの作成」を参照してください。
-
「Oracle BIサーバー」にパラメータを渡し、すべて選択可能でNULL値が渡されましたを選択した場合は、問合せでNULL値を処理してください。
Oracle Fusion Cloud Applications表に対する問合せに関するノート
Oracle Fusion Cloud Applications表に対する問合せを記述する場合は、Oracle Fusion Cloud Applications顧客に特別な考慮事項が適用されます
-
to_char(sysdate,"mon")を使用してsysdateから月名を返すことはできません。 この関数は月番号を返します。 月名を表示するには、次のソリューションのいずれかを使用します:-
次の構文を使用して、レイアウトの日付フィールドを書式設定 :
<?format_date:fieldname;MASK)?> -
月番号に基づく月名を表示するには、レイアウトで次の構文を使用します:
<?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)11月を返します(現在の
SYSTDATEが11月の場合)
-