Use column references in Oracle SQL select query to avoid re-calculation

I want to create a summarised database backup report, which includes data from multiple database tables which has no relation to each other. For example, I want to include the name of the database from v$database view:

SQL> Select name from v$database;

And the size of the database:

SQL> SELECT sum(bytes) FROM v$datafile;

And consider there is no common column between those two views.

Beside that I want to include the database size in Bytes, KBs, MBs ,……, and so on.

I am using the following query:

SQL> SELECT 
        "Database", "DB_SIZE_IN_Bytes", "DB_SIZE_IN_KBs", "DB_SIZE_IN_MBs", 
        "DB_SIZE_IN_GBs", "DB_SIZE_IN_TBs"
FROM (
    SELECT 
           (SELECT name FROM v$database ) "Database",
           (SELECT sum(bytes) FROM v$datafile ) "DB_SIZE_IN_Bytes",
           (SELECT sum(bytes)/1024 FROM v$datafile ) "DB_SIZE_IN_KBs",
           (SELECT sum(bytes)/1024/1024 FROM v$datafile ) "DB_SIZE_IN_MBs",
           (SELECT sum(bytes)/1024/1024/1024 FROM v$datafile ) "DB_SIZE_IN_GBs",
           (SELECT sum(bytes)/1024/1024/1024/1024 FROM v$datafile ) "DB_SIZE_IN_TBs"
    FROM dual
    );

In this way I am avoiding the join condition between v$database and v$datafile.

This is just an example, however, in my project I have more than 10 tables. Most of them without a common column.

The question is while converting bytes into kbs, mbs, and so on, I want to use the column references such as:

(SELECT DB_SIZE_IN_Bytes/1024 FROM dual ) "DB_SIZE_IN_KBs",
(SELECT DB_SIZE_IN_KBs /1024 FROM dual ) "DB_SIZE_IN_MBs",
(SELECT DB_SIZE_IN_MBs /1024 FROM dual ) "DB_SIZE_IN_GBs",
(SELECT DB_SIZE_IN_GBs /1024 FROM dual ) "DB_SIZE_IN_TBs"

instead of again and again calculations. Is there any way to use the column references such as mentioned above in the example instead of re-calculation?

Answer

Use a subquery to calculate the results of the sum once in the from clause and then use that:

SELECT (SELECT name FROM v$database ) as "Database",
        DB_SIZE_IN_Bytes,
        DB_SIZE_IN_Bytes / 1024 as DB_SIZE_IN_KBs,
        DB_SIZE_IN_Bytes / (1024 * 1024) as DB_SIZE_IN_MBs,
        DB_SIZE_IN_Bytes / (1024 * 1024 * 1024) as DB_SIZE_IN_GBs,
        DB_SIZE_IN_Bytes / (1024 * 1024 * 1024 * 1024) as DB_SIZE_IN_TBs
FROM (SELECT sum(bytes) as DB_SIZE_IN_Bytes FROM v$datafile ) x;

You can also do this use lateral joins, but it is definitely overkill for this particular example:

SELECT (SELECT name FROM v$database ) as "Database",
        bb.*, bk.*, bm.*, bg.*, bt.*
FROM (SELECT sum(bytes) as DB_SIZE_IN_Bytes FROM v$datafile
     ) bb CROSS JOIN LATERAL
     (SELECT bb.DB_SIZE_IN_Bytes / 1024 as DB_SIZE_IN_Kb
      FROM dual
     ) bk CROSS JOIN LATERAL
     (SELECT bk.DB_SIZE_IN_Kb / 1024 as DB_SIZE_IN_Mb
      FROM dual
     ) bm CROSS JOIN LATERAL
     (SELECT bm.DB_SIZE_IN_Mb / 1024 as DB_SIZE_IN_Gb
      FROM dual
     ) bg CROSS JOIN LATERAL
     (SELECT bb.DB_SIZE_IN_Gb / 1024 as DB_SIZE_IN_Tb
      FROM dual
     ) t;