Difference between revisions of "CIMMYT Wheat Breeding Database"

From Poland Lab Wiki
Jump to: navigation, search

Deprecated: The each() function is deprecated. This message will be suppressed on further calls in /home/customer/www/wiki.wheatgenetics.k-state.edu/public_html/includes/diff/DairikiDiff.php on line 434

Deprecated: assert(): Calling assert() with a string argument is deprecated in /home/customer/www/wiki.wheatgenetics.k-state.edu/public_html/includes/diff/DairikiDiff.php on line 437
 
(2 intermediate revisions by the same user not shown)
Line 3: Line 3:
 
The CIMMYT wheat breeding database schema is shown in the figure below:
 
The CIMMYT wheat breeding database schema is shown in the figure below:
  
[[File:cimmyt_v2.png]]
+
[[File:cimmytSchema2.png]]
  
 
==Example Queries==
 
==Example Queries==
Line 18: Line 18:
 
SET @SQL = NULL;
 
SET @SQL = NULL;
 
CREATE TEMPORARY TABLE IF NOT EXISTS p AS (SELECT * FROM phenotypes  
 
CREATE TEMPORARY TABLE IF NOT EXISTS p AS (SELECT * FROM phenotypes  
WHERE (YEAR(phenotype_date) = 2016 AND phenotype_value IS NOT NULL AND plot_id LIKE '16-OBR-B2I-EYT%' and trait_id LIKE '%NDVI%'));
+
WHERE (YEAR(phenotype_date) = 2016 AND phenotype_value IS NOT NULL AND plot_id LIKE '16-OBR-EYT-B2I%' and trait_id LIKE '%NDVI%'));
 
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(p.phenotype_date = ''', `phenotype_date`, ''',p.phenotype_value,NULL)) AS ''', `phenotype_date`,'''')
 
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(p.phenotype_date = ''', `phenotype_date`, ''',p.phenotype_value,NULL)) AS ''', `phenotype_date`,'''')
 
Order by `phenotype_date` ASC  ) INTO @SQL FROM p;
 
Order by `phenotype_date` ASC  ) INTO @SQL FROM p;

Latest revision as of 09:18, 18 June 2018

The CIMMYT wheat breading database contains phenotypic field data that was collected for CIMMYT wheat breeding experiments collected at multiple locations.

The CIMMYT wheat breeding database schema is shown in the figure below:

CimmytSchema2.png

Example Queries

Row-to-Column Query

FOR QUERY OF REPEATED MEASUREMENTS: This query can be used to extract a column format of a repeat measurements (over dates) for multiple traits.

The query below is an example of how to generate a report of the G-NDVI and R-NDVI traits from the 16OBR plots with B2I conditions, (bed planting and two irrigations) for the Elite Yield Trial (EYT).

SET SESSION group_concat_max_len = 10000;
USE CIMMYT;
SET @SQL = NULL;
CREATE TEMPORARY TABLE IF NOT EXISTS p AS (SELECT * FROM phenotypes 
WHERE (YEAR(phenotype_date) = 2016 AND phenotype_value IS NOT NULL AND plot_id LIKE '16-OBR-EYT-B2I%' AND trait_id LIKE '%NDVI%'));
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(p.phenotype_date = ''', `phenotype_date`, ''',p.phenotype_value,NULL)) AS ''', `phenotype_date`,'''')
ORDER BY `phenotype_date` ASC  ) INTO @SQL FROM p;
SET @SQL = CONCAT('SELECT  p.plot_id,p.trait_id, ', @SQL, ' FROM p GROUP BY p.plot_id,p.trait_id ORDER BY p.plot_id');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TEMPORARY TABLE p;