function based on value in another field

[Ian]

I'm trying to create a calculated value (ToUREV). Two fields need to be
multiplied; CONSUMPTION (from DATANORM) and either OFFRATE, MIDRATE or ONRATE
depending on the value of PEAKTYPE (see data below to make sense of this)
PEAKTYPE is an INT either 1, 2 or 3. If PEAKTYPE = 1 then ToUREV =
CONSUMPTION*ONRATE, if PEAKTYPE = 2 then ToUREV = CONSUMPTION*MIDRATE and if
PEAKTYPE = 3 then ToUREV = CONSUMPTION*ONRATE

Basic SQL statement without ToUREV:
SELECT DATANORM.*, PEAKTYPE, HOURIDDATA.OFFRATE, HOURIDDATA.MIDRATE,
HOURIDDATA.ONRATE
FROM DATANORM INNER JOIN HOURIDDATA
ON (HOURIDDATA.POWERHOUR = DATANORM.TOD) AND (DATANORM.DATEREAD =
HOURIDDATA.POWERDATE)

with an output of
ID DATEREAD TOD CONSUMPTION readid PEAKTYPE OFFRATE MIDRATE ONRATE
29 2008-12-12 18 2.730000 24474329 1 4 7.2 8.8
29 2008-12-12 8 3.380000 19727309 1 4 7.2 8.8
29 2008-12-12 14 3.850000 22575521 2 4 7.2 8.8
30 2008-12-12 14 0.840000 22575887 2 4 7.2 8.8
30 2008-12-12 10 0.860000 20677079 1 4 7.2 8.8
30 2008-12-12 13 0.890000 22101185 2 4 7.2 8.8

 

[ghetto_banjo]

in the query design:

ToUREV: CONSUMPTION * iif(PEAKTYPE = 1, OFFRATE, iif(PEAKTYPE = 2,
MIDRATE, ONRATE))



you will need to specify table names in the event that the same field
names exist in both tables.

 

[Ian]

thx.