germinate_template_4_21_11_08
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
export_trait_categorical
Parameters
Name
Type
Mode
groupIds
text
IN
markedIds
text
IN
datasetIds
text
IN
traitIds
text
IN
Definition
BEGIN SET @SQL = NULL; SET @@group_concat_max_len = 64000000; SET @QRY = CONCAT(' SELECT GROUP_CONCAT(DISTINCT CONCAT("COUNT(IF(`dataset_id` = ", `dataset_id`,",phenotype_value,NULL)) AS ", "`", datasets.`name`, "`")) INTO @SQL FROM phenotypedata LEFT JOIN datasets ON datasets.id = phenotypedata.dataset_id ', IF(groupIds IS NULL, '', 'LEFT JOIN groupmembers ON phenotypedata.germinatebase_id = groupmembers.foreign_id '), 'WHERE ', IF(datasetIds IS NULL, '', CONCAT('phenotypedata.dataset_id IN (', datasetIds, ') AND ')), IF (markedIds IS NULL, '', CONCAT('phenotypedata.germinatebase_id IN (', markedIds,') AND ')), IF (groupIds IS NULL, '', CONCAT('groupmembers.group_id IN (', groupIds,') AND ')), 'phenotypedata.phenotype_id IN (', traitIds, ')'); PREPARE stmtone FROM @QRY; EXECUTE stmtone; DEALLOCATE PREPARE stmtone; IF @SQL IS NULL THEN SELECT NULL as ERROR; ELSE SET @SQL = CONCAT(' SELECT `phenotype_value`, ', @SQL, ' FROM phenotypedata LEFT JOIN datasets ON phenotypedata.dataset_id = datasets.id ', IF(groupIds IS NULL, '', 'LEFT JOIN groupmembers ON phenotypedata.germinatebase_id = groupmembers.foreign_id '), 'WHERE ', IF(datasetIds IS NULL, '', CONCAT('phenotypedata.dataset_id IN (', datasetIds, ') AND ')), IF (markedIds IS NULL, '', CONCAT('phenotypedata.germinatebase_id IN (', markedIds,') AND ')), IF (groupIds IS NULL, '', CONCAT('groupmembers.group_id IN (', groupIds,') AND ')), 'phenotypedata.phenotype_id IN (', traitIds, ') GROUP BY phenotypedata.phenotype_value'); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END