Data Retrieval: getting study obs units

From ICISWiki

Study Observation Unit Queries: (datatype id: StudyBaseConstants.OBSERVATION_UNITS_DATATYPE_ID)

Getting by Representation ID:

Searchable attribute id: StudyBaseConstants.EFFECTS_DATATYPE_ATTRIBUTE_ID
+ LSID.ATTRIBUTE_DELIMITER
+ EffectBaseConstants.REPRESENTATIONS_DATATYPE_ATTRIBUTE_ID
+ LSID.ATTRIBUTE_DELIMITER
+ RepresentationBaseConstants.UNIQUE_IDENTIFIER_DATATYPE_ATTRIBUTE_ID;

SQL query:

SELECT DISTINCT ou.ounitid FROM oindex ou WHERE ou.represno=?

Getting by Study ID and Germplasm ID:

Searchable attribute id: StudyBaseConstants.UNIQUE_IDENTIFIER_DATATYPE_ATTRIBUTE_ID and GermplasmBaseConstants.UNIQUE_IDENTIFIER_DATATYPE_ATTRIBUTE_ID

SQL query:

SELECT DISTINCT ou.ounitid AS ounitid FROM oindex ou INNER JOIN level_n ln ON ln.levelno = ou.levelno AND ln.lvalue = 50533 INNER JOIN factor f ON f.labelid = ln.labelid AND f.studyid = 124 AND f.scaleid = 91 AND f.traitid = 251 AND f.tmethid = 17 UNION SELECT DISTINCT ou.ounitid AS ounitid FROM oindex ou INNER JOIN level_c lc ON lc.levelno = ou.levelno AND lc.lvalue = '50533' INNER JOIN factor f ON f.labelid = lc.labelid AND f.studyid = 124 AND f.scaleid = 91 AND f.traitid = 251 AND f.tmethid = 17

Getting by Representation ID and Germplasm ID:

Searchable attribute id: StudyBaseConstants.EFFECTS_DATATYPE_ATTRIBUTE_ID
+ LSID.ATTRIBUTE_DELIMITER
+ EffectBaseConstants.REPRESENTATIONS_DATATYPE_ATTRIBUTE_ID
+ LSID.ATTRIBUTE_DELIMITER
+ RepresentationBaseConstants.UNIQUE_IDENTIFIER_DATATYPE_ATTRIBUTE_ID;
and GermplasmBaseConstants.UNIQUE_IDENTIFIER_DATATYPE_ATTRIBUTE_ID

SQL query:

SELECT DISTINCT ou.ounitid AS ounitid FROM oindex ou INNER JOIN level_n ln ON ln.levelno = ou.levelno AND ln.lvalue = ? INNER JOIN factor f ON f.labelid = ln.labelid AND ou.represno = ? AND f.scaleid = 91 AND f.traitid = 251 AND f.tmethid = 17 UNION SELECT DISTINCT ou.ounitid AS ounitid FROM oindex ou INNER JOIN level_c lc ON lc.levelno = ou.levelno AND lc.lvalue = '50533' INNER JOIN factor f ON f.labelid = lc.labelid AND ou.represno = 248 AND f.scaleid = 91 AND f.traitid = 251 AND f.tmethid = 17

Getting by ValueSet Values (ValueSet.getValues):

Note: Observation Units are represented as ValueSet objects

SQL query:

SELECT wd.datacol, if(wd.datavaldesc is null, wd.dataval, wd.datavaldesc), wd.ounitid, wd.dataset
FROM wh_data wd WHERE wd.ounitid = ? ORDER BY wd.datarow, wd.datacol

SELECT wc.colname FROM wh_columns wc WHERE wc.dataset = wh_data.dataset ORDER BY wc.col