I would like to know if there is a way in SQL to automatically
multiply a column by the contents of another column. Here is the
background information:
I have the following tables which store engineering test data:
SQL> desc SCALARS
Name Null? Type
------------------------------- -------- ----
ID NOT NULL NUMBER(7)
SCALAR_VAL NOT NULL NUMBER(16,8)
DATTYPE_ID NOT NULL NUMBER(4)
SQL> desc DATA_TYPES
Name Null? Type
------------------------------- -------- ----
ID NOT NULL NUMBER(4)
NAME NOT NULL CHAR(30)
UNITS_ID NUMBER(4)
SQL> desc UNITS
Name Null? Type
------------------------------- -------- ----
ID NOT NULL NUMBER(4)
NAME NOT NULL CHAR(20)
ENGLISH_NAME NOT NULL CHAR(20)
OFFSET NOT NULL NUMBER(16,8)
SLOPE NOT NULL NUMBER(16,8)
The SCALARS table contains data values and has the column DATTYPE_ID
as a foreign key which refers to the DATA_TYPES table. Data is stored
in the SCALAR_VAL column in metric units.
The DATA_TYPES table tells what type of data is stored in the SCALARS
table -- e.g. barometric pressure. It has a foreign key UNITS_ID which
refers to the UNITS table.
The UNITS table tells what units the data is stored in -- e.g.
kilopascals. It also has an alternate name for English units and slope
and offset for converting from metric to English.
Is there a way that I can write a query such that the data is
displayed in English units? I would like to write:
select scalar_val * (select slope
from units
where units.id = ...)
from scalars;
Where the ... is some expression to join the three tables.
The problem is I don't think you can multiply by a select statement.
Any suggestions?
Thanks in advance.
--
Hugh Fader
hugh_at_slee01.srl.ford.com
