- Extensibility Guide
- Advanced Conditions Extensibility
- Write Custom SQL in Advance Condition
- Write Custom SQL for Oracle Argus Mart Advance Condition
- Last Locked Revision for a Version in a Period (Case Creation Date) Point-in-Time Query
Last Locked Revision for a Version in a Period (Case Creation Date) Point-in-Time Query
The following are the steps to create custom SQL for Current Data point-in-time query:
- Login to Oracle Argus Insight.
- Navigate to Queries > Advance Condition > New (Argus Mart).
- From Query Type drop-down list, select Last Locked Revision for a Version in a Period.
The Last Locked Revision for a Version In a Period dialog box appears.
- Select Case Creation Date option, enter the date range in From and To fields, and click Save.
- Add a field, and save the advance condition.
- Click View SQL.
The Advanced Conditions SQL screen appears.
- Write the custom SQL as per the format given below:
Query Format:
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 CREATE_TIME >= To_Date ('<FROM_DATE>','DD-MON-YYYY HH24:MI:SS') AND CREATE_TIME < 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 CREATE_TIME >= To_Date ('01-JAN-2014 00:00:00','DD-MON-YYYY HH24:MI:SS') AND CREATE_TIME < 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 CREATE_TIME >= To_Date ('01-JAN-2014 00:00:00','DD-MON-YYYY HH24:MI:SS') AND CREATE_TIME < To_Date ('22-DEC-2015 23:59:59','DD-MON-YYYY HH24:MI:SS') GROUP BY CASE_ID) X, (SELECT CASE_ID, MAX(LOCKED_EFFECTIVE_START_DATE) AS EFFECTIVE_START_DATE FROM ALL_CASES_BY_RECEIPT_DATE WHERE CREATE_TIME >= To_Date ('01-JAN-2014 00:00:00','DD-MON-YYYY HH24:MI:SS') AND CREATE_TIME < 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 CREATE_TIME >= To_Date ('<FROM_DATE>','DD-MON-YYYY HH24:MI:SS') AND CREATE_TIME < 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 Last Locked Revision for a Version in a Period (Case Creation Date).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 Last Locked Revision for a Version in a Period (Case Creation Date) type.
- Join with (SELECT CASE_ID, MAX(LOCKED_EFFECTIVE_START_DATE) AS EFFECTIVE_START_DATE FROM ALL_CASES_BY_RECEIPT_DATE WHERE CREATE_TIME >= To_Date ('<FROM_DATE>','DD-MON-YYYY HH24:MI:SS') AND CREATE_TIME < To_Date ('<TO_DATE>','DD-MON-YYYY HH24:MI:SS') GROUP BY CASE_ID) X is required to get all post locked revisions of each cases.
- ALL_CASES_BY_RECEIPT_DATE table contains Create Time and corresponding post lock revision effective start date.
- 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.
- Make sure the query begins with
Parent topic: Write Custom SQL for Oracle Argus Mart Advance Condition