Oracle SQL：重复使用CASE WHEN的子查询,而不必重复子查询

SELECT
    nested_query.ID,nested_query.FULL_NAME,nested_query.EarliestDate,CASE
        WHEN nested_query.EarliestDate BETWEEN TO_DATE('2000-01-01','YYYY-MM-DD') THEN 'First Period'
        WHEN nested_query.EarliestDate BETWEEN TO_DATE('2005-01-01','YYYY-MM-DD') THEN 'Second Period'
        WHEN nested_query.EarliestDate >= TO_DATE('2010-01-01','YYYY-MM-DD') THEN 'Third Period'
    END   AS CaseStatementResult
FROM
(
    SELECT
        APPLICANT.ID,(
        SELECT
            MIN(PERSON_EVENTS_Sub.REQUESTED_DTE) /* Earliest date of the secondary event */
        FROM
            EVENTS PERSON_EVENTS_Sub
        WHERE
            PERSON_EVENTS_Sub.PER_ID = APPLICANT.ID /* Link the person ID */
            AND PERSON_EVENTS_Sub.DEL_IND IS NULL /* Not a deleted event */
            AND PERSON_EVENTS_Sub.EVTYPE_SDV_VALUE IN (/* List of secondary events */)
            AND PERSON_EVENTS_Sub.COU_SDV_VALUE = PERSON_EVENTS.COU_SDV_VALUE /* Another link from the subQ to the main query */
            AND PERSON_EVENTS_Sub.REQUESTED_DTE <= PERSON_EVENTS.REQUESTED_DTE /* subQ event occurred before main query event */
            AND ROWNUM = 1 /* To ensure only one record returned,in case multiple rows match the MIN date */
        )
            AS EarliestDate
    FROM
        EVENTS PERSON_EVENTS,PEOPLE APPLICANT
    WHERE
        PERSON_EVENTS.PER_ID=APPLICANT.ID
        AND PERSON_EVENTS.EVTYPE_SDV_VALUE IN (/* List of values - removed ID information */)
        AND PERSON_EVENTS.REQUESTED_DTE BETWEEN '01-Jan-2014' AND '31-Jan-2014'
)   nested_query

（编辑：清远站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!