dontHaveName dontHaveName - 6 months ago 9
SQL Question

sql working with alias from other select

I have this select:

SELECT
*,
(SELECT some_value FROM other_table) as a1,
(SELECT some_value FROM other_table2) as a2
FROM some_table;


Is there any way to use the values a1, a2 and work with them like this?

SELECT
*,
(SELECT some_value FROM other_table) as a1,
(SELECT some_value FROM other_table2) as a2,
a1+a2,
a1/a2
FROM some_table;


Those subqueries are quite big so it's not good if I have to do this:

SELECT
*,
(SELECT some_value FROM other_table) as a1,
(SELECT some_value FROM other_table2) as a2,
(SELECT some_value FROM other_table)+(SELECT some_value FROM other_table),
SELECT some_value FROM other_table)/(SELECT some_value FROM other_table2)
FROM some_table;

Answer

Use WITH in Oracle. It's very helpful. Link

WITH a1 AS (SELECT some_value FROM other_table),
a2 AS (SELECT some_value FROM other_table2)
SELECT *
FROM some_table t
JOIN a1 ON a1.key = t.key
JOIN a2 ON a2.key = t.key
Comments