As of Date Point-in-Time Query

The following are the steps to create custom SQL for As of Date 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 As of Date.
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:

   SELECT DISTINCT CASE_MASTER.CASE_ID,CASE_MASTER.EFFECTIVE_START_DATE
   FROM CASE_MASTER, <additional table(s)>
   WHERE <filter clause(s)>
   AND CASE_MASTER.EFFECTIVE_START_DATE <= To_Date ('<DATE_FOR_AS_OF_DATE>','DD-MON-YYYY HH24:MI:SS')
   AND CASE_MASTER.EFFECTIVE_END_DATE > To_Date (<DATE_FOR_AS_OF_DATE>,'DD-MON-YYYY HH24:MI:SS')
   

   Example 1: Custom SQL using a single table

   SELECT DISTINCT CASE_MASTER.CASE_ID,CASE_MASTER.EFFECTIVE_START_DATE FROM CASE_MASTER
   WHERE ((UPPER(case_master.case_num)=UPPER('CASE100')))
   AND  CASE_MASTER.EFFECTIVE_START_DATE <= To_Date ('22-DEC-2015 14:12:07','DD-MON-YYYY HH24:MI:SS') 
   AND CASE_MASTER.EFFECTIVE_END_DATE > To_Date ('22-DEC-2015 14:12:07','DD-MON-YYYY HH24:MI:SS')
   

   Example 2: Custom SQL using two or more tables

   SELECT DISTINCT CASE_MASTER.CASE_ID,CASE_MASTER.EFFECTIVE_START_DATE FROM CASE_MASTER,
   (SELECT * FROM CASE_PARENT_INFO  WHERE  CASE_PARENT_INFO.EFFECTIVE_START_DATE <= To_Date ('22-DEC-2015 14:12:07','DD-MON-YYYY HH24:MI:SS')AND CASE_PARENT_INFO.EFFECTIVE_END_DATE > To_Date ('22-DEC-2015 14:12:07','DD-MON-YYYY HH24:MI:SS') ) 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.EFFECTIVE_START_DATE <= To_Date ('22-DEC-2015 14:12:07','DD-MON-YYYY HH24:MI:SS')
   AND CASE_MASTER.EFFECTIVE_END_DATE > To_Date ('22-DEC-2015 14:12:07','DD-MON-YYYY HH24:MI:SS'

   Note:

   • Make sure the query begins with SELECT DISTINCT CASE_MASTER.CASE_ID,CASE_MASTER.EFFECTIVE_START_DATE FROM CASE_MASTER.
   • All the tables other than CASE_MASTER should be in format (SELECT * FROM <TABLE_NAME> WHERE <TABLE_NAME>.EFFECTIVE_START_DATE <= To_Date ('< DATE_FOR_AS_OF_DATE >','DD-MON-YYYY HH24:MI:SS') AND <TABLE_NAME>.EFFECTIVE_END_DATE > To_Date ('<DATE_FOR_AS_OF_DATE> ','DD-MON-YYYY HH24:MI:SS')) <TABLE_NAME> to execute query as As of 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 tables, then the query will execute and case series will be generated, but the result may not be of As of Date type.
   • 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.