Difference between revisions of "Manual Phenotyping Database"

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

Revision as of 08:52, 5 October 2016 (view source) Mlucas (Talk | contribs) ← Older edit		Revision as of 08:54, 5 October 2016 (view source) Mlucas (Talk | contribs) Newer edit →
Line 18:		Line 18:
	CONCAT(		CONCAT(
	'MAX(IF(phenotype_date = ''', `phenotype_date`, ''',phenotype_value,NULL)) AS ''', `phenotype_date`,'''')		'MAX(IF(phenotype_date = ''', `phenotype_date`, ''',phenotype_value,NULL)) AS ''', `phenotype_date`,'''')
−	) INTO @sql	+	) INTO @sql FROM phenotype WHERE YEAR(phenotype_date) = 2016;
−	FROM phenotype WHERE YEAR(phenotype_date) = 2016;	+
	SET @sql = CONCAT('SELECT  entity_id, ', @sql, '		SET @sql = CONCAT('SELECT  entity_id, ', @sql, '
	FROM    phenotype WHERE phenotype.entity_id LIKE ''%16ASH0%'' AND phenotype.trait_id LIKE ''%PCTHEAD%''		FROM    phenotype WHERE phenotype.entity_id LIKE ''%16ASH0%'' AND phenotype.trait_id LIKE ''%PCTHEAD%''

---

Revision as of 08:54, 5 October 2016

The manual phenotyping database contains phenotypic field data that was collected by hand.

The main tables in the manual phenotyping database are shown in the figure below.

Example Queries

Row-to-Column Query

The query below is an example of how to generate a monthly report of the PCTHEAD (percent heading) trait.

SET SESSION group_concat_max_len = 10000; SET @sql = NULL; SELECT

 GROUP_CONCAT(DISTINCT
   CONCAT(
     'MAX(IF(phenotype_date = , `phenotype_date`, ,phenotype_value,NULL)) AS , `phenotype_date`,')
 ) INTO @sql FROM phenotype WHERE YEAR(phenotype_date) = 2016;

SET @sql = CONCAT('SELECT entity_id, ', @sql, '

                 FROM    phenotype WHERE phenotype.entity_id LIKE %16ASH0% AND phenotype.trait_id LIKE %PCTHEAD%
                 GROUP   BY entity_id');

PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;