You can extend the feature of performing the search using the advanced conditions by creating queries on new fields defined with custom configurations.
This chapter covers the steps involved in creating and configuring the new custom fields, and writing custom SQL for both Insight Mart and Argus Mart.
To configure the new fields, you may need to do some configuration in the following tables:
CMN_FIELDS — See Section 1.1, "Configuring CMN_FIELDS Table."
CMN_FIELD_CONFIGURATION — See Section 1.2, "Configuring CMN_FIELD_CONFIGURATION Table."
CMN_COMPLEXFIELD_CONFIGURATION — See Section 1.3, "Configuring CMN_COMPLEXFIELD_CONFIGURATION Table."
To write custom SQL, see Section 1.4, "Writing Custom SQL in Advance Condition."
You can configure the advance condition extensibility columns for the new field ID in the database table CMN_FIELDS as given below:
Table 1-1 CMN _FIELDS Column Details
Column | Sample Value for Insight Mart | Sample Value for Argus Mart | Description |
---|---|---|---|
ENTERPRISE_ID |
3 |
3 |
Defines the current Enterprise ID. This is a mandatory column. |
FIELD_ID |
30000000 |
30000000 |
Defines the Argus Insight new field ID that must be unique and must be in the following range:
All other IDs are reserved for Oracle. This is a mandatory column. |
FIELD_LABEL |
Custom Product Country |
Custom Product Country |
Defined the field label having maximum length of 200 characters. This is a mandatory column. |
TABLE_NAME |
V_RPT_PRODUCT |
CASE_PRODUCT |
Defines the table name that contains the column for search criteria. The maximum length for the table name is 50 characters. This is a mandatory column. |
COLUMN_NAME |
COUNTRY_ID |
COUNTRY_ID |
Defines the column name for the search criteria. This column name must exist in table populated in TABLE_NAME. The maximum length of this column is 50 characters. This is a mandatory column. |
JOIN_FIELD |
COUNTRY_ID |
COUNTRY_ID |
Defines the column name if this field is of drop-down type on UI. This column contains the name of column that you want to use for join condition between the tables populated in TABLE_NAME and SELECT_TABLE. This is an optional column. |
SELECT_TABLE |
LM_COUNTRIES |
LM_COUNTRIES |
Defines the table name if this field is of drop-down type on UI. This column contains the name of table that you want to use to populate the drop-down values. This is an optional column. |
SELECT_COLUMN |
COUNTRY |
COUNTRY |
Defines the column name if this field is of drop-down type on UI. This column contains the name of column that you want to use to populate for the drop-down values. This is an optional column. |
ADV_COND_FIELD |
1 |
1 |
Contains the value for the new field ID as 1. This is a mandatory column. |
TREE_VIEW |
PRODUCTS:Product Information |
PRODUCTS:Product Information |
Defines the hierarchical structure of field in advance condition tree on Advance Condition Editor page. The first level and second level node of the tree must be separated by character ":".For example, First Level Tree Node: Second Level Tree Node This is a mandatory column. |
SQL_SELECT |
SELECT 1 ID, 'UNITED STATE' STATUS FROM DUAL UNION SELECT 2, 'UNITED KINGDOM' FROM DUAL UNION SELECT 3, 'INDIA' FROM DUAL |
SELECT 1 ID, 'UNITED STATE' STATUS FROM DUAL UNION SELECT 2, 'UNITED KINGDOM' FROM DUAL UNION SELECT 3, 'INDIA' FROM DUAL |
Defines the SQL query if this field is of drop-down type on UI. This column contains the selected query that you want to use to populate the drop-down values. This select query must contain the columns ID and STATUS. Note: If this column is configured then the values configured in columns SELECT_COLUMN, SELECT_TABLE and JOIN_FIELD will be ignored. This is an optional column. |
HIDDEN |
0 |
0 |
Contains the value for the new field ID as 0. This is a mandatory column. |
CONTROL_TYPE_ID |
2 |
2 |
Defines the ID of the control that you want to display on UI.Refer to the table CMN_CONTROL_TYPE for supported Control Type ID.1 - Textbox2 - Dropdown3 - DatePicker4 - DateTimePicker5 - Numeric Control TypeThis is a mandatory column. |
ADDITIONAL_TABLE_LIST |
RPT_EVENT |
CASE_EVENT |
Defines the comma separated table list that is to be added in From clause of final SQL query except table name entered in the column Table_Name, and:
This field is required only if any additional join tables are required. This is an optional column. |
ADDITIONAL_WHERE |
V_RPT_PRODUCT.SEQ_NUM = RPT_EVENT.SEQ_NUM AND V_RPT_PRODUCT. COUNTRY_ID > 0 |
CASE_PRODUCT.SEQ_NUM = CASE_EVENT.SEQ_NUM AND CASE_PRODUCT. COUNTRY_ID > 0 |
Defines the additional Where clause that you want to add in the final SQL query of advance condition. This is an optional column. |
DATA_SOURCE_ID |
1 |
2 |
Defines the value of the target data source (Insight Mart/Argus Mart). This is a mandatory column. |
To configure remaining columns of the database table CMN_FIELDS, refer to the DBA Guide.
The Argus Insight supports different field types. The field ID that belongs to one or more field types must be configured in the database table CMN_FIELD_CONFIGURATION.
Note:
One field can belong to one or more field types.Table 1-2 Argus Insight Supported Field Types
Field Type ID | Field Type | Description |
---|---|---|
1 |
SMQ_NARROW |
The field configured as SMQ_NARROW field type identifies cases that are highly likely to represent the condition of interest. Narrow search consists of all PTs that indicate the condition with great certainty. To configure this field type, see Section 1.2.1, "Configuring SMQ_NARROW Field Type". |
2 |
SMQ_BROAD |
The field configured as SMQ_BROAD field type identifies all possible cases, including some that may prove to be of little or no interest on closer inspection. Those are highly likely to represent the condition of interest. Field as SMQ_BROAD field type search includes both the narrow terms and the additional broad terms, often of less-specific nature. To configure this field type, see Section 1.2.2, "Configuring SMQ_BROAD Field Type". |
3 |
MEDDRA |
The field configured as MEDDRA field type enables the MedDRA menu to open MedDRA browser. To configure this field type, see Section 1.2.3, "Configuring MEDDRA Field Type". |
4 |
WHO |
The field configured as WHO field type enables the WHO menu to open WHO Drug browser. To configure this field type, see Section 1.2.4, "Configuring WHO Field Type". |
5 |
COMPANY_DRUG |
The field configured as COMPANY_DRUG field type enables the Company Drug menu to open Product browser. To configure this field type, see Section 1.2.5, "Configuring COMPANY_DRUG Field Type". |
6 |
INGREDIENT |
The field configured as INGREDIENT field type enables the Ingredient menu to open Ingredient browser. To configure this field type, see Section 1.2.6, "Configuring INGREDIENT Field Type". |
7 |
MINUTES_CALCULATOR |
The field configured as MINUTES_CALCULATOR field type enables the Minutes Calculator menu to open Minutes Calculator browser. To configure this field type, see Section 1.2.7, "Configuring MINUTES_CALCULATOR Field Type". |
8 |
LITERATURE |
The field configured as LITERATURE field type enables the Literature menu to open Literature browser. To configure this field type, see Section 1.2.8, "Configuring LITERATURE Field Type". |
9 |
EVENT_LICENSE |
The field configured as EVENT_LICENSE field type enables the Event License menu to open Event License browser. To configure this field type, see Section 1.2.9, "Configuring EVENT_LICENSE Field Type". |
10 |
STUDY_DRUG |
The field configured as STUDY_DRUG field type enables the Study Drug menu to open Product browser. To configure this field type, see Section 1.2.10, "Configuring STUDY_DRUG Field Type". |
11 |
CLINICAL_STUDY_LOOKUP |
The field configured as CLINICAL_STUDY_LOOKUP field type enables the Literature menu to open Clinical Study Lookup browser. To configure this field type, see Section 1.2.11, "Configuring CLINICAL_STUDY_LOOKUP Field Type". |
12 |
BATCH_LOT_NO |
The field configured as BATCH_LOT_NO field type enables the Batch Lot # menu to open Batch/Lot Number browser. To configure this field type, see Section 1.2.12, "Configuring BATCH_LOT_NO Field Type". |
13 |
INVESTIGATIONAL_DRUG |
The field configured as INVESTIGATIONAL_DRUG field type enables the Investigational Drug menu to open Investigational Drug browser. To configure this field type, see Section 1.2.13, "Configuring INVESTIGATIONAL_DRUG Field Type". |
14 |
CO_DRUG_CODE_WITH_STUDY |
The field configured as CO_DRUG_CODE_WITH_STUDY field type enables the Co-Drug Code menu to open Batch/Lot Number browser. To configure this field type, see Section 1.2.14, "Configuring CO_DRUG_CODE_WITH_STUDY Field Type". |
15 |
DVB |
The field configured as DVB field type enables the specific range called Duration Value Bands. To configure this field type, see Section 1.2.15, "Configuring DVB Field Type". |
16 |
GENERIC |
The field configured as GENERIC field type enables the Generic Name menu to open Generic Name browser. To configure this field type, see Section 1.2.16, "Configuring GENERIC Field Type". |
17 |
PATIENT_HISTORY |
The field configured as PATIENT_HISTORY field type is considered as information of the patient. To configure this field type, see Section 1.2.17, "Configuring PATIENT_HISTORY Field Type". |
18 |
PARTIAL_DATE |
The field configured as PARTIAL_DATE field type allows the user to enter the partial date. To configure this field type, see Section 1.2.18, "Configuring PARTIAL_DATE Field Type". |
19 |
CLOB |
The field configured as COLB field type is considered as field with data type CLOB of column configured in CMN_FIELDS.COLUMN_NAME. To configure this field type, see Section 1.2.19, "Configuring CLOB Field Type". |
20 |
PARENT_HISTORY |
The field configured as PARENT_HISTORY field type is considered as information about patient's parent. To configure this field type, see Section 1.2.20, "Configuring PARENT_HISTORY Field Type". |
You can configure a field id as SMQ_NARROW field type. This field type identifies cases that are highly likely to represent the condition of interest. Narrow search consists of all PTs that indicate the condition with great certainly.
To configure the new field ID as SMQ_NARROW field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-3 Configurations for Field Type SMQ_NARROW
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
1 |
SMQ_NARROW |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
PT/LLT/ADDITIONALWHERE |
This column contains the key as PT, LLT, or ADDITIONALWHERE. One row for each PT (Preffered Term) and LLT (Low Level Term) is mandatory while row with key as ADDITIONALWHERE is optional. |
VALUE |
RPT_EVENT.ART_CODE/RPT_EVENT.INC_CODE/RPT_EVENT.ISPRIMARY = 1 |
If KEY= PT thenAdd <<Table Name>>.<<Column name>> which contains PT code. If KEY= LLT thenAdd <<Table Name>>.<<Column>> name which contains LLT code.If KEY= ADDITIONALWHERE thenIf any additional WHERE condition is required. |
Note:
You can also refer to existing field EVENTS >Primary Event > Event SMQ (Narrow), (CMN_FIELDS.Field_ID - 201760627) of SMQ_NARROW field type.You can configure a field ID as SMQ_BROAD field type. This field type identifies all possible cases, including some that may prove to be of little or no interest on closer inspection. Those are highly likely to represent the condition of interest. This field type search includes both the narrow terms and additional broad terms, often of less-specific nature.
To configure the new field ID as SMQ_BROAD field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-4 Configurations for Field Type SMQ_BROAD
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
2 |
SMQ_BROAD |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
PT/LLT/ADDITIONALWHERE |
This column contains the key as PT, LLT or ADDITIONALWHERE. One row for each PT and LLT is mandatory while row with key as ADDITIONALWHERE is optional. |
VALUE |
RPT_EVENT.ART_CODE/RPT_EVENT.INC_COD/RPT_EVENT.ISPRIMARY = 1 |
If KEY= PT then Add <<Table Name>>.<<Column name>> which contains PT code. If KEY= LLT then Add <<Table Name>>.<<Column>> name which contains LLT code. If KEY= ADDITIONALWHERE then If any additional WHERE condition is required. |
Note:
You can also refer to existing field EVENTS > Primary Event > Event SMQ (Broad), (CMN_FIELDS.Field_ID - 201760628) of SMQ_NARROW field type.You can configure a field id as MedDRA field type. This field type enables you to open the MedDRA browser from menu. With this browser you can search the following:
HLGT — High Level Group Term
HLT — High Level Term
LLT — Low Level Term
PT — Preferred Term
SOC — System Organ Class
To configure the new field ID as MEDDRA field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-5 Configurations for Field Type MEDDRA
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
3 |
MEDDRA |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
SOC_CODE |
Enter the Return Type text. See Table 1-6, "Supported Return Type Key/Value for Field Type MEDDRA". This is a mandatory column. |
VALUE |
1 |
Enter the Return Type ID. See Table 1-6, "Supported Return Type Key/Value for Field Type MEDDRA". This is a mandatory column. |
Note:
You can also refer to existing field EVENTS > Primary Event > Event SMQ (Narrow), (CMN_FIELDS.Field_ID - 201760627) of SMQ_NARROW field type.Supported Return Type Texts and IDs (Key/Value):
The MedDRA browser returns one the following texts as per the return type configured against the new field ID to the Advance Condition Editor page.
Table 1-6 Supported Return Type Key/Value for Field Type MEDDRA
Return Type ID | |
---|---|
SOC_CODE |
1 |
SOC_NAME |
2 |
HLGT_CODE |
3 |
HLGT_NAME |
4 |
HLT_CODE |
5 |
HLT_NAME |
6 |
PT_CODE |
7 |
PT_NAME |
8 |
LLT_CODE |
9 |
LLT_NAME |
10 |
Note:
You can also refer to existing field EVENTS > Event Information > Event Body System Code, (CMN_FIELDS.Field_ID - 201450542) of MEDDRA field type.You can configure a field id as WHO field type. This field type enables you to open the WHO browser from menu. This browser searches the product from WHO Drug Dictionary. With this browser you can search for the following:
ATC Code/Description
Country
Formation
Ingredient
Medical Prod ID
Trade Name
To configure the new field ID as WHO field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-7 Configurations for Field Type WHO
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
4 |
WHO |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
PROD_NAME |
Enter the Return Type text. See Table 1-8, "Supported Return Type Key/Value for Field Type WHO". This is a mandatory column. |
VALUE |
1 |
Enter the Return Type ID. See Table 1-8, "Supported Return Type Key/Value for Field Type WHO". This is a mandatory column. |
Supported Return Type Texts and IDs (Key/Value):
The WHO browser returns one the following texts as per the return type configured against the new field ID to the Advance Condition Editor page.
Table 1-8 Supported Return Type Key/Value for Field Type WHO
Return Type Text | Return Type ID |
---|---|
PROD_NAME |
1 |
ATC_DESC |
2 |
ATC_CODE |
3 |
DRUG_CODE |
4 |
MED_PROD_ID |
5 |
Note:
You can also refer to existing field Products > Product Drug/Vaccine > Drug Code, (CMN_FIELDS.Field_ID - 203650840) of WHO field type.You can configure a field ID as COMPANY_DRUG field type. This field type enables the Company Drug menu to open the Product browser. With this browser you can search the following:
Ingredient
Product Family
Product Name
Trade Name
To configure the new field ID as COMPANY_DRUG field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-9 Configurations for Field Type COMPANY_DRUG
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
5 |
COMPANY_DRUG |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
PRODUCT_NAME |
Enter the Return Type text. See Table 1-10, "Supported Return Type Key/Value for Field Type COMPANY_DRUG". This is a mandatory column. |
VALUE |
1 |
Enter the Return Type ID. See Table 1-10, "Supported Return Type Key/Value for Field Type COMPANY_DRUG". This is a mandatory column. |
Supported Return Type Texts and IDs (Key/Value):
The Company Drug browser returns one the following texts as per the return type configured against the new field ID to the Advance Condition Editor page.
Table 1-10 Supported Return Type Key/Value for Field Type COMPANY_DRUG
Return Type Text | Return Type ID |
---|---|
PROD_NAME |
1 |
PRODUCT_ID |
2 |
INGREDIENT_NAME |
3 |
TRADE_NAME |
4 |
Note:
You can also refer to existing field Products > Product Information > Company Product, (CMN_FIELDS.Field_ID - 203650960) of COMPANY_DRUG field type.You can configure a field ID as INGREDIENT field type. This field type enables the Ingredient menu to open Ingredient Browser. With this browser you can search ingredient.To configure the new field ID as INGREDIENT field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-11 Configurations for Field Type INGREDIENT
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
6 |
INGREDIENT |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
INGREDIENT |
Enter the Return Type text. This is an optional column. |
VALUE |
1 |
Enter the Return Type ID as 1. This is a mandatory column. |
Note:
You can also refer to existing field PRODUCTS > Product Information > Ingredient, (CMN_FIELDS.Field_ID - 203810990) of INGREDIENT field type.You can configure a field ID as MINUTES_CALCULATOR field type. This field type enables the Minutes Calculator menu to open the Duration Calculator Browser from menu. This browser allows you enter the time in hours, day, weeks, months, or year, and then converts the time to minutes/seconds. Alternatively, you can select duration band and value, if available.To configure the new field ID as MINUTES_CALCULATOR field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-12 Configurations for Field Type MINUTES_CALCULATOR
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
7 |
MINUTES_CALCULATOR |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
DVB_SEC |
Enter the Return Type text. See Table 1-13, "Supported Return Type Key/Value for Field Type MINUTES_CALCULATOR". This is a mandatory column. |
VALUE |
1 |
Enter the Return Type ID. See Table 1-13, "Supported Return Type Key/Value for Field Type MINUTES_CALCULATOR". This is a mandatory column. |
Supported Return Type Texts and IDs (Key/Value):
The Duration Calculator browser returns one the following texts as per the return type configured against the new field ID to the Advance Condition Editor page.
Table 1-13 Supported Return Type Key/Value for Field Type MINUTES_CALCULATOR
Return Type Text | Return Type ID |
---|---|
DVB_MIN |
0 |
DVB_SEC |
1 |
NOTDVB_SEC |
3 |
Note:
You can also refer to existing field PRODUCTS > Dosage Regimen > Duration of Regimen, (CMN_FIELDS.Field_ID - 201311457) of INGREDIENT field type.You can configure a field ID as LITERATURE field type. This field type enables the Literature menu to open the Literature browser from menu. With this browser you can search literature.To configure the new field ID as LITERATURE field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-14 Configurations for Field Type LITERATURE
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
8 |
LITERATURE |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
NULL |
Enter NULL. |
VALUE |
1 |
Enter the Return Type ID as 1. This is a mandatory column. |
Note:
You can also refer to existing field GENERAL > Literature > Literature, (CMN_FIELDS.Field_ID - 202810741) of INGREDIENT field type.You can configure a field ID as EVENT_LICENSE field type. This field type enables the Event License menu to open the Event License browser from menu. With this browser you can search events.
To configure the new field ID as EVENT_LICENSE field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-15 Configurations for Field Type EVENT_LICENSE
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
9 |
EVENT_LICENSE |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
NULL |
Enter NULL. |
VALUE |
1 |
Enter the Return Type ID as 1. This is a mandatory column. |
Note:
You can also refer to existing field EVENTS > Event Assessment > Event Assessment License, (CMN_FIELDS.Field_ID - 201510613) of EVENT_LICENSE field type.You can configure a field ID as STUDY_DRUG field type. This field type enables the Study Drug menu to open the Study Drug Lookup browser from menu. With this browser you can search study drugs.To configure the new field ID as STUDY_DRUG field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-16 Configurations for Field Type STUDY_DRUG
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
10 |
STUDY_DRUG |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
PROD_NAME |
Enter the Return Type text as PROD_NAME. This is a mandatory column. |
VALUE |
NOR |
Enter the Return Type ID as NOR. This is a mandatory column. |
Note:
You can also refer to existing field PRODUCTS > Product Information > Study Drug, (CMN_FIELDS.Field_ID - 203650965) of STUDY_DRUG field type.You can configure a field ID as CLINICAL_STUDY_LOOKUP field type. This field type enables the clinical study lookup menu to open the Clinical Study Lookup browser from menu. With this browser you can search study information for clinical studies based on the following:
Center ID
Project ID
Study ID
To configure the new field ID as CLINICAL_STUDY_LOOKUP field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-17 Configurations for Field Type CLINICAL_STUDY_LOOKUP
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
11 |
CLINICAL_STUDY_LOOKUP |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
CENTERID |
Enter the Return Type text. See Table 1-18, "Supported Return Type Key/Value for Field Type CLINICAL_STUDY_LOOKUP". This is a mandatory column. |
VALUE |
1 |
Enter the Return Type ID. See Table 1-18, "Supported Return Type Key/Value for Field Type CLINICAL_STUDY_LOOKUP". This is a mandatory column. |
Supported Return Type Texts and IDs (Key/Value):
The Clinical Study Lookup browser returns one the following texts as per the return type configured against the new field ID to the Advance Condition Editor page.
Table 1-18 Supported Return Type Key/Value for Field Type CLINICAL_STUDY_LOOKUP
Return Type Text | Return Type ID |
---|---|
CENTERID |
1 |
STUDYID |
2 |
PROJECTID |
3 |
Note:
You can also refer to existing field GENERAL > Case Study > Center ID, (CMN_FIELDS.Field_ID - 200650348) of CLINICAL_STUDY_LOOKUP field type.You can configure a field ID as BATCH_LOT_NO field type. This field type enables the Batch Lot # menu to open the Batch Lot # Lookup browser from menu. With this browser you can search batch or lot number.To configure the new field ID as BATCH_LOT_NO field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-19 Configurations for Field Type BATCH_LOT_NO
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
12 |
BATCH_LOT_NO |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
NULL |
Enter Return type text as NULL. |
VALUE |
NULL |
Enter the Return Type ID as NULL. |
Note:
You can also refer to existing field PRODUCTS > Dosage Regimen > Batch/Lot #, (CMN_FIELDS.Field_ID - 201350479) of BATCH_LOT_NO field type.You can configure a field ID as INVESTIGATIONAL_DRUG field type. This field type enables the Investigational Drug menu to open the Investigational Drug browser from menu. With this browser you can search and select investigational drug. To configure the new field ID as INVESTIGATIONAL_DRUG field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-20 Configurations for Field Type INVESTIGATIONAL_DRUG
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
13 |
INVESTIGATIONAL_DRUG |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
NULL |
Enter Return type text as NULL. |
VALUE |
NULL |
Enter the Return Type ID as NULL. |
Note:
You can also refer to existing field PRODUCTS > Product Information > Investigational Drug, (CMN_FIELDS.Field_ID - 203610883) of INVESTIGATIONAL_DRUG field type.You can configure a field ID as CO_DRUG_CODE_WITH_STUDY field type. This field type enables the Co-Drug Code w Study menu to open the Co-Drug Code w Study browser from menu. With this browser you can search and select co-drug code with study. To configure the new field ID as CO_DRUG_CODE_WITH_STUDY field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-21 Configurations for Field Type CO_DRUG_CODE_WITH_STUDY
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
14 |
CO_DRUG_CODE_WITH_STUDY |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
NULL |
Enter key as NULL |
VALUE |
NULL |
Enter the value as NULL |
Note:
You can also refer to existing field PRODUCTS > Product Drug/Vaccine > Co-Drug Code w Study, (CMN_FIELDS.Field_ID - 203650861) of CO_DRUG_CODE_WITH_STUDY field type.You can configure a field ID as DVB field type. This field type enables the specific range called the Duration Value Bands (DVB). With this field type, you can specify query criteria for the configured field based on ranges instead of specific values.
Note:
All the field IDs configured as DVB field type must also be configured as MINUTES_CALCULATOR field type to open the Minutes Calculator browser.Table 1-22 Configurations for Field Type DVB
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
15 |
DVB |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
HOURS |
Enter the Return Type text. See Table 1-23, "Supported Return Type Key/Value for Field Type DVB". This is a mandatory column. |
VALUE |
DUR_HR_BAND |
Enter the Return Type ID. See Table 1-23, "Supported Return Type Key/Value for Field Type DVB". This is a mandatory column. |
Supported Return Type Texts and IDs (Key/Value):
The following keys must be configured for a field ID of field type as DVB. In the data table CMN_FIELD_CONFIGURATION, one row must be configured for each KEY . Value against all the keys should be a database column name. The database column name should exist in data table configured in CMN_FIELD.TABLE_NAME against the field ID. The following are the available keys for configuration:
Table 1-23 Supported Return Type Key/Value for Field Type DVB
Key | Sample Value |
---|---|
HOURS |
ONSET_LATENCY_ HRS _BAND |
DAYS |
ONSET_LATENCY_DAYS_BAND |
WEEKS |
ONSET_DELAY_ WEEKS _BAND |
MONTHS |
ONSET_LATENCY_MONTHS_BAND |
YEARS |
ONSET_DELAY_YEARS_BAND |
Note:
You can also refer to existing field EVENTS > Time to Onset from First Dose, (CMN_FIELDS.Field_ID - 201610626) of DVB field type.You can configure a field ID as GENERIC field type. This field type enables the Generic Name menu to open the Generic Name browser from menu. With this browser you can search and select generic name of a product. To configure the new field ID as GENERIC field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-24 Configurations for Field Type GENERIC
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
16 |
GENERIC |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
NULL |
Enter key as NUL |
VALUE |
NULL |
Enter value as NULL |
Note:
You can also refer to existing field PRODUCTS > Product Information > Generic Name, (CMN_FIELDS.Field_ID - 203650842) of GENERIC field type.You can configure a field ID as PATIENT_HISTORY field type, if the field is based on information about the patient. This field type adds an additional condition as PARENT = 0 in the WHERE clause of final SQL query for the field.
To configure the new field ID as PATIENT_HISTORY field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-25 Configurations for Field Type PATIENT_HISTORY
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
17 |
PATIENT_HISTORY |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
NULL |
Enter key as NULL. |
VALUE |
NULL |
Enter value as NULL. |
Note:
You can also refer to existing field PATIENT > Patient History > Relevant History Parent Information, (CMN_FIELDS.Field_ID - 203410798) of PATIENT_HISTORY.You can configure a field ID as PARTIAL_DATE field type. This field type displays the value "??-??-0000" in the control on UI. This field type allows the user to enter the partial date. A valid partial date must comprise either a year, or a year and a month.
To configure the new field ID as PARTIAL_DATE field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-26 Configurations for Field Type PARTIAL_DATE
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
18 |
PARTIAL_DATE |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
START_DATE |
Enter Key as column name configured in CMN_FIELDS.COLUMN_NAME. This is a mandatory column. |
VALUE |
START_DATE_RES |
Enter the column name as replacement of columns name configured in CMN_FIELDS.COLUMN_NAME if partial date is entered by the user. This is a mandatory column. |
Note:
PATIENT > Parent History > Stop Date is an existing field of PARTIAL_DATE type in CMN_FIELD_CONFIGURATION table.You can configure a field ID as CLOB field type, if the data type of column configured in CMN_FIELDS.COLUMN_NAME is CLOB. This field type supports the following advanced conditions:
Begins with
Contains
Does not contains
Missing
Exists
To configure the new field ID as CLOB field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-27 Configurations for Field Type CLOB
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
19 |
CLOB |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
NULL |
Enter Key as NULL. |
VALUE |
NULL |
Enter value as NULL. |
Note:
: You can also refer to existing field ANALYSIS > Case Narrative > Narrative, (CMN_FIELDS.Field_ID - 203050754) of CLOB field type.You can configure a field ID as PARENT_HISTORY field type, if the field is based on information about the patient's parent. This field type adds an additional condition as PARENT = 1 in the WHERE clause of final SQL query for the field.To configure the new field ID as PARENT_HISTORY field type, the following configurations are required in the database table CMN_FIELD_CONFIGURATION:
Table 1-28 Configurations for Field Type PARENT_HISTORY
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_TYPE_ID |
20 |
PARENT_HISTORY |
FIELD_ID |
30000000 |
Field ID entered in the table CMN_FIELDS. |
KEY |
NULL |
Enter Key as NULL. |
VALUE |
NULL |
Enter value as NULL. |
Note:
You can also refer to existing field PATIENT > Parent History > Relevant History Parent Information, (CMN_FIELDS.Field_ID - 205050009) of PARENT_HISTORY.The table CMN_COMPLEXFIELD_CONFIGURATION is used to configure fields that have very complex business logic. Beside, you can also use this table if you want to specify different condition for different operators in WHERE clause. You should define WHERE condition against each operator.
Table 1-29 CMN_COMPLEXFIELD_CONFIGURATION Column Details
Column | Sample Value | Description |
---|---|---|
ENTERPRISE_ID |
3 |
Current Enterprise ID |
FIELD_ID |
30000000 |
New Field ID |
OPERATOR |
contains |
Enter the desired operator to support the new Field ID. See Table 1-30, "Supported Operator List" for configuration. This is a mandatory column. |
SORT_ORDER |
6 |
Enter the sorting order of operator. This is a mandatory column. |
REQ_TABLE_LIST |
Add the common separated table list in FROM Clause of final SQL query except V_RPT_CASE, and table name entered in Table_Name Column. This is an optional column. |
|
WHERE_QUERY |
(UPPER(V_RPT_PRODUCT.PRODUCT_NAME) NOT LIKE UPPER('%PARAM_VALUE%') AND V_RPT_PRODUCT.pat_exposure > 0 ) |
Define the WHERE clause for the new field ID against the operator entered in Operator Column. This is a mandatory column. Note: Use the Place holder <<PARAM_VALUE>> in WHERE clause of SQL query, where selected value is to be placed. |
The following are the supported operators for the new field IDs:
Table 1-30 Supported Operator List
Operator | Description |
---|---|
equal to |
Retrieves cases where the selected attribute's value is equal to what the Value field specifies. |
not equal to |
Retrieves cases where the selected attribute's value is not equal to what the Value field specifies. |
greater than |
Retrieves cases where the selected attribute's value is greater than what the Value field specifies. |
greater than or equal to |
Retrieves cases where the selected attribute's value is greater than or equal to what the Value field specifies. |
less than |
Retrieves cases where the selected attribute's value is less than what the Value field specifies. |
less than or equal to |
Retrieves cases where the selected attribute's value is less than or equal to the Value that the field specifies. |
missing |
Retrieves cases where the selected attribute's value has not been specified. |
exists |
Retrieves cases where the selected attribute has any value. |
begins with |
Retrieves cases where the selected attribute's value begins with what the Value field specifies. |
contains |
Retrieves cases where the selected attribute's value contains what the Value field specifies. |
does not contain |
Retrieves cases where the selected attribute's value does not contain what the Value field specifies. |
in |
Retrieves cases where the selected attribute's value exists in what the Value field specifies. |
not in |
Retrieves cases where the selected attribute's value does not exist in what the Value field specifies. |
Note:
You can also refer to existing field PRODUCTS > Study Drug, (CMN_FIELDS.Field_ID - 203650965).You may write custom SQL for advanced conditions.
The following are the steps to create custom SQL for Argus Insight Advanced Condition:
Login to Argus Insight.
Navigate to Queries > Advance Condition > New (Insight Mart).
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 V_RPT_CASE.CASE_ID FROM V_RPT_CASE, <additionaltable(s)> WHERE <filter clause(s)>
Example 1: Custom SQL using a single table
SELECT DISTINCT V_RPT_CASE.CASE_ID FROM V_RPT_CASE WHERE ((UPPER(V_RPT_CASE.CASE_NUM)=UPPER('CASE001')))
Example 2: Custom SQL using two or more tables
SELECT DISTINCT V_RPT_CASE.CASE_ID FROM V_RPT_CASE, V_RPT_PRODUCT WHERE (V_RPT_CASE.CASE_ID = V_RPT_PRODUCT.CASE_ID AND ( (UPPER(V_RPT_CASE.CASE_NUM) =UPPER('CASE001')) AND (V_RPT_PRODUCT.COUNTRY_ID=223)))
Note:
Make sure the query begins with SELECT DISTINCT V_RPT_CASE.CASE_ID FROM V_RPT_CASE.
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.
Argus Insight provides different type of point-in-time queries. You may create custom SQL for any of these point-in-time queries.
The following sections comprise the procedures to create custom query for each type of point-in-time query:
Last Locked Revision for a Version in a Period (Case Receipt Date) Point-in-Time Query
Last Locked Revision for a Version in a Period (Case Locked 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 Argus Insight.
Navigate to Queries > Advance Condition > New (Argus Mart).
From Query Type drop-down list, select Current Data.
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, <additional table(s)> WHERE <filter clause(s)> AND CASE_MASTER.EFFECTIVE_END_DATE = '31-DEC-9999'
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_END_DATE = '31-DEC-9999'
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_END_DATE = '31-DEC-9999') 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_END_DATE = '31-DEC-9999'
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_END_DATE = '31-DEC-9999' ) <TABLE_NAME> to execute query as Current Data.
If the table does not have EFFECTIVE_START_DATE column then no inner view is required.
If you do not include EFFECTIVE_END_DATE = '31-DEC-9999' clause with all the tables, then the query will execute and case series will be generated, but the result may not be of Current Data 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.
The following are the steps to create custom SQL for As of Date point-in-time query:
Login to Argus Insight.
Navigate to Queries > Advance Condition > New (Argus Mart).
From Query Type drop-down list, select As of Date.
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, <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.
The following are the steps to create custom SQL for Current Data point-in-time query:
Login to Argus Insight.
Navigate to Queries > Advance Condition > New (Argus Mart).
From Query Type drop-down list, select At Lock.
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, VALIDSTART AS EFFECTIVE_START_DATE, DATE_LOCKED FROM CASE_ALL_LOCKED_REV WHERE USER_LOCKED = 1 ) X , <additional table(s)> WHERE <filter clause(s)> AND CASE_MASTER.EFFECTIVE_START_DATE <= X.EFFECTIVE_START_DATE AND CASE_MASTER.EFFECTIVE_END_DATE > X.EFFECTIVE_START_DATE AND X.CASE_ID = CASE_MASTER.CASE_ID AND X.DATE_LOCKED <= To_Date ('<DATE_FOR_LOCKED_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, (SELECT CASE_ID, VALIDSTART AS EFFECTIVE_START_DATE, DATE_LOCKED FROM CASE_ALL_LOCKED_REV WHERE USER_LOCKED = 1) X WHERE ((UPPER(case_master.case_num) =UPPER('CASE100'))) AND CASE_MASTER.EFFECTIVE_START_DATE <= X.EFFECTIVE_START_DATE AND CASE_MASTER.EFFECTIVE_END_DATE > X.EFFECTIVE_START_DATE AND X.CASE_ID = CASE_MASTER.CASE_ID AND X.DATE_LOCKED <= 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 CASE_ID, VALIDSTART AS EFFECTIVE_START_DATE, DATE_LOCKED FROM CASE_ALL_LOCKED_REV WHERE USER_LOCKED = 1) X, (SELECT CASE_PARENT_INFO.* FROM CASE_PARENT_INFO, (SELECT CASE_ID, VALIDSTART AS EFFECTIVE_START_DATE, DATE_LOCKED FROM CASE_ALL_LOCKED_REV WHERE USER_LOCKED = 1) X WHERE CASE_PARENT_INFO.EFFECTIVE_START_DATE <= X.EFFECTIVE_START_DATE AND CASE_PARENT_INFO.EFFECTIVE_END_DATE > X.EFFECTIVE_START_DATE AND X.CASE_ID = CASE_PARENT_INFO.CASE_ID AND X.DATE_LOCKED <= 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 <= X.EFFECTIVE_START_DATE AND CASE_MASTER.EFFECTIVE_END_DATE > X.EFFECTIVE_START_DATE AND X.CASE_ID = CASE_MASTER.CASE_ID AND X.DATE_LOCKED <= 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 tables other than CASE_MASTER should be in format (SELECT <TABLE_NAME>.* FROM <TABLE_NAME>, (SELECT CASE_ID, VALIDSTART AS EFFECTIVE_START_DATE, DATE_LOCKED FROM CASE_ALL_LOCKED_REV WHERE USER_LOCKED = 1 ) X WHERE <TABLE_NAME>.EFFECTIVE_START_DATE <= X.EFFECTIVE_START_DATE AND <TABLE_NAME>.EFFECTIVE_END_DATE > X.EFFECTIVE_START_DATE AND X.CASE_ID = <TABLE_NAME>.CASE_ID AND X.DATE_LOCKED <= To_Date ('<DATE_FOR_LOCKED_DATE>','DD-MON-YYYY HH24:MI:SS') ) <TABLE_NAME> to execute query as At Lock.
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 At Lock type.
Join with (SELECT CASE_ID, VALIDSTART AS EFFECTIVE_START_DATE, DATE_LOCKED FROM CASE_ALL_LOCKED_REV WHERE USER_LOCKED = 1 )X is required to get valid revision for table <TABLE_NAME> which is user locked.
CASE_ALL_LOCKED_REV table contains all locked revisions (user locked as well as post locked).
CASE_ALL_LOCKED_REV.USER_LOCKED = 1 will give only user locked revisions.
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.
The following are the steps to create custom SQL for Current Data point-in-time query:
Login to Argus Insight.
Navigate to Queries > Advance Condition > New (Argus Mart).
From Query Type drop-down list, select Last Locked Revision as of a Point in Time.
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(VALIDSTART) AS EFFECTIVE_START_DATE FROM CASE_ALL_LOCKED_REV WHERE DATE_LOCKED <= To_Date ('<DATE_FOR_LAST_LOCKEDREVISION>','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(VALIDSTART) AS EFFECTIVE_START_DATE FROM CASE_ALL_LOCKED_REV WHERE DATE_LOCKED <= To_Date ('22-DEC-2015 14:12:07','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(VALIDSTART) AS EFFECTIVE_START_DATE FROM CASE_ALL_LOCKED_REV WHERE DATE_LOCKED <= To_Date ('22-DEC-2015 14:12:07','DD-MON-YYYY HH24:MI:SS') GROUP BY CASE_ID) X, (SELECT CASE_PARENT_INFO.* FROM CASE_PARENT_INFO, (SELECT CASE_ID, MAX(VALIDSTART) AS EFFECTIVE_START_DATE FROM CASE_ALL_LOCKED_REV WHERE DATE_LOCKED <= To_Date ('22-DEC-2015 14:12:07','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(VALIDSTART) AS EFFECTIVE_START_DATE FROM CASE_ALL_LOCKED_REV WHERE DATE_LOCKED <= To_Date ('<DATE_FOR_LAST_LOCKED_REVISION>','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 as of a Point in Time.
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 as of a Point in Time type.
Join with (SELECT CASE_ID, MAX(VALIDSTART) AS EFFECTIVE_START_DATE FROM CASE_ALL_LOCKED_REV WHERE DATE_LOCKED <= To_Date ('<DATE_FOR_LAST_LOCKED_REVISION>','DD-MON-YYYY HH24:MI:SS') GROUP BY CASE_ID ) X is required to get all user locked revisions of cases.
CASE_ALL_LOCKED_REV table contains all locked revisions (user locked as well as post locked).
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.
The following are the steps to create custom SQL for Current Data point-in-time query:
Login to 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 Receipt 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 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 Last Locked Revision for a Version in a Period (Case Receipt 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 Receipt Date) 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.
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.
The following are the steps to create custom SQL for Current Data point-in-time query:
Login to 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 Locked 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(VALIDSTART) AS EFFECTIVE_START_DATE FROM CASE_ALL_LOCKED_REV WHERE DATE_LOCKED >= To_Date ('<FROM_DATE>','DD-MON-YYYY HH24:MI:SS') AND DATE_LOCKED < 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(VALIDSTART) AS EFFECTIVE_START_DATE FROM CASE_ALL_LOCKED_REV WHERE DATE_LOCKED >= To_Date ('01-JAN-2014 00:00:00','DD-MON-YYYY HH24:MI:SS') AND DATE_LOCKED < 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(VALIDSTART) AS EFFECTIVE_START_DATE FROM CASE_ALL_LOCKED_REV WHERE DATE_LOCKED >= To_Date ('01-JAN-2014 00:00:00','DD-MON-YYYY HH24:MI:SS') AND DATE_LOCKED < To_Date ('22-DEC-2015 23:59:59','DD-MON-YYYY HH24:MI:SS') GROUP BY CASE_ID) X, (SELECT CASE_ID, MAX(VALIDSTART) AS EFFECTIVE_START_DATE FROM CASE_ALL_LOCKED_REV WHERE DATE_LOCKED >= To_Date ('01-JAN-2014 00:00:00','DD-MON-YYYY HH24:MI:SS') AND DATE_LOCKED < 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(VALIDSTART) AS EFFECTIVE_START_DATE FROM CASE_ALL_LOCKED_REV WHERE DATE_LOCKED >= To_Date ('<FROM_DATE>','DD-MON-YYYY HH24:MI:SS') AND DATE_LOCKED < 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 Locked 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 Locked Date) type.
Join with (SELECT CASE_ID, MAX(VALIDSTART) AS EFFECTIVE_START_DATE FROM CASE_ALL_LOCKED_REV WHERE DATE_LOCKED >= To_Date ('<FROM_DATE>','DD-MON-YYYY HH24:MI:SS') AND DATE_LOCKED < To_Date ('<TO_DATE>','DD-MON-YYYY HH24:MI:SS') GROUP BY CASE_ID ) X is required to get all user locked revisions of cases.
CASE_ALL_LOCKED_REV table contains all locked revisions (user locked as well as post locked.
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.
The following are the steps to create custom SQL for Current Data point-in-time query:
Login to 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.
The following are the steps to create custom SQL for Current Data point-in-time query:
Login to Argus Insight.
Navigate to Queries > Advance Condition > New (Argus Mart).
From Query Type drop-down list, select Aggregate Queries.
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:
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.