Select constants as pseudo columns and do arithmetic on pseudo columns

Question

I want to do something like this:

select 999 as price, 0.1 as tax_rate, 
price*tax_rate as tax_amount, tax_amount+price as total
from Dual;

on an Oracle database.

Answer 1

You could use CROSS APPLY(Oracle 12c):

select price, tax_rate, tax_amount, tax_amount+price as total
from Dual
CROSS APPLY (SELECT 999 AS price, 0.1 AS tax_rate FROM dual) s
CROSS APPLY (SELECT price*tax_rate AS tax_amount FROM dual) s2;

select price, tax_rate, tax_amount, tax_amount+price as total
from Dual
,LATERAL (SELECT 999 AS price, 0.1 AS tax_rate FROM dual) s
,LATERAL (SELECT price*tax_rate AS tax_amount FROM dual) s2;

db<>fiddle demo

You could not refer to defined columns at the same level(SELECT) but with CROSS APPLY/LATERAL JOIN you could create a chain of calculated columns without using subqueries.

Similar approach: PostgreSQL using a calculated column in the same query