In the outer join example previously shown, when there is no matching ORDERS row, the null value is returned for T2.AMOUNT. This is true even if the AMOUNT column was defined as NOT NULL.
If there is a default value you wish to have returned when a value is null, you can use the VALUE function, which returns the first non-null value in its argument list. In this case, SUM(T2.AMOUNT) is returned if there is a matching row. Zero is returned when:
The phrase CUSTOMER T1 LEFT OUTER JOIN ORDERS T2 is called a joined table. A joined table can be used with other simple table references in the FROM list. For example, if you want to also report from the LINE_ITEM table:
SELECT T1.NAME, VALUE (SUM(T2.AMOUNT), 0), COUNT(DISTINCT T3.ORDNO)
FROM CUSTOMER T1 LEFT OUTER JOIN ORDERS T2
ON T1.CUSTNO = T2.CUSTNO,
LINE_ITEM T3
WHERE T2.ORDNO = T3.ORDNO
GROUP BY T1.NAME;
Alternately you can use the joined table syntax by replacing the right operand with an INNER JOIN:
SELECT T1.NAME, VALUE (SUM(T2.AMOUNT), 0), COUNT(DISTINCT T3.ORDNO)
FROM CUSTOMER T1 LEFT JOIN (ORDERS T2 INNER JOIN LINE_ITEM T3
ON T2.ORDNO = T3.ORDNO)
ON T1.CUSTNO = T2.CUSTNO
GROUP BY T1.NAME;
In this example, note the following:
|
Copyright © 2014 CA.
All rights reserved.
|
|