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'