CODEWITHSUNDEEP
sqloracle11g
dontHaveName
dontHaveName

Reputation: 1937

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;

Upvotes: 0

Views: 32

Answers (3)

Serg
Serg

Reputation: 22811

A set of scalar variables can be introduced as a one row query which you then join with all the rest query. 

WITH x AS
( SELECT 
   (SELECT some_value FROM other_table) as a1, 
   (SELECT some_value FROM other_table2) as a2
  FROM dual
)
SELECT t.*, x.a1 + x.a2 , x.a1 * x.a2
FROM some_table t
CROSS JOIN x;

Upvotes: 0

GavinCattell
GavinCattell

Reputation: 3963

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

Upvotes: 1

juergen d
juergen d

Reputation: 204746

Try

SELECT t.*,
       a1.some_value + a2.some_value,
       a1.some_value / a2.some_value 
FROM some_table t
cross join (SELECT some_value FROM other_table) as a1
cross join (SELECT some_value FROM other_table2) as a2

Upvotes: 0

Related Questions