SQL: Create Customized Column Variable In SELECT-CodePudding

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!

CodePudding user response：

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:

PROJECT	PERCENTAGE
a	100%
b	58%
c	0%

fiddle

CodePudding user response：

Add a virtual column:

ALTER TABLE table_name
  ADD customized_value NUMBER GENERATED ALWAYS AS (100 - round(value, 5) * 100);

Which, if you have the table:

CREATE TABLE table_name (value) AS
SELECT 0.00 FROM DUAL UNION ALL
SELECT 0.42 FROM DUAL UNION ALL
SELECT 1.00 FROM DUAL;

Then, after you have added the column, when you query the table:

SELECT * FROM table_name;

The output is:

VALUE	CUSTOMIZED_VALUE
0	100
.42	58
1	0

And you can use CUSTOMIZED_VALUE in other, more complicated, expressions.

fiddle