SQL問合せを使用したデータ・セットの作成
このトピックでは、SQL問合せを使用してデータ・セットを作成する方法について説明します。
SQLクエリー・ビルダーの使用
コーディングせずにSQL問合せを作成するには、クエリー・ビルダーを使用します。クエリー・ビルダーを使用すると、SQLに関する最小限の知識で、データベース・オブジェクトの検索とフィルタ処理、オブジェクトと列の選択、オブジェクト間の関連の作成、および書式設定された問合せ結果の表示を行うことができます。
この項では、クエリー・ビルダーの使用方法について説明します。この項の内容は次のとおりです。
クエリー・ビルダーの概要
「クエリー・ビルダー」ページは、「オブジェクト選択」ペインと設計と出力ペインに分かれています。
-
「オブジェクト選択」ペインには、問合せの作成に使用できるオブジェクトのリストが表示されます。現行スキーマのオブジェクトのみが表示されます。
-
設計と出力ペインは、次の4つのタブで構成されています。
-
モデル - 「オブジェクト選択」ペインから選択したオブジェクトが表示されます。
-
条件 - 選択した列に条件を適用できます。
-
SQL - 問合せが表示されます。
-
結果 - 問合せの結果が表示されます。
-
サポートされている列タイプ
すべてのタイプの列がオブジェクトとして「設計」ペインに表示されます。各問合せで選択できる列の数は60以下です。
| サポートされている列タイプ | 制限事項 |
|---|---|
| VARCHAR2、CHAR | 該当なし |
| NUMBER | 該当なし |
| DATE、TIMESTAMP | TIMESTAMP WITH LOCAL TIMEZONEデータ型はサポートされていません。
|
| バイナリ・ラージ・オブジェクト(Binary Large Object: BLOB) | BLOBは、イメージ、テキストまたはXMLデータになります。クエリー・ビルダーで問合せを実行すると、BLOBは「結果」ペインに表示されませんが、問合せはデータ・モデル・エディタに保存されるときに正しく作成されます。BIJDBCドライバの制限により、BLOBデータはOracle BI EEデータ・ソースではサポートされていません。
イメージ・データ型でBLOBデータ列を使用する場合は、RTFテンプレートを使用します。 |
| キャラクタ・ラージ・オブジェクト(CLOB) | Publisherでは、Oracle Analytics Serverデータソースでの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の後にくる句を置換できます。
実行時にパラメータで複数の値を置換する場合は字句参照を使用します。字句参照を使用してフレックスフィールドを問合せに組み込めることもできます。字句参照がサポートされるのは、Oracleアプリケーションに対する問合せのみです。
SQL問合せの中に字句参照を作成するには、次の構文を使用します。
¶metername
- 問合せを作成する前に、問合せの字句参照ごとに、PL/SQLデフォルト・パッケージでパラメータを定義します。データ・エンジンでは、これらの値を使用して字句パラメータを置換します。
- データ・モデル・エディタの「プロパティ」ページで、「Oracle DBデフォルト・パッケージ」を指定します。
- データ・モデル・エディタで、「データの前」イベント・トリガーを作成し、このPL/SQLパッケージをコールするように設定します。
- 字句参照が含まれるSQL問合せを作成します。
- SQL問合せを閉じるために「OK」をクリックすると、パラメータを入力するための画面が表示されます。
たとえば、パッケージを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データ・エディタまたはクエリー・ビルダーを使用して、SQL問合せをOracle BIサーバーに対して作成するときは、論理SQLが生成されます。他のデータベース・ソースの場合とは異なり、物理SQLは生成されません。
-
階層列はサポートされていません。最も上のレベルが常に返されます。
-
サブジェクト領域内では、表間の結合条件がすでに作成されています。したがって、クエリー・ビルダーで結合を作成する必要はありません。クエリー・ビルダーによって主キーが公開されることはありません。
データ・モデル・エディタの「リンクの作成」機能を使用して、データ・セットをリンクできます。要素レベルのリンクの作成を参照してください。Oracle BIサーバーから作成したデータ・セットの場合、1つのデータ・モデルに対して2つの要素レベルのリンクという制限があります。
-
クエリー・ビルダーの「条件」タブに表示される機能のうち、「ソート順序」と「グループ基準」は、Oracle BIサーバーに対する問合せの場合はサポートされません。「ソート順序」に入力した場合や「グループ基準」チェック・ボックスを選択した場合、クエリー・ビルダーによってSQLが作成されてPublisherの「SQL問合せ」テキスト・ボックスに出力されますが、「データ・セット」ダイアログを閉じようとすると、問合せは検証に失敗します。
SQL問合せで取り出したデータをグループ化するには、データ・モデル・エディタの「グループ基準」機能をかわりに使用してください。サブグループの作成を参照してください。
-
パラメータをOracle BIサーバーに渡すとともに、「全選択可能」で「NULL値が渡されました」を選択した場合は、問合せの中で確実にnull値を処理してください。
Oracle Fusionアプリケーション表に対する問合せのノート
Oracle Fusionアプリケーション表に対する問合せを書く際にOracle Fusionアプリケーション顧客に対する特別な考慮事項が適用されます
-
月の名前を
sysdateからto_char(sysdate,"mon")を使用して返すことはできません。この関数は月の番号を返します。月の名前を表示するには、次のいずれかのソリューションを使用します:-
レイアウトの日付フィールドを、次の構文を使用して書式設定します:
<?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)Novが返されます(現在の
SYSTDATEがNovemberの場合)
-