Using IF to convert information when building a View in pgAdmin4

user_15470201 0
Viewed 41

I am working with a company to provide information on sewer systems. As part of this work, I am taking information from a municipality and converting it into data that can be used by a company. The municipality is having me work with pgAdmin4 to interact with their database, and I am using Views to format their existing data into a form that can be used by the company. The municipality has storm data written in one form, and I wanted to take an IF statement and convert it to another form. I have an example below of the View I have currently built:

 SELECT t1."Asset ID",
    t1."Name",
    ( SELECT t1."Asset ID") AS "GIS ID",
    t1."Asset Status",
    ( SELECT pipes.conyear
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision AND pipes.conyear <> 0) AS "Installation Year",
    t1."Initial Cost",
    ( SELECT pipes.shape_leng
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision AND pipes.shape_leng IS NOT NULL) AS "Length",
    ( SELECT (pipes.shape_leng * pipes.size::double precision / 12::double precision)::numeric AS "numeric"
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision) AS "Area",
    ( SELECT (pipes.shape_leng * (pipes.size::double precision / 2::double precision) * (pipes.size::double precision / 2::double precision) * 3.14::double precision)::numeric AS "numeric"
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision) AS "Volume",
    t1."Quantity",
    t1."Replacement Cost",
    t1."Replacement Cost Year",
    t1."Cost Adjustment",
    t1."Fixed Cost",
    t1."Exclude",
    t1."Zone",
    ( SELECT pipes.size
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision AND pipes.size::double precision IS NOT NULL) AS "Diameter",
    t1."Size Category",
    t1."Type",
    ( SELECT pipes.type
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision) AS "Material",
    t1."Material Type",
    t1."Shape",
    t1."Classification",
    ( SELECT pipes.up_str
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision AND pipes.up_str IS NOT NULL) AS "Start Manhole ID",
    ( SELECT pipes.dwn_str
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision AND pipes.dwn_str IS NOT NULL) AS "Finish Manhole ID",
    t1."Roughness Coefficient",
    t1."Surface Type",
    t1."Buried Depth",
    t1."Depth of Cover",
    t1."Inner Pressure",
    ( SELECT pipes.slope
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision AND pipes.slope IS NOT NULL) AS "Slope",
    t1."Soil Type",
    t1."Soil Condition",
    t1."Groundwater Table (GWT)",
    t1."Main Type",
    t1."Roadside Environment",
    t1."Functional Class",
    t1."Fiber Optic Use",
    t1."Service Type",
    t1."Condition Index (CI)",
    t1."CI Assessment Year",
    t1."Service Life (SL)",
    t1."Remaining Service Life",
    t1."Defect Flag",
    t1."Count"
   FROM sewers.sanitary_inventory t1;

What I would like to do is take this line:

( SELECT pipes.type
               FROM sewers.pipes
              WHERE pipes.id::double precision = t1."Asset ID"::double precision) AS "Material",

And make it an IF statement that takes the input, which would be text values, and if it matches a specific word, convert them to another text value, then input that new value into the view. This is a small example of what I am thinking:

IF pipes.type = 'CONC' 
   convert to 'concrete'
OR IF pipes.types = 'PVC'
   convert to 'Polyvinyl Chloride'
OR IF ...

I am not sure how to make this work, does any one know how to do this, or is it even possible?

if-statement postgresql sql
3 Answers

You need CASE:

SELECT
  a,
  b,
  CASE pipes.type
    WHEN 'CONC' THEN 'concrete'
    WHEN 'PVC' THEN 'Polyvinyl Chloride'
    ELSE ...
  END AS c,
  d
FROM
  mytable ...

You can read more about this in the documentation.

user_121961760

Use a case when statement.

case when pipes.type = 'CONC' then 'concrete' 
     when pipes.type = 'PVC' then 'Polyvinyl Chloride' 
end as pipes_type
user_44325410

Thanks to Thom Brown, I found a solution that works. The code is below:

 SELECT t1."Asset ID",
    t1."Name",
    ( SELECT t1."Asset ID") AS "GIS ID",
    t1."Asset Status",
    ( SELECT pipes.conyear
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision AND pipes.conyear <> 0) AS "Installation Year",
    t1."Initial Cost",
    ( SELECT pipes.shape_leng
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision AND pipes.shape_leng IS NOT NULL) AS "Length",
    ( SELECT (pipes.shape_leng * pipes.size::double precision / 12::double precision)::numeric AS "numeric"
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision) AS "Area",
    ( SELECT (pipes.shape_leng * (pipes.size::double precision / 2::double precision) * (pipes.size::double precision / 2::double precision) * 3.14::double precision)::numeric AS "numeric"
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision) AS "Volume",
    t1."Quantity",
    t1."Replacement Cost",
    t1."Replacement Cost Year",
    t1."Cost Adjustment",
    t1."Fixed Cost",
    t1."Exclude",
    t1."Zone",
    ( SELECT pipes.size
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision AND pipes.size::double precision IS NOT NULL) AS "Diameter",
    t1."Size Category",
    t1."Type",
    ( SELECT
                CASE
                    WHEN pipes.type::text = 'CONC'::text THEN 'concrete'::character varying
                    WHEN pipes.type::text = 'PVC'::text THEN 'Polyvinyl Chloride'::character varying
                    WHEN pipes.type::text = 'HDPE'::text THEN 'High Density Polyethylene'::character varying
                    WHEN pipes.type::text = 'CI'::text THEN 'Cast Iron'::character varying
                    WHEN pipes.type::text = 'CMP'::text THEN 'Corrugated Metal'::character varying
                    WHEN pipes.type::text = 'DI'::text THEN 'Ductile Iron'::character varying
                    WHEN pipes.type::text = 'HOBAS'::text THEN 'Plastic'::character varying
                    WHEN pipes.type::text = 'HOOD'::text THEN 'Unknown'::character varying
                    WHEN pipes.type::text = 'NSI'::text THEN 'Unknown'::character varying
                    WHEN pipes.type::text = 'Plugged'::text THEN 'Unknown'::character varying
                    WHEN pipes.type::text = 'PLAS'::text THEN 'Plastic'::character varying
                    WHEN pipes.type::text = 'C900'::text THEN 'Plastic'::character varying
                    WHEN pipes.type::text = 'C905'::text THEN 'Plastic'::character varying
                    WHEN pipes.type::text = 'BPP'::text THEN 'Cast Iron'::character varying
                    WHEN pipes.type::text = 'CCP'::text THEN 'Reinforced Concrete'::character varying
                    ELSE pipes.type
                END AS type
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision) AS "Material",
    t1."Material Type",
    t1."Shape",
    t1."Classification",
    ( SELECT pipes.up_str
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision AND pipes.up_str IS NOT NULL) AS "Start Manhole ID",
    ( SELECT pipes.dwn_str
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision AND pipes.dwn_str IS NOT NULL) AS "Finish Manhole ID",
    t1."Roughness Coefficient",
    t1."Surface Type",
    t1."Buried Depth",
    t1."Depth of Cover",
    t1."Inner Pressure",
    ( SELECT pipes.slope
           FROM sewers.pipes
          WHERE pipes.id::double precision = t1."Asset ID"::double precision AND pipes.slope IS NOT NULL) AS "Slope",
    t1."Soil Type",
    t1."Soil Condition",
    t1."Groundwater Table (GWT)",
    t1."Main Type",
    t1."Roadside Environment",
    t1."Functional Class",
    t1."Fiber Optic Use",
    t1."Service Type",
    t1."Condition Index (CI)",
    t1."CI Assessment Year",
    t1."Service Life (SL)",
    t1."Remaining Service Life",
    t1."Defect Flag",
    t1."Count"
   FROM sewers.storm_inventory t1;
user_154702010
Related