Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

wesleycrusher

Technical User
Sep 30, 2003
61
US
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.
 
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…
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top