Trying to look for better way to store customized column (that called many times in queries) in SQL variable.

I’m using Oracle and I execute this query:

SELECT project, 
( CASE 
WHEN TO_CHAR((100 - round(value, 5) * 100)) NOT LIKE ‘.%’ 
THEN (100 - round(value, 5) * 100) || ‘%’ 
ELSE ‘0’ || (100 - round(value, 5) * 100) || ‘%’ 
END ) AS percentage 
FROM table;

The customized column: 100 - round(value, 5) is called 3 times. In addition, That customized column called in many other SELECT queries in my project. Is there any option to save that customized column as a variable and call it in shorter way?

For example, I would like to do something like that:

SELECT project, 
( CASE 
WHEN TO_CHAR(CUSTOMIZED_VALUE) NOT LIKE ‘.%’ 
THEN CUSTOMIZED_VALUE || ‘%’ 
ELSE ‘0’ || CUSTOMIZED_VALUE || ‘%’ 
END ) AS percentage 
FROM table;

Table Columns:

|  Column  |     Type    |
|:—————————|————————————:|
| project  |VARCHAR2(100)|
|  value   |  FLOAT(*)   |

When value is always between 0 to 1.

Thank you for your help!

>Solution :

You can define it in a sub-query (or a sub-query factoring clause) and then use it in an outer query:

SELECT project, 
       TO_CHAR(CUSTOMIZED_VALUE, 'FM990') || '%' AS percentage 
FROM (
  SELECT t.*,
         100 - round(value, 5) * 100 AS customized_value
  FROM   table_name t
);

or:

WITH subquery_factoring_clause (project, value, customized_value) AS (
  SELECT t.*,
         100 - round(value, 5) * 100 AS customized_value
  FROM   table_name t
)
SELECT project, 
       TO_CHAR(CUSTOMIZED_VALUE, 'FM990') || '%' AS percentage 
FROM   subquery_factoring_clause;

Which, for the sample data:

CREATE TABLE table_name (project, value) AS
SELECT 'a', 0.00 FROM DUAL UNION ALL
SELECT 'b', 0.42 FROM DUAL UNION ALL
SELECT 'c', 1.00 FROM DUAL;

Outputs:

fiddle