SQLビルダを利用したクエリ作成例

この CookBook では、 intra-mart Accel Platform 2020 Spring から利用可能になった、ViewCreator の「SQLビルダ」機能を活用し、既存のクエリ編集では表現することができなかったクエリ(SQL)の作成例を紹介します。

SQLビルダの詳細については下記ドキュメントを参照してください。

ViewCreator 管理者操作ガイド - 認可の設定
ViewCreator 管理者操作ガイド - SQLビルダによるクエリの作成

完成イメージ

テーブルに登録されている情報

上記テーブルを参照して、年齢が同じ人の名前を一列に表示するクエリを作成し、データ参照のリスト集計を表示した例

レシピ

サンプルデータの用意
クエリビルダで自己参照SQLクエリを作成してみる
SQLビルダで自己参照SQLを記述
作成したクエリからデータ参照作成を開く

1. サンプルデータの用意

まずは完成イメージにあるテーブルを用意します。

テナント管理者でログインしてください。

グローバルナビから「サイトマップ」に遷移し、「テナント管理」→「データベース操作」をクリックしてください。
ここに、以下の SQL (DDL) を入力し、実行してください。

※ 以下は PostgreSQL での SQL です。他データベースをご利用の場合は、別途テーブルを作成する SQL を実行してください。

CREATE TABLE cb187248(
  id integer NOT NULL,
  name varchar(20),
  age integer,
  PRIMARY KEY (id)
);

CREATE TABLE cb187248(

id integer NOT NULL,

name varchar(20),

age integer,

PRIMARY KEY (id)

);

上記 SQL (DDL) を実行した後に、以下の SQL (INSERT文) を入力し、実行してください。

※ 以下は PostgreSQL での SQL です。他データベースをご利用の場合は、別途 SQL を実行してください。

INSERT INTO cb187248 VALUES(001,'aoyagi',23);
INSERT INTO cb187248 VALUES(002,'ueda',35);
INSERT INTO cb187248 VALUES(003,'edano',28);
INSERT INTO cb187248 VALUES(004,'kashiwagi',23);
INSERT INTO cb187248 VALUES(005,'satou',35);

INSERT INTO cb187248 VALUES(001,'aoyagi',23);

INSERT INTO cb187248 VALUES(002,'ueda',35);

INSERT INTO cb187248 VALUES(003,'edano',28);

INSERT INTO cb187248 VALUES(004,'kashiwagi',23);

INSERT INTO cb187248 VALUES(005,'satou',35);

2. クエリビルダで自己参照するクエリを作成してみる

はじめに既存のクエリビルダで、自己参照するクエリ作成を試してみましょう。
目標としては、テーブルに登録されている年齢が同じ人の名前を一列で取得するようなクエリを作成することです。

サイトマップから「ViewCreator」 -> 「クエリ編集」 -> 「新規」をクリックしてください。

「cb187248」テーブルをダブルクリックして、テーブルを追加してください。

名前と年齢を取得するために、「cb187248」テーブルの「name」,「age」をダブルクリックしカラム一覧に追加してください。

「cb187248」テーブルに対して、自己参照を行うために、もう一つ同じテーブルを追加する必要があります。
もう一度「cb187248」テーブルをダブルクリックして、テーブルを追加してください。

同一テーブルを追加する場合、本来であれば別名を付ける必要がありますが、クエリビルダではそのような機能を提供していないため、同一テーブルの追加をすることができないようになっています。

このように、クエリビルダでは自己参照するクエリを作成することができませんでした。
ここからは、SQLビルダを使い実現してみましょう。
クエリビルダからSQLビルダへ切り替える場合、途中まで作成していた情報はSQLとして保持されますので、この画面のまま下記の操作を続けてみてください。

3. SQLビルダで自己参照SQLを記述

ツールバーにある「SQLで編集」をクリックしてください。

SQLビルダに切り替える直前のクエリビルダで作成していた内容が、SQLとして初期表示されます。

画面中央のエディタ部分に、下記のSQLを参考に初期表示されたSQLを編集してください。

SELECT
  customer1.name AS name1,
  customer2.name AS name2,
  customer2.age AS age
FROM
  cb187248 customer1 INNER JOIN
  cb187248 customer2 ON
  customer1.age = customer2.age AND
  customer1.id < customer2.id

SELECT

customer1.name AS name1,

customer2.name AS name2,

customer2.age AS age

FROM

cb187248 customer1 INNER JOIN

cb187248 customer2 ON

customer1.age = customer2.age AND

customer1.id < customer2.id

「カラム一覧に反映」ボタンをクリックしてください。

キャプション名を自由に編集し、ツールバーの保存をクリックしてください。

4. 作成したクエリからデータ参照作成を開く

サイトマップから「ViewCreator」 -> 「クエリ編集」をクリックしてください。
手順2で保存したクエリ名の「データ参照作成」列にある「リスト集計を作成する」アイコンをクリックしてください。

「プレビュー」をクリックすると、年齢が同じ人の名前を一列に表示するSQLをクエリとして保存できたことが確認できます。

おわりに

今回紹介したように、SQLビルダを利用することで自己参照を使ったSQLを記述し、ViewCreator のクエリとして登録することができます。（他にヒント句やUNION句などについても、SQLビルダを利用することで記述できます。）
クエリとして登録した後は、既存のクエリと同じ要領でデータ参照を作成できますので、ぜひ運用に合わせて活用してみてください。