Conditional "Create View <formula> AS <name>" ?

[wesleycrusher]

Here is my code...

CREATE VIEW public.wh AS SELECT DISTINCT model.model_number, model.fuel_type, model.efactor, 41045/model.efactor*365/100000 AS ngas_use, 41045/model.efactor*365/91333 AS pgas_use
FROM
(etc…

I'm trying to determine the annual energy use (represented by ngas_use & pgas_use) of heaters. However, the formula that I use depends on the gas type. So, if the unit uses natural gas, it uses the 100000 formula. If it uses propane, it uses the 91333 formula.

Is there a way to use an if-then type statement in this CREATE VIEW to first determine the fuel_type then employ the appropriate formula to find the annual energy usage? I want to keep the results in two separate columns. TY.

 

[ceco]

you can use something like

SELECT 41045/model.efactor*365/ CASE WHEN gas_type = 'natural' THEN 100000 ELSE 91333 END


see

http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html

for details on CASE usage

(you can use more WHEN clauses in ona CASE)

 

[wesleycrusher]

Thanks! your suggestion helped me out greatly. It took me a while to get the formatting down but it worked beautifully! FYI, here is what I did:

CREATE VIEW public.wh AS SELECT DISTINCT model.model_number, model.fuel_type, model.efactor,
round(365/rwh_model.efactor*
CASE
WHEN model.fuel_type='electric' THEN 12.03 END) AS elec_use,
round(365/rwh_model.efactor*41045/
CASE
WHEN model.fuel_type='natural' THEN 100000
WHEN model.fuel_type='both' THEN 100000 END) AS ngas_use,
round(365/rwh_model.efactor*41045/
CASE
WHEN model.fuel_type ='propane' THEN 91333
WHEN model.fuel_type ='both' THEN 91333 END) AS pgas_use
FROM
(etc…