Aggregate Queries

The following are the steps to create custom SQL for Current Data point-in-time query:

1. Login to Oracle Argus Insight.
2. Navigate to Queries > Advance Condition > New (Argus Mart).
3. From Query Type drop-down list, select Aggregate Queries.
4. Add a field, and save the advance condition.
5. Click View SQL.

   The Advanced Conditions SQL screen appears.

6. Write the custom SQL as per the format given below:

   Query Format:

   • When unlocked revisions are not required.

     SELECT DISTINCT CASE_MASTER.CASE_ID,CASE_MASTER.EFFECTIVE_START_DATE 
     FROM CASE_MASTER, 
     (SELECT CASE_ID, MAX(LOCKED_EFFECTIVE_START_DATE) AS EFFECTIVE_START_DATE FROM ALL_CASES_BY_RECEIPT_DATE WHERE RECEIPT_DATE >= To_Date ('<FROM_DATE>','DD-MON-YYYY HH24:MI:SS')  AND RECEIPT_DATE < To_Date ('<TO_DATE>','DD-MON-YYYY HH24:MI:SS') GROUP BY CASE_ID) X, <additional table(s)>
     WHERE  <filter clause(s)>
     AND  CASE_MASTER.CASE_ID = X.CASE_ID 
     AND CASE_MASTER.EFFECTIVE_START_DATE <= X.EFFECTIVE_START_DATE 
     AND CASE_MASTER.EFFECTIVE_END_DATE > X.EFFECTIVE_START_DATE
     

   • When unlocked revisions are required.

     SELECT DISTINCT CASE_MASTER.CASE_ID,CASE_MASTER.EFFECTIVE_START_DATE 
     FROM CASE_MASTER, 
     (SELECT CASE_ID, NVL(MAX(LOCKED_EFFECTIVE_START_DATE), MAX(UNLOCKED_EFFECTIVE_START_DATE)) AS EFFECTIVE_START_DATE FROM ALL_CASES_BY_RECEIPT_DATE WHERE RECEIPT_DATE >= To_Date ('<FROM_DATE>','DD-MON-YYYY HH24:MI:SS')  AND RECEIPT_DATE < To_Date ('<TO_DATE>','DD-MON-YYYY HH24:MI:SS') GROUP BY CASE_ID )X , <additional table(s)>  
     WHERE  <filter clause(s)> 
     AND  CASE_MASTER.CASE_ID = X.CASE_ID 
     AND CASE_MASTER.EFFECTIVE_START_DATE <= X.EFFECTIVE_START_DATE 
     AND CASE_MASTER.EFFECTIVE_END_DATE > X.EFFECTIVE_START_DATE
     

   Example 1: Custom SQL using a single table

   SELECT DISTINCT CASE_MASTER.CASE_ID,CASE_MASTER.EFFECTIVE_START_DATE 
   FROM CASE_MASTER, 
   (SELECT CASE_ID, MAX(LOCKED_EFFECTIVE_START_DATE) AS EFFECTIVE_START_DATE FROM ALL_CASES_BY_RECEIPT_DATE WHERE RECEIPT_DATE >= To_Date ('01-JAN-2014 00:00:00','DD-MON-YYYY HH24:MI:SS') AND RECEIPT_DATE < To_Date ('22-DEC-2015 23:59:59','DD-MON-YYYY HH24:MI:SS') GROUP BY CASE_ID) X  
   WHERE  ((UPPER(case_master.case_num) =UPPER('CASE100')))
   AND CASE_MASTER.CASE_ID = X.CASE_ID 
   AND CASE_MASTER.EFFECTIVE_START_DATE <= X.EFFECTIVE_START_DATE  
   AND CASE_MASTER.EFFECTIVE_END_DATE > X.EFFECTIVE_START_DATE 
   

   Example 2: Custom SQL using two or more tables

   SELECT DISTINCT CASE_MASTER.CASE_ID,CASE_MASTER.EFFECTIVE_START_DATE 
   FROM CASE_MASTER, 
   (SELECT CASE_ID, MAX(LOCKED_EFFECTIVE_START_DATE) AS EFFECTIVE_START_DATE FROM ALL_CASES_BY_RECEIPT_DATE WHERE RECEIPT_DATE >= To_Date ('01-JAN-2014 00:00:00','DD-MON-YYYY HH24:MI:SS') AND RECEIPT_DATE < To_Date ('22-DEC-2015 23:59:59','DD-MON-YYYY HH24:MI:SS') GROUP BY CASE_ID) X, 
   (SELECT CASE_PARENT_INFO.* 
   FROM CASE_PARENT_INFO, (SELECT CASE_ID, MAX(LOCKED_EFFECTIVE_START_DATE) AS EFFECTIVE_START_DATE FROM ALL_CASES_BY_RECEIPT_DATE WHERE RECEIPT_DATE >= To_Date ('01-JAN-2014 00:00:00','DD-MON-YYYY HH24:MI:SS') AND RECEIPT_DATE < To_Date ('22-DEC-2015 23:59:59','DD-MON-YYYY HH24:MI:SS') GROUP BY CASE_ID ) X WHERE  CASE_PARENT_INFO.CASE_ID = X.CASE_ID and CASE_PARENT_INFO.EFFECTIVE_START_DATE <= X.EFFECTIVE_START_DATE AND CASE_PARENT_INFO.EFFECTIVE_END_DATE > X.EFFECTIVE_START_DATE) 
   CASE_PARENT_INFO 
   WHERE (CASE_MASTER.CASE_ID = CASE_PARENT_INFO.CASE_ID  
   AND ((UPPER(case_master.case_num) =UPPER('CASE100')) 
   AND  (case_parent_info.gender_id=1))) 
   AND CASE_MASTER.CASE_ID = X.CASE_ID 
   AND CASE_MASTER.EFFECTIVE_START_DATE <= X.EFFECTIVE_START_DATE  
   AND CASE_MASTER.EFFECTIVE_END_DATE > X.EFFECTIVE_START_DATE 

   Note:

   • Make sure the query begins with SELECT DISTINCT CASE_MASTER.CASE_ID,CASE_MASTER.EFFECTIVE_START_DATE FROM CASE_MASTER.
   • All tables other than CASE_MASTER should be in format (SELECT <TABLE_NAME>.* FROM <TABLE_NAME>, (SELECT CASE_ID, MAX(LOCKED_EFFECTIVE_START_DATE) AS EFFECTIVE_START_DATE FROM ALL_CASES_BY_RECEIPT_DATE WHERE RECEIPT_DATE >= To_Date ('<FROM_DATE>','DD-MON-YYYY HH24:MI:SS') AND RECEIPT_DATE < To_Date ('<TO_DATE>','DD-MON-YYYY HH24:MI:SS') GROUP BY CASE_ID ) X WHERE <TABLE_NAME>.CASE_ID = X.CASE_ID and <TABLE_NAME>.EFFECTIVE_START_DATE <= X.EFFECTIVE_START_DATE AND <TABLE_NAME>.EFFECTIVE_END_DATE > X.EFFECTIVE_START_DATE ) <TABLE_NAME> to execute query as Aggregate Queries.

     If the table does not have EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns then no inner view is required.

   • If you do not include EFFECTIVE_START_DATE and EFFECTIVE_END_DATE clause with all the tables, then the query will execute and case series will be generated, but the result may not be of Aggregate Queries type.
   • Join with (SELECT CASE_ID, MAX(LOCKED_EFFECTIVE_START_DATE) AS EFFECTIVE_START_DATE FROM ALL_CASES_BY_RECEIPT_DATE WHERE RECEIPT_DATE >= To_Date ('<FROM_DATE>','DD-MON-YYYY HH24:MI:SS') AND RECEIPT_DATE < To_Date ('<TO_DATE>','DD-MON-YYYY HH24:MI:SS') GROUP BY CASE_ID ) X is required to get all post locked revisions of cases for each Receipt Date.
   • ALL_CASES_BY_RECEIPT_DATE table contains Receipt Date and corresponding post lock revision effective start date.
   • NVL(MAX(LOCKED_EFFECTIVE_START_DATE), MAX(UNLOCKED_EFFECTIVE_START_DATE)) AS EFFECTIVE_START_DATE provides latest unlocked version when there is not locked version available for the selected date range.
   • Make sure the query is well formatted and executable without any parameters.
   • Do not use ";" at the end of the query.
   • Do not use comments in the query.