副照会を簡単に使いまわせますか？

質問

たとえば、次のSQLがあったとして、

select キー1, キー2, sum(金額)
(
　select A.キー1 , A.キー2 , A.金額
　　from (select * from ～ where ...) A
　union all
　select '' , A.キー2 , A.金額
　　from (select * from ～ where ...) A
　union all
　select A.キー1 , '' , A.金額
　　from (select * from ～ where ...) A
　union all
　select ''　　 , '' , A.金額
　　from (select * from ～ where ...) A
)
group by キー1, キー2

一番内側の副照会は、
(select * from ～ where ...) A
は、4箇所とも同じSQL文で、とても長いのですが
2箇所目以降は、1箇所目と同じSQL文ということで
記述を省けたりするような構文があったりしないでしょうか？

そこを、ビューにしろといわれるとその通りなのですが
ビューにしたくないです。（プログラムで動的にここの副照会部分を
パラメータに応じて作っているので、ビューにするとビューがいくつも
できて、きりがない）

Oracle9iで、利用しています。

回答No.3

k_o_r_o_c_h_a_n · ベストアンサー

SQL99における、共通表式 WITH句。
http://www.atmarkit.co.jp/fnetwork/tokusyuu/01sql99/sql99_1b.html
が判りやすい。

今回のケースは、CUBEが最適です。

ただし、CUBEを使わなくても、発想の転換で、
select キー1, キー2, sum(金額)
from
(
　select X,decode(X,1,A.キー1,2,null,3,A.キー1,null) キー1,
　　　　 decode(X,1,A.キー2,2,A.キー2,null) キー2, A.金額
　　from
　　　(select * from ～ where ...) A,
　　　(select 1 X from dummy union all select 2 from dummy union all
　　　 select 3 from dummy union all select 4 from dummy) B
)
group by X,キー1, キー2
に書き換え可能です。

CUBEで対応できないような事例であっても、必ずしも同じSQLを複数回書かなければならないとは限りません。
当然、書かざるおえない場合はありますが、創意工夫で解決できることもあります。

お礼

ありがとうございます。
WITH句が、まさに捜し求めていたものです。

ただし、今回は、ご指摘の通り、CUBEで行こうと思います。

なるほど、そんな書き換え方もあるんですね。
勉強になりました。

回答No.2

DRAGON_TAIL

質問に対しては一時表で実現できると答えられます。

ただ、unionを使用する場合、本当にそれが必要かどうか考えたほうが良いかと思います。

今回は単純で目的がクロス集計のようですから、

SELECT
A.キー1, A.キー2, sum(A.金額)
FROM (select * from ～ where ...) A
GROUP BY CUBE(A.キー1, A.キー2)

で済みます。

お礼

ご回答ありがとうございます。
CUBE便利そうですね。
試してみたいと思います。

一時表のほうも試してみたいと思います。

回答No.1

k_o_r_o_c_h_a_n

SQL99でサポートしているのですが、Oracle9iでは使えないハズです。

でも・・

同じ問い合わせを複数回実行しているわけで、サブクエリを一撃で決められるような気がしますので、
SQL文がｎ倍長くて動作の遅いものになるのは防げるように思いますよ。

補足

ご回答ありがとうございます。

>>SQL99でサポートしているのですが、Oracle9iでは使えないハズです。

これが、気になります。
なんのことを指しているのか、教えていただけませんでしょうか？