社内SEの徒然なる日記

元が1つ集計単位が違うデータの1対多結合の1回のSQLで実行!(ORACLE)の内情

■ ListCreator

前回(元が1つ集計単位が違うデータの1対多結合の1回のSQLで実行!(ORACLE))は、分析関数まで使ってデータを作ったのですが、この目的はListCreatorという帳票作成ソフトで作った帳票の入力データを作ることでした。
集計単位が違うSQLサンプル - 7

ListCreatorでは、このように1つの帳票の中で複数の明細行を作りたいのであれば、フリーフレームって形式で作るのが正しいみたいです。

ただ、あれは帳票の設計が面倒だし、明細行の数が多くて改ページした時の出力結果が気に入りません。そこで、集計表っていう形式で作ることにしたのですが、この形式では明細行を1つしか持てません。

そこで、内部テーブル「枝番&支店」と「支店」の2つを連番で結合して、無理やり1行のデータ(明細行)になるようにしたのです。

■ SQLの見直し

前回のSQLで少し気になるのは、内部テーブル「枝番支店」と「支店」を連番で結合している部分ですかね。

前回の条件だと、「支店」.連番が常に枝番支店に存在するので良いのですが、例えば枝番支店の集計条件から支店を除いた場合、この関係が崩れます。

・・・うーん、内部テーブルの部分をWITH句で作って、枝番支店と支店から管理番号と連番のみ取得して、UNIONで統合。これで結合した方がスマートかな?

SELECT
WITH
-- 内部テーブル「枝番&支店」
V_枝番支店 AS
(
SELECT
管理番号,枝番,支店,金額 ,
ROW_NUMBER ()
OVER (
PARTITION BY 管理番号
ORDER BY 枝番,支店
) AS 連番
FROM
(
SELECT
管理番号,枝番,支店,SUM(金額) AS 金額
FROM 明細
GROUP BY
管理番号,枝番,支店
)
)
-- 内部テーブル「支店」
, V_支店 AS (
SELECT
管理番号,支店,金額 ,
ROW_NUMBER ()
OVER (
PARTITION BY 管理番号
ORDER BY 支店
) AS 連番
FROM
(
SELECT
管理番号,支店,SUM(金額) AS 金額
FROM 明細
GROUP BY
管理番号,枝番,支店
)
)
-- V_連番
, 連番 AS (
SELECT 管理番号,連番
FROM V_枝番支店
UNION
SELECT 管理番号,連番
FROM V_支店
)
SELECT
管理.管理番号
,枝番支店.連番
,枝番支店.枝番
,枝番支店.支店
,枝番支店.金額
,支店.支店
,支店.金額
FROM
管理
,V_連番
,V_枝番支店 AS 枝番支店
,V_支店 AS 支店
WHERE
管理.管理番号 = V_連番.管理番号
AND V_連番.管理番号 = 枝番支店.管理番号
AND V_連番.連番 = 枝番支店.連番
AND V_連番.管理番号 = 支店.管理番号
AND V_連番.連番 = 支店.連番
ORDER BY
管理.管理番号
,V_連番.連番


・・・うん、こっちの方が綺麗ですね。

■ 後書き

ListCreatorって富士通製の帳票作成ソフトなのですが、どうも妙な癖があって取っ付きにくい。

帳票レイアウト、帳票上の各項目と関連付けられた入力データ。ここまでは難しくないのですが、問題はここからです。

入力データを使用して帳票の動作(文字編集、集計、四則演算など)を設定する部分の定義が理解しきれない。単純に上から下にって感じじゃないんですよね。

この会社の製品は機能的には不足を感じないのですが、運用しようとすると苦戦することが多くて困ります。

前回:元が1つ集計単位が違うデータの1対多結合の1回のSQLで実行!(ORACLE)

最新の記事:http://harikofu.blog.fc2.com/
投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end — スポンサードリンク

PageTop

コメント


管理者にだけ表示を許可する