Oracle subquery column aliasing syntax

oracleoracle-10g

I have something like:

SELECT a, b
INTO v_a, v_b
FROM (
    SELECT x, y
    FROM whatever
    UNION
    SELECT z, w
    FROM something
) AS subquery (a, b)

Which of course doesn't work in Oracle (that syntax – apart from the INTO and the optional AS) would be fine in SQL Server http://sqlfiddle.com/#!6/b7dcf/1

Is there an alternative to this (which requires me to put the aliasing inside) where the column aliasing is outside:

SELECT a, b
INTO v_a, v_b
FROM (
    SELECT x AS a, y AS b
    FROM whatever
    UNION
    SELECT z, w
    FROM something
) subquery;

http://sqlfiddle.com/#!6/c12e1/1 – which works on both SQL Server and Oracle, of course

My objection to the interior aliasing is that it is not as good for maintenance, obviously, if new sections are added above that section, the aliasing gets whacked.

Best Answer

Consider using the WITH syntax that has the subquery above the outer SELECT and has the better maintainability you were looking for: 

WITH subquery (a, b) AS 
(
    SELECT x, y FROM whatever
    UNION
    SELECT z, w FROM something
) 
SELECT a, b FROM subquery;

... runs on Oracle 11.2 according to: http://sqlfiddle.com/#!4/d0b42/2/0

Related Question