Oracle SQL: Re-use subquery for CASE WHEN without having to repeat subquery

Question

I have an Oracle SQL query which includes calculations in its column output. In this simplified example, we're looking for records with dates in a certain range where some field matches a particular thing; and then for those records, take the ID (not unique) and search the table again for records with the same ID, but where some field matches something else and the date is before the date of the main record. Then return the earliest such date. The follow code works exactly as intended:

SELECT
    TblA.ID, /* Not a primary key: there may be more than one record with the same ID */
    (
    SELECT
        MIN(TblAAlias.SomeFieldDate)
    FROM
        TableA TblAAlias
    WHERE
        TblAAlias.ID = TblA.ID /* Here is the link reference to the main query */
        TblAAlias.SomeField = 'Another Thing'
        AND TblAAlias.SomeFieldDate <= TblA.SomeFieldDate /* Another link reference */
    ) AS EarliestDateOfAnotherThing
FROM
    TableA TblA
WHERE
    TblA.SomeField = 'Something'
    AND TblA.SomeFieldDate BETWEEN TO_DATE('2015-01-01','YYYY-MM-DD') AND TO_DATE('2015-12-31','YYYY-MM-DD')

Further to this, however, I want to include another calculated column which returns text output according to what EarliestDateOfAnotherThing actually is. I can do this with a CASE WHEN statement as follows:

CASE WHEN
    (
    SELECT
        MIN(TblAAlias.SomeFieldDate)
    FROM
        TableA TblAAlias
    WHERE
        TblAAlias.ID = TblA.ID /* Here is the link reference to the main query */
        TblAAlias.SomeField = 'Another Thing'
        AND TblAAlias.SomeFieldDate <= TblA.SomeFieldDate /* Another link reference */
    ) BETWEEN TO_DATE('2000-01-01','YYYY-MM-DD') AND TO_DATE('2004-12-31','YYYY-MM-DD')
    THEN 'First period'
    WHEN
    (
    SELECT
        MIN(TblAAlias.SomeFieldDate)
    FROM
        TableA TblAAlias
    WHERE
        TblAAlias.ID = TblA.ID /* Here is the link reference to the main query */
        TblAAlias.SomeField = 'Another Thing'
        AND TblAAlias.SomeFieldDate <= TblA.SomeFieldDate /* Another link reference */
    ) BETWEEN TO_DATE('2005-01-01','YYYY-MM-DD') AND TO_DATE('2009-12-31','YYYY-MM-DD')
    THEN 'Second period'
    ELSE 'Last period'
END

That is all very well. However the problem is that I'm re-running exactly the same subquery - which strikes me as very inefficient. What I'd like to do is run the subquery just once, then take the output and subject it to various cases. Just as if I could use the VBA statement "SELECT CASE" as follows:

''''' Note that this is pseudo-VBA not SQL:
Select case (Subquery which returns a date)
    Case Between A and B
        "Output 1"
    Case Between C and D
        "Output 2"
    Case Between E and F
        "Output 3"
End select
' ... etc

My investigations suggested that the SQL statement "DECODE" could do the job: however it turns out that DECODE only works with discrete values, and not date ranges. I also found some things about putting the subquery in the FROM section - and then re-using the output in multiple places in SELECT. However that failed because the subquery does not stand up in its own right, but relies upon comparing values to the main query... and those comparisons could not be made until the main query had been executed (therefore making a circular reference, as the FROM section is itself part of the main query).

I'd be grateful if anyone could tell me an easy way to achieve what I want - because so far the only thing that works is manually re-using the subquery code in every place I want it, but as a programmer it pains me to be so inefficient!

EDIT: Thanks for the answers so far. However I think I'm going to have to paste the real, unsimplified code here. I tried to simplify it to just get the concept clear, and to remove potentially identifying information - but the answers so far make it clear that it's more complicated than my basic SQL knowledge will allow. I'm trying to wrap my head around the suggestions people have given, but I can't match up the concepts to my actual code. For example my actual code includes more than one table from which I am selecting in the main query.

I think I'm going to have to bite the bullet and show my (still simplified, but more accurate) actual code in which I have been trying to get the "Subquery in FROM clause" thing to work. Perhaps some kind person will be able to use this to more accurately guide me in how to use the concepts introduced so far in my actual code? Thanks.

SELECT
    APPLICANT.ID,
    APPLICANT.FULL_NAME,
    EarliestDate,
    CASE
        WHEN EarliestDate BETWEEN TO_DATE('2000-01-01','YYYY-MM-DD') AND TO_DATE('2004-12-31','YYYY-MM-DD') THEN 'First Period'
        WHEN EarliestDate BETWEEN TO_DATE('2005-01-01','YYYY-MM-DD') AND TO_DATE('2009-12-31','YYYY-MM-DD') THEN 'Second Period'
        WHEN EarliestDate >= TO_DATE('2010-01-01','YYYY-MM-DD') THEN 'Third Period'
    END
FROM
    /* Subquery in FROM - trying to get this to work */
    (
    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 */
    ) /* And here - how would I alias the result of this subquery as "EarliestDate", for use above? */,
    /* Then there are other tables from which to select */
    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'

Answer 1

Looking only at restructuring the existing query (rather that logically or functionally different approaches).

The simplest approach, to me, for is simply to do this as a nested query...
- The inner query would be your basic query, without the CASE statement
- It would also include your correlated sub-query as an additional field
- The outer query can then embed that field in a CASE statement

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') AND TO_DATE('2004-12-31','YYYY-MM-DD') THEN 'First Period'
        WHEN nested_query.EarliestDate BETWEEN TO_DATE('2005-01-01','YYYY-MM-DD') AND TO_DATE('2009-12-31','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,
        APPLICANT.FULL_NAME,
        (
        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