Project

General

Profile

« Previous | Next » 

Revision 13366

Added by Aaron Marcuse-Kubitza over 10 years ago

bugfix: inputs/SALVIAS/validations.sql: need to cast character varying to text so that the types of each side of if() match

View differences:

trunk/inputs/SALVIAS/validations.sql
219 219 
    count(a.taxon) AS taxa
220 220 
   FROM ( SELECT DISTINCT p.project_name,
221 221 
            (pm."SiteCode")::text AS plot_code,
222 
            btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text)))))) AS taxon
222 
            btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon
223 223 
           FROM ((projects p
224 224 
      JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
225 225 
   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))) a
......
243 243 
CREATE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS
244 244 
 SELECT DISTINCT p.project_name,
245 245 
    (pm."SiteCode")::text AS plot_code,
246 
    btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text)))))) AS taxon
246 
    btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon
247 247 
   FROM ((projects p
248 248 
   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))
249 249 
   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))
250 
  ORDER BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text))))));
250 
  ORDER BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))));
251 251 

  		




  252
  252
  
    

  		




  253
  253
  
    
--


  		




  ......




  266
  266
  
    
CREATE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS


  		




  267
  267
  
    
 SELECT DISTINCT p.project_name, 


  		




  268
  268
  
    
    (pm."SiteCode")::text AS plot_code, 


  		




  269
  
  
    
    btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text)))))) AS taxon, 


  		




  
  269
  
    
    btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon, 


  		




  270
  270
  
    
    sum(po."NoInd") AS individuals


  		




  271
  271
  
    
   FROM ((projects p


  		




  272
  272
  
    
   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))


  		




  273
  273
  
    
   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))


  		




  274
  
  
    
  GROUP BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text))))))


  		




  275
  
  
    
  ORDER BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text))))));


  		




  
  274
  
    
  GROUP BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))))


  		




  
  275
  
    
  ORDER BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))));


  		




  276
  276
  
    

  		




  277
  277
  
    

  		




  278
  278
  
    
--


  		




  ......




  291
  291
  
    
CREATE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS


  		




  292
  292
  
    
 SELECT DISTINCT p.project_name, 


  		




  293
  293
  
    
    (pm."SiteCode")::text AS plot_code, 


  		




  294
  
  
    
    btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text)))))) AS taxon, 


  		




  
  294
  
    
    btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon, 


  		




  295
  295
  
    
    sum((po.cover_percent)::double precision) AS totalpercentcover


  		




  296
  296
  
    
   FROM ((projects p


  		




  297
  297
  
    
   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))


  		




  298
  298
  
    
   JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.cover_percent IS NOT NULL))))


  		




  299
  
  
    
  GROUP BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text))))))


  		




  300
  
  
    
  ORDER BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text))))));


  		




  
  299
  
    
  GROUP BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))))


  		




  
  300
  
    
  ORDER BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))));


  		




  301
  301
  
    

  		




  302
  302
  
    

  		




  303
  303
  
    
--


  		




  ......




  318
  318
  
    
CREATE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj AS


  		




  319
  319
  
    
 SELECT DISTINCT p.project_name, 


  		




  320
  320
  
    
    (pm."SiteCode")::text AS plot_code, 


  		




  321
  
  
    
    btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text)))))) AS taxon, 


  		




  
  321
  
    
    btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon, 


  		




  322
  322
  
    
    po.intercept_cm


  		




  323
  323
  
    
   FROM ((projects p


  		




  324
  324
  
    
   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))


  		




  325
  325
  
    
   JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.intercept_cm IS NOT NULL))))


  		




  326
  
  
    
  ORDER BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text))))));


  		




  
  326
  
    
  ORDER BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))));


  		




  327
  327
  
    

  		




  328
  328
  
    

  		




  329
  329
  
    
--


  		












Also available in:  Unified diff