All queries



Query addQualitativeValueByPmfmStrategyId

SQL query HQL query
insert into PMFM_STRAT_PMFM_QUAL_VALUE(PMFM_STRAT_ID,PMFM_ID,QUAL_VALUE_ID) values (:pmfmStrategyId,:pmfmId,:qualitativeValueId);
Parameters
Name Type Description
pmfmId java.lang.Integer  
pmfmStrategyId java.lang.Integer  
qualitativeValueId java.lang.Integer  


Query copyMonLocAreasToSurvey

SQL query HQL query
insert into Q2_TU.SURVEY_AREA ( SURVEY_ID, SURVEY_POSITION ) select :surveyId, monlocarea0_.MON_LOC_POSITION as col_0_0_ from Q2_TU.MON_LOC_AREA monlocarea0_ where monlocarea0_.MON_LOC_ID=:monLocId; INSERT INTO SurveyAreaImpl (surveyId, surveyPosition) SELECT :surveyId, monLocPosition FROM MonLocAreaImpl WHERE monLocId = :monLocId
Parameters
Name Type Description
surveyId java.lang.Integer  
monLocId java.lang.Integer  


Query copyMonLocLinesToSurvey

SQL query HQL query
insert into Q2_TU.SURVEY_LINE ( SURVEY_ID, SURVEY_POSITION ) select :surveyId, monlocline0_.MON_LOC_POSITION as col_0_0_ from Q2_TU.MON_LOC_LINE monlocline0_ where monlocline0_.MON_LOC_ID=:monLocId; INSERT INTO SurveyLineImpl (surveyId, surveyPosition) SELECT :surveyId, monLocPosition FROM MonLocLineImpl WHERE monLocId = :monLocId
Parameters
Name Type Description
surveyId java.lang.Integer  
monLocId java.lang.Integer  


Query copyMonLocPointsToSurvey

SQL query HQL query
insert into Q2_TU.SURVEY_POINT ( SURVEY_ID, SURVEY_POSITION ) select :surveyId, monlocpoin0_.MON_LOC_POSITION as col_0_0_ from Q2_TU.MON_LOC_POINT monlocpoin0_ where monlocpoin0_.MON_LOC_ID=:monLocId; INSERT INTO SurveyPointImpl (surveyId, surveyPosition) SELECT :surveyId, monLocPosition FROM MonLocPointImpl WHERE monLocId = :monLocId
Parameters
Name Type Description
surveyId java.lang.Integer  
monLocId java.lang.Integer  


Query copySurveyAreasToSamplingOperation

SQL query HQL query
insert into Q2_TU.SAMPLING_OPER_AREA ( SAMPLING_OPER_ID, SAMPLING_OPER_POSITION ) select :samplingOperId, surveyarea0_.SURVEY_POSITION as col_0_0_ from Q2_TU.SURVEY_AREA surveyarea0_ where surveyarea0_.SURVEY_ID=:surveyId; INSERT INTO SamplingOperAreaImpl (samplingOperId, samplingOperPosition) SELECT :samplingOperId, surveyPosition FROM SurveyAreaImpl WHERE surveyId = :surveyId
Parameters
Name Type Description
samplingOperId java.lang.Integer  
surveyId java.lang.Integer  


Query copySurveyLinesToSamplingOperation

SQL query HQL query
insert into Q2_TU.SAMPLING_OPER_LINE ( SAMPLING_OPER_ID, SAMPLING_OPER_POSITION ) select :samplingOperId, surveyline0_.SURVEY_POSITION as col_0_0_ from Q2_TU.SURVEY_LINE surveyline0_ where surveyline0_.SURVEY_ID=:surveyId; INSERT INTO SamplingOperLineImpl (samplingOperId, samplingOperPosition) SELECT :samplingOperId, surveyPosition FROM SurveyLineImpl WHERE surveyId = :surveyId
Parameters
Name Type Description
samplingOperId java.lang.Integer  
surveyId java.lang.Integer  


Query copySurveyPointsToSamplingOperation

SQL query HQL query
insert into Q2_TU.SAMPLING_OPER_POINT ( SAMPLING_OPER_ID, SAMPLING_OPER_POSITION ) select :samplingOperId, surveypoin0_.SURVEY_POSITION as col_0_0_ from Q2_TU.SURVEY_POINT surveypoin0_ where surveypoin0_.SURVEY_ID=:surveyId; INSERT INTO SamplingOperPointImpl (samplingOperId, samplingOperPosition) SELECT :samplingOperId, surveyPosition FROM SurveyPointImpl WHERE surveyId = :surveyId
Parameters
Name Type Description
samplingOperId java.lang.Integer  
surveyId java.lang.Integer  


Query countSurveyUsingCampaign

SQL query HQL query
select count(*) as col_0_0_ from Q2_TU.SURVEY surveyimpl0_ where surveyimpl0_.CAMPAIGN_ID=:campaignId; SELECT COUNT(*) FROM SurveyImpl WHERE campaign.campaignId = :campaignId
Parameters
Name Type Description
campaignId java.lang.Integer  


Query countSurveysByProgramLocationAndInsideDates

SQL query HQL query
SELECT COUNT(*) FROM ( SELECT S.SURVEY_ID FROM SURVEY S INNER JOIN SURVEY_PROG SP ON SP.SURVEY_ID = S.SURVEY_ID WHERE S.MON_LOC_ID = :locationId AND SP.PROG_CD = :programCode AND S.SURVEY_DT >= :startDate AND S.SURVEY_DT <= :endDate MINUS SELECT S.SURVEY_ID FROM SURVEY S INNER JOIN SURVEY_PROG SP ON SP.SURVEY_ID = S.SURVEY_ID, ( SELECT AP.APPLIED_PERIOD_START_DT, AP.APPLIED_PERIOD_END_DT FROM APPLIED_STRATEGY APS INNER JOIN APPLIED_PERIOD AP ON AP.APPLIED_STRAT_ID = APS.APPLIED_STRAT_ID INNER JOIN STRATEGY S ON S.STRAT_ID = APS.STRAT_ID WHERE S.PROG_CD = :programCode AND APS.MON_LOC_ID = :locationId AND APS.APPLIED_STRAT_ID != :appliedStrategyId ) OTHERAPS WHERE S.MON_LOC_ID = :locationId AND SP.PROG_CD = :programCode AND S.SURVEY_DT < OTHERAPS.APPLIED_PERIOD_START_DT AND S.SURVEY_DT > OTHERAPS.APPLIED_PERIOD_END_DT );
Parameters
Name Type Description
programCode java.lang.String  
endDate java.util.Date  
locationId java.lang.Integer  
appliedStrategyId java.lang.Integer  
startDate java.util.Date  


Query countSurveysByProgramLocationAndInsideDatesPostgresql

SQL query HQL query
SELECT COUNT(*) FROM ( SELECT S.SURVEY_ID FROM SURVEY S INNER JOIN SURVEY_PROG SP ON SP.SURVEY_ID = S.SURVEY_ID WHERE S.MON_LOC_ID = :locationId AND SP.PROG_CD = :programCode AND S.SURVEY_DT >= :startDate AND S.SURVEY_DT <= :endDate EXCEPT SELECT S.SURVEY_ID FROM SURVEY S INNER JOIN SURVEY_PROG SP ON SP.SURVEY_ID = S.SURVEY_ID, ( SELECT AP.APPLIED_PERIOD_START_DT, AP.APPLIED_PERIOD_END_DT FROM APPLIED_STRATEGY APS INNER JOIN APPLIED_PERIOD AP ON AP.APPLIED_STRAT_ID = APS.APPLIED_STRAT_ID INNER JOIN STRATEGY S ON S.STRAT_ID = APS.STRAT_ID WHERE S.PROG_CD = :programCode AND APS.MON_LOC_ID = :locationId AND APS.APPLIED_STRAT_ID != :appliedStrategyId ) OTHERAPS WHERE S.MON_LOC_ID = :locationId AND SP.PROG_CD = :programCode AND S.SURVEY_DT < OTHERAPS.APPLIED_PERIOD_START_DT AND S.SURVEY_DT > OTHERAPS.APPLIED_PERIOD_END_DT ) AS SURVEYS;
Parameters
Name Type Description
programCode java.lang.String  
endDate java.util.Date  
locationId java.lang.Integer  
appliedStrategyId java.lang.Integer  
startDate java.util.Date  


Query countSurveysByProgramLocationAndOutsideDates

SQL query HQL query
SELECT COUNT(*) FROM ( SELECT S.SURVEY_ID FROM SURVEY S INNER JOIN SURVEY_PROG SP ON SP.SURVEY_ID = S.SURVEY_ID WHERE S.MON_LOC_ID = :locationId AND SP.PROG_CD = :programCode AND (S.SURVEY_DT < :startDate OR S.SURVEY_DT > :endDate) MINUS SELECT S.SURVEY_ID FROM SURVEY S INNER JOIN SURVEY_PROG SP ON SP.SURVEY_ID = S.SURVEY_ID, ( SELECT AP.APPLIED_PERIOD_START_DT, AP.APPLIED_PERIOD_END_DT FROM APPLIED_STRATEGY APS INNER JOIN APPLIED_PERIOD AP ON AP.APPLIED_STRAT_ID = APS.APPLIED_STRAT_ID INNER JOIN STRATEGY S ON S.STRAT_ID = APS.STRAT_ID WHERE S.PROG_CD = :programCode AND APS.MON_LOC_ID = :locationId AND APS.APPLIED_STRAT_ID != :appliedStrategyId ) OTHERAPS WHERE S.MON_LOC_ID = :locationId AND SP.PROG_CD = :programCode AND S.SURVEY_DT >= OTHERAPS.APPLIED_PERIOD_START_DT AND S.SURVEY_DT <= OTHERAPS.APPLIED_PERIOD_END_DT );
Parameters
Name Type Description
programCode java.lang.String  
endDate java.util.Date  
locationId java.lang.Integer  
appliedStrategyId java.lang.Integer  
startDate java.util.Date  


Query countSurveysByProgramLocationAndOutsideDatesPostgresql

SQL query HQL query
SELECT COUNT(*) FROM ( SELECT S.SURVEY_ID FROM SURVEY S INNER JOIN SURVEY_PROG SP ON SP.SURVEY_ID = S.SURVEY_ID WHERE S.MON_LOC_ID = :locationId AND SP.PROG_CD = :programCode AND (S.SURVEY_DT < :startDate OR S.SURVEY_DT > :endDate) EXCEPT SELECT S.SURVEY_ID FROM SURVEY S INNER JOIN SURVEY_PROG SP ON SP.SURVEY_ID = S.SURVEY_ID, ( SELECT AP.APPLIED_PERIOD_START_DT, AP.APPLIED_PERIOD_END_DT FROM APPLIED_STRATEGY APS INNER JOIN APPLIED_PERIOD AP ON AP.APPLIED_STRAT_ID = APS.APPLIED_STRAT_ID INNER JOIN STRATEGY S ON S.STRAT_ID = APS.STRAT_ID WHERE S.PROG_CD = :programCode AND APS.MON_LOC_ID = :locationId AND APS.APPLIED_STRAT_ID != :appliedStrategyId ) OTHERAPS WHERE S.MON_LOC_ID = :locationId AND SP.PROG_CD = :programCode AND S.SURVEY_DT >= OTHERAPS.APPLIED_PERIOD_START_DT AND S.SURVEY_DT <= OTHERAPS.APPLIED_PERIOD_END_DT ) AS SURVEYS;
Parameters
Name Type Description
programCode java.lang.String  
endDate java.util.Date  
locationId java.lang.Integer  
appliedStrategyId java.lang.Integer  
startDate java.util.Date  


Query deleteMonLonAreas

SQL query HQL query
delete from Q2_TU.MON_LOC_AREA where MON_LOC_ID=:monLocId; DELETE MonLocAreaImpl WHERE monLocId = :monLocId
Parameters
Name Type Description
monLocId java.lang.Integer  


Query deleteMonLonLines

SQL query HQL query
delete from Q2_TU.MON_LOC_LINE where MON_LOC_ID=:monLocId; DELETE MonLocLineImpl WHERE monLocId = :monLocId
Parameters
Name Type Description
monLocId java.lang.Integer  


Query deleteMonLonPoints

SQL query HQL query
delete from Q2_TU.MON_LOC_POINT where MON_LOC_ID=:monLocId; DELETE MonLocPointImpl WHERE monLocId = :monLocId
Parameters
Name Type Description
monLocId java.lang.Integer  


Query deleteQualitativeValuesByPmfmStrategyId

SQL query HQL query
delete from PMFM_STRAT_PMFM_QUAL_VALUE where PMFM_STRAT_ID = :pmfmStrategyId;
Parameters
Name Type Description
pmfmStrategyId java.lang.Integer  


Query deleteSamplingOperAreas

SQL query HQL query
delete from Q2_TU.SAMPLING_OPER_AREA where SAMPLING_OPER_ID=:samplingOperId; DELETE SamplingOperAreaImpl WHERE samplingOperId = :samplingOperId
Parameters
Name Type Description
samplingOperId java.lang.Integer  


Query deleteSamplingOperLines

SQL query HQL query
delete from Q2_TU.SAMPLING_OPER_LINE where SAMPLING_OPER_ID=:samplingOperId; DELETE SamplingOperLineImpl WHERE samplingOperId = :samplingOperId
Parameters
Name Type Description
samplingOperId java.lang.Integer  


Query deleteSamplingOperPoints

SQL query HQL query
delete from Q2_TU.SAMPLING_OPER_POINT where SAMPLING_OPER_ID=:samplingOperId; DELETE SamplingOperPointImpl WHERE samplingOperId = :samplingOperId
Parameters
Name Type Description
samplingOperId java.lang.Integer  


Query deleteSurveyAreas

SQL query HQL query
delete from Q2_TU.SURVEY_AREA where SURVEY_ID=:surveyId; DELETE SurveyAreaImpl WHERE surveyId = :surveyId
Parameters
Name Type Description
surveyId java.lang.Integer  


Query deleteSurveyLines

SQL query HQL query
delete from Q2_TU.SURVEY_LINE where SURVEY_ID=:surveyId; DELETE SurveyLineImpl WHERE surveyId = :surveyId
Parameters
Name Type Description
surveyId java.lang.Integer  


Query deleteSurveyPoints

SQL query HQL query
delete from Q2_TU.SURVEY_POINT where SURVEY_ID=:surveyId; DELETE SurveyPointImpl WHERE surveyId = :surveyId
Parameters
Name Type Description
surveyId java.lang.Integer  


Query lastSystemVersion

SQL query HQL query
select systemvers0_.SYSTEM_VERSION_LB as col_0_0_ from Q2_TU.SYSTEM_VERSION systemvers0_ where systemvers0_.SYSTEM_VERSION_ID=( select max(systemvers1_.SYSTEM_VERSION_ID) from Q2_TU.SYSTEM_VERSION systemvers1_ ); SELECT systemVersionLb as schemaVersion FROM SystemVersionImpl WHERE systemVersionId = (select max(systemVersionId) from SystemVersionImpl)


Query lightMonitoringLocations

SQL query HQL query
select monitoring0_.MON_LOC_ID as col_0_0_, monitoring0_.MON_LOC_LB as col_1_0_, monitoring0_.MON_LOC_NM as col_2_0_ from Q2_TU.MONITORING_LOCATION monitoring0_; SELECT monLocId, monLocLb, monLocNm FROM MonitoringLocationImpl


Query positioningSystemIds

SQL query HQL query
select positionni0_.POS_SYSTEM_ID as col_0_0_ from Q2_TU.POSITIONNING_SYSTEM positionni0_; SELECT posSystemId FROM PositionningSystemImpl


Query qualitativeValueIdsByPmfmStrategyId

SQL query HQL query
select QUAL_VALUE_ID from PMFM_STRAT_PMFM_QUAL_VALUE where PMFM_STRAT_ID = :pmfmStrategyId;
Parameters
Name Type Description
pmfmStrategyId java.lang.Integer  


Query surveyIdsWithInheritedGeometry

SQL query HQL query
select surveyimpl0_.SURVEY_ID as col_0_0_ from Q2_TU.SURVEY surveyimpl0_ where surveyimpl0_.MON_LOC_ID=:monLocId and surveyimpl0_.SURVEY_ACTUAL_POSITION='0'; SELECT surveyId FROM SurveyImpl WHERE monitoringLocation.monLocId = :monLocId AND surveyActualPosition = '0'
Parameters
Name Type Description
monLocId java.lang.Integer