単一の照会のためだけに視点の定義が必要となる場合には、 表式 を使うことができます。
表式は一時的なものであり、それが有効なのはその SQL ステートメントの存続期間だけです。 表式は視点と違って共有できませんが、視点よりも柔軟性があります。
ここでは、照会の中での共通表式およびネストされた表式の使い方について説明します。
ネストされた表式は、メインの照会の FROM 文節の中で定義がネストされている (直接定義されている) 一時視点です。
次の照会では、 ネストされた表式を使うことによって、 教育レベル (EDLEVEL) が 16 を超える人について、 所得合計 (TOTAL_PAY) の平均、教育レベル、 および採用年 (HIREYEAR) を調べています。
SELECT EDLEVEL, HIREYEAR, DECIMAL(AVG(TOTAL_PAY),7,2) FROM (SELECT EDLEVEL, YEAR(HIREDATE) AS HIREYEAR, SALARY+BONUS+COMM AS TOTAL_PAY FROM EMPLOYEE WHERE EDLEVEL > 16) AS PAY_LEVEL GROUP BY EDLEVEL, HIREYEAR ORDER BY EDLEVEL, HIREYEAR
結果は、次のとおりです。
EDLEVEL HIREYEAR 3 ------- ----------- --------- 17 1967 28850.00 17 1973 23547.00 17 1977 24430.00 17 1979 25896.50 18 1965 57970.00 18 1968 32827.00 18 1973 45350.00 18 1976 31294.00 19 1958 51120.00 20 1975 42110.00
この照会では、ネストした表式を使うことによって、 まず HIREDATE 列から採用年を抽出し、 次にそれを GROUP BY 文節で使うようにしています。 EDLEVEL の値をいろいろに変えて同様の照会を実行しようとする場合、 これを視点として作成しないほうがよいでしょう。
この例では、スカラー組み込み関数 DECIMAL が使われています。 DECIMAL は、数値または文字ストリングを 10 進数として戻します。 関数については、 SQL 解説書 を参照してください。
共通表式 は、 複雑な照会で一貫して使用するために作成する表式です。 照会の最初に、WITH 文節を使って定義し、名前を付けます。 共通表式を繰り返して参照した場合、 同じ結果セットが使われます。 これに対し、もしネストされた表式や視点を使うとすれば、 いちいち結果セットを生成し直すことになり、 生成するたびにその結果が違うという可能性も出てきます。
次の例では、全社員の中で、教育レベルが 16 を超える人のうち、 同じ年に採用された同じ教育レベルの人の平均所得を下回っている人のリストを出力します。 この照会の各部分について、この後さらに詳しく説明します。
(1)
WITH PAYLEVEL AS (SELECT EMPNO, EDLEVEL, YEAR(HIREDATE) AS HIREYEAR, SALARY+BONUS+COMM AS TOTAL_PAY FROM EMPLOYEE WHERE EDLEVEL > 16),
(2)
PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS (SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY) FROM PAYLEVEL GROUP BY EDLEVEL, HIREYEAR)
(3)
SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, DECIMAL(AVG_TOTAL_PAY,7,2) FROM PAYLEVEL, PAYBYED WHERE EDLEVEL = EDUC_LEVEL AND HIREYEAR = YEAR_OF_HIRE AND TOTAL_PAY < AVG_TOTAL_PAY
最終的な結果は次のとおりです。
EMPNO EDLEVEL YEAR_OF_HIRE TOTAL_PAY 5 ------ ------- ------------ ------------- --------- 000210 17 1979 20132.00 25896.50