Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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

share|improve this answer
    
Will have to check out using this with the INTO for PL/SQL variables on Monday. I should have realized that the subquery refactoring/CTE syntax would still work on Oracle. –  Cade Roux Jun 30 '12 at 23:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.