VALUE

The VALUE or COALESCE function (COALESCE is the keyword specified by the ANSI/ISO standard of 1992 as a synonym for the VALUE function of IBM SQL), by either name, can be particularly useful in join operations. Suppose, for example, that in the full outer join sample query below, the PARTSUPP table contained a nonmatched row entry of P9 in the PNO column:

SELECT part.pno, pname, qty
  FROM part FULL OUTER JOIN partsupp
  ON part.pno = partsupp.pno

In this case, the P9 entry would not appear in the first column of the result. The problem is that the PNO column is in the PARTSUPP table, and is not included in the SELECT list of the query. We can, however, capture the PNO column of the PARTSUPP table by using the VALUE (or COALESCE) function as shown in the following query:

SELECT VALUE(part.pno, partsupp.pno) AS
    partnum, pname, qty
  FROM part FULL OUTER JOIN partsupp
  ON part.pno = partsupp.pno

The VALUE clause forces the query to first search the PNO column in the PART table for result data; and if none is found, search the PNO column of the PARTSUPP table. In the example above, the AS clause (AS partnum) provides a synonym for the result of the VALUE function. (See AS clause in SELECT Clause).