Re: Multiply one column by another?

In article <BwwJ6E.L94_at_fmsrl7.srl.ford.com>, hugh_at_slee06.srl.ford.com (Hugh Fader) writes:
> 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
>

-- 

select t1.scaler_val * t2.slope
 from scalers t1, units t2
where t2.id = ...

================================================================================
Scott Stephens				inet:	sstephen.us.oracle.com
Oracle WorldWide Support                Redwood City, California