RTV Retrieve Queries from Local

From ICISWiki

Needed Tables:

• RTV-LIST - contains the entries of the selected lists
• RTV-STUDY-GID - contains the selected studies where the entries were tested
• RTV-PROPERTY-GID - contains the selected properties from the selected studies

Involved Queries:

• RTVQRY_GID_OUNTID_GID

SELECT [RTV-STUDY-GID].SNAME, FACTOR.STUDYID, OINDEX.OUNITID, LEVEL_N.LVALUE, 
FACTOR.FNAME, FACTOR.SCALEID, [RTV-LIST].DESIG
FROM ([RTV-LIST] INNER JOIN ((FACTOR INNER JOIN LEVEL_N ON FACTOR.LABELID = 
LEVEL_N.LABELID) INNER JOIN OINDEX ON LEVEL_N.LEVELNO = OINDEX.LEVELNO) ON 
[RTV-LIST].GID = LEVEL_N.LVALUE) INNER JOIN [RTV-STUDY-GID] ON FACTOR.STUDYID = 
[RTV-STUDY-GID].STUDYID WHERE (((FACTOR.SCALEID)=91));

• RTVQRY_ANYGID_OUTPUT_DATA_CL

SELECT RTVQRY_GID_OUNITID_GID.SNAME, DATA_C1.OUNITID, DATA_C1.DVALUE, [RTV-PROPERTY-GID].VARNAME
FROM RTVQRY_GID_OUNITID_GID INNER JOIN ((VARIATE INNER JOIN [RTV-PROPERTY-GID] ON 
(VARIATE.TRAITID =  [RTV-PROPERTY-GID].TRAITID) AND (VARIATE.SCALEID = [RTV-PROPERTY-GID].SCALEID) AND 
(VARIATE.TMETHID = [RTV-PROPERTY-GID].TMETHID)) INNER JOIN DATA_C1 ON VARIATE.VARIATID = DATA_C1.VARIATID) 
 ON RTVQRY_GID_OUNITID_GID.OUNITID = DATA_C1.OUNITID;

• RTVQRY_ANYGID_OUTPUT_DATA_NL

SELECT [RTVQry_GID_OUNITID_RTV-STUDY].SNAME, [RTV-PROPERTY].VARNAME, 
[RTVQry_GID_OUNITID_RTV-STUDY].OUNITID, DATA_N1.DVALUE
FROM [RTVQry_GID_OUNITID_RTV-STUDY] INNER JOIN (([RTV-PROPERTY] INNER JOIN VARIATE 
ON ([RTV-PROPERTY].TRAITID = VARIATE.TRAITID) AND ([RTV-PROPERTY].SCALEID = VARIATE.SCALEID) 
AND ([RTV-PROPERTY].TMETHID = VARIATE.TMETHID)) INNER JOIN DATA_N1 ON VARIATE.VARIATID = DATA_N1.VARIATID) 
ON [RTVQry_GID_OUNITID_RTV-STUDY].OUNITID = DATA_N1.OUNITID;

• RTVQRY_Append_Data_CL_to_RTV-OUTPUT

INSERT INTO [RTV-OUTPUT] ( STUDY, VARNAME, OUNITID, [VALUE] )
 SELECT RTVQry_Output_Data_CL.SNAME, RTVQry_Output_Data_CL.VARNAME, RTVQry_Output_Data_CL.OUNITID, RTVQry_Output_Data_CL.DVALUE
FROM RTVQry_Output_Data_CL;