Don't Perform the Same Calculation Over and Over : Select clause « Query Select « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Query Select
  3. Select clause
SQL>
SQL> CREATE TABLE contract
  2  (income   INTEGER
  3  ,overhead INTEGER);

Table created.

SQL>
SQL>
SQL> INSERT INTO contract VALUES (1000,20);

1 row created.

SQL> INSERT INTO contract VALUES (2000,10);

1 row created.

SQL> INSERT INTO contract VALUES (1000,50);

1 row created.

SQL>
SQL> SELECT income,
  2         overhead,
  3         (income-income*overhead/100) AS residual,
  4         0.20*(income-income*overhead/100) AS Est,
  5         0.10*(income-income*overhead/100) AS Admin,
  6         0.05*(income-income*overhead/100) AS Rsrv
  7    FROM contract;

    INCOME   OVERHEAD   RESIDUAL        EST      ADMIN       RSRV
---------- ---------- ---------- ---------- ---------- ----------
      1000         20        800        160         80         40
      2000         10       1800        360        180         90
      1000         50        500        100         50         25

SQL> SELECT income,
  2         overhead,
  3         residual,
  4         0.20*residual AS Est,
  5         0.10*residual AS Admin,
  6         0.05*residual AS Rsrv
  7    FROM
  8     (SELECT income, overhead, (income-income*overhead/100) AS residual
  9        FROM contract) subquery;

    INCOME   OVERHEAD   RESIDUAL        EST      ADMIN       RSRV
---------- ---------- ---------- ---------- ---------- ----------
      1000         20        800        160         80         40
      2000         10       1800        360        180         90
      1000         50        500        100         50         25

SQL> CREATE VIEW residual1 AS
  2    SELECT income, overhead, (income-income*overhead/100) AS residual
  3      FROM contract;

View created.

SQL>
SQL> SELECT income,
  2         overhead,
  3         residual,
  4         0.20*residual AS Est,
  5         0.10*residual AS Admin,
  6         0.05*residual AS Rsrv
  7    FROM residual1;

    INCOME   OVERHEAD   RESIDUAL        EST      ADMIN       RSRV
---------- ---------- ---------- ---------- ---------- ----------
      1000         20        800        160         80         40
      2000         10       1800        360        180         90
      1000         50        500        100         50         25

SQL> SELECT subquery.*,
  2         0.20*residual AS Est,
  3         0.10*residual AS Admin,
  4         0.05*residual AS Rsrv
  5    FROM
  6     (SELECT contract.*, (income-income*overhead/100) AS residual
  7        FROM contract) subquery;

    INCOME   OVERHEAD   RESIDUAL        EST      ADMIN       RSRV
---------- ---------- ---------- ---------- ---------- ----------
      1000         20        800        160         80         40
      2000         10       1800        360        180         90
      1000         50        500        100         50         25

SQL>
SQL> DROP VIEW residual1;

View dropped.

SQL> DROP TABLE contract;

Table dropped.

SQL>
SQL>








2.2.Select clause
2.2.1.Performing Single Table SELECT Statements
2.2.2.Select employee first and last and sort by last name
2.2.3.List single column from a table
2.2.4.Use as to specify the alias name
2.2.5.Use function in select clause
2.2.6.Use more than one aggregate functions in a select statement
2.2.7.where clause
2.2.8.Compare with number
2.2.9.Concatenate string
2.2.10.Select from a Subquery
2.2.11.Select constant as a column
2.2.12.Negate a column value
2.2.13.Math calculation in select statement
2.2.14.Search for String Across Columns
2.2.15.Don't Perform the Same Calculation Over and Over
2.2.16.NO_INDEX function in select statement