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'