view_wpr_entity

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

enviro_id

int4

√

null

sample_number

int4

√

null

wpr

varchar

2147483647

√

null

wpr_botanical

varchar

2147483647

√

null

wpr_botanical_display

varchar

2147483647

√

null

wpr_colloquial

varchar

2147483647

√

null

entity

varchar

√

null

View Definition

SELECT DISTINCT e.enviro_id,
    e.sample_number,
        CASE
            WHEN e.wpr_analysis THEN wpr_lkp_b.wpr
            ELSE wpr_lkp_a.wpr
        END AS wpr,
        CASE
            WHEN e.wpr_analysis THEN wpr_b.wpr_botanical
            ELSE wpr_a.species
        END AS wpr_botanical,
        CASE
            WHEN e.wpr_analysis THEN wpr_lkp_b.waterlogged_name_display
            ELSE wpr_lkp_a.waterlogged_name_display
        END AS wpr_botanical_display,
        CASE
            WHEN e.wpr_analysis THEN wpr_lkp_b.waterlogged_colloq
            ELSE wpr_lkp_a.waterlogged_colloq
        END AS wpr_colloquial,
    ent.entity
   FROM ((((((tbl_environmental e
     LEFT JOIN tbl_contexts ctx ON ((e.context_number = ctx.context)))
     LEFT JOIN view_context_entity ent USING (context))
     LEFT JOIN tbl_wpr_species_b wpr_b USING (enviro_id))
     LEFT JOIN lkp_waterlogged wpr_lkp_b ON (((wpr_b.wpr_botanical)::text = (wpr_lkp_b.waterlogged_name)::text)))
     LEFT JOIN tbl_wpr_species wpr_a USING (sample_number))
     LEFT JOIN lkp_waterlogged wpr_lkp_a ON (((wpr_a.species)::text = (wpr_lkp_a.waterlogged_name)::text)))
  WHERE ((wpr_a.sample_number IS NOT NULL) OR (wpr_b.enviro_id IS NOT NULL))
UNION
 SELECT e.enviro_id,
    e.sample_number,
        CASE
            WHEN e.wpr_analysis THEN ('Matrix: '::text || (wm_b.wprmatrix_subtype)::text)
            ELSE ('Matrix: '::text || (wm_a.wprmatrix_subtype)::text)
        END AS wpr,
    NULL::character varying AS wpr_botanical,
    ''::character varying AS wpr_botanical_display,
        CASE
            WHEN e.wpr_analysis THEN wpr_b.wpr_matrix
            ELSE wpr_a.wpr_matrix
        END AS wpr_colloquial,
    ent.entity
   FROM ((((((tbl_environmental e
     LEFT JOIN tbl_contexts ctx ON ((e.context_number = ctx.context)))
     LEFT JOIN view_context_entity ent USING (context))
     LEFT JOIN tbl_wpr_matrix_b wpr_b USING (enviro_id))
     LEFT JOIN tbl_wpr_matrix wpr_a USING (enviro_id))
     LEFT JOIN lkp_wpr_matrix wm_a ON (((wm_a.wprmatrix)::text = (wpr_a.wpr_matrix)::text)))
     LEFT JOIN lkp_wpr_matrix wm_b ON (((wm_b.wprmatrix)::text = (wpr_b.wpr_matrix)::text)))
  WHERE ((wpr_a.enviro_id IS NOT NULL) OR (wpr_b.enviro_id IS NOT NULL));

Columns

View Definition

Possibly Referenced Tables/Views