Oracle SQL - return multiple columns from subquery

Question

Let's take a simple query in Oracle:

SELECT
    CASE.ID,
    CASE.TYPE,
    CASE.DATE_RAISED
FROM
    CASE
WHERE
    CASE.DATE_RAISED > '2019-01-01'

Now let's say another table, EVENT, contains multiple events which may be associated with each case (linked via EVENT.CASE_ID). OR not exist at all. I want to report on the earliest-dated future event per case - or if nothing exists, return NULL. I can do this with a subquery in the SELECT clause, as follows:

SELECT
    CASE.ID,
    CASE.TYPE,
    CASE.DATE_RAISED,
    (
    SELECT
        MIN(EVENT.DATE)
    FROM
        EVENT
    WHERE
        EVENT.CASE_ID = CASE.ID
        AND EVENT.DATE >= CURRENT_DATE
    ) AS MIN_EVENT_DATE
FROM
    CASE
WHERE
    CASE.DATE_RAISED > '2019-01-01'

This will return a table like this:

Case ID Case Type   Date Raised Min Event Date
76      A           03/01/2019  10/05/2019
43      B           02/02/2019  [NULL]
89      A           29/01/2019  08/07/2019
90      A           04/03/2019  [NULL]
102     C           15/04/2019  20/05/2019

Note that if there do not exist any Events which match the criteria, the line is still returned but without a value. This is because the subquery is in the SELECT clause. This works just fine.

My problem, however, is if I want to return more than one column from the EVENT table - while still at the same time preserving the possibility that there are no matching rows from the EVENT table. The above code only returns EVENT.DATE as the single subquery result, to ONE column of the main query. But what if I also want to return EVENT.ID, or EVENT.TYPE? While still allowing for them to be NULL (if no matching records from CASE are found)?

I suppose I could use multiple subqueries in the SELECT clause: each returning just ONE column. But this seems horribly inefficient, given that each subquery would be based on the same criteria (the minimum-dated EVENT whose CASE ID matches that of the main query; or NULL if no such events found).

I suspect some nifty joins would be the answer - although I'm struggling to understand which ones exactly.

Please note that the above examples are vastly simplified versions of my actual code, which already contains multiple joins in the "old style" Oracle format, eg:

WHERE
    CASE.ID(+) = EVENT.CASE_ID

There are reasons why this is so - therefore a request to anyone answering this, please would you demonstrate any solutions in this style of coding, as my SQL isn't far enough advanced to be able to re-factor the "newer" style joins into existing code.

I would highly, highly recommend you stop writing queries in the "old style" join syntax. Refactoring existing queries into the new style is generally not difficult (unless there are some complex and weird joins in the existing sql. — Boneist
– Boneist, Commented May 8, 2019 at 13:30
To refactor, you need to identify the join conditions for the tables involved. If you edit your question to add an example of your query, we can refactor it for you. The "new" (it's been around for several years now) syntax is easier to understand, since your join conditions are no longer mixed in with the predicates. — Boneist
– Boneist, Commented May 8, 2019 at 13:45
@Boneist - I use the old style because this is where I first learned it, as it is generated in that form by the Business Objects application. I started tweaking this generated code and introducing my own complexities - all while retaining the same style I learned. Most of the code I'm working with joins multiple tables in multiple ways, and I can't get my head around how to do this in the "new" style. This minimal example in the question may be easy to convert - but I wouldn't know where to start, with 10+ tables linked in different ways. — Chris Melville
– Chris Melville, Commented May 8, 2019 at 14:41
I too first learnt the old way, and used it for ~10 years. The “new way” is simply better. You can do more with it than the old way, and it’s really not that hard. If you’re stuck with generated sql from some app, though, that’s tougher. — Boneist
– Boneist, Commented May 8, 2019 at 14:43
@Boneist - If I were to provide a sample of the actual code I'm using (changing some names due to protection issues), would you be kind enough to show me a re-factor? This would be beyond the scope of this question though, so not sure where to ask it (Code Review?) — Chris Melville
– Chris Melville, Commented May 8, 2019 at 14:44

Gordon Linoff · Accepted Answer · 2019-05-08 11:10:56Z

6

You can use a join and window functions. For instance:

select c.*, e.*
from c left join
     (select e.*,
             row_number() over (partition by e.case_id order by e.date desc) as seqnum
      from events e
     ) e
     on e.case_id = c.id and e.seqnum = 1;
where c.date_raised > date '2019-01-01';  -- assuming the value is a date

answered May 8, 2019 at 11:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chris Melville Over a year ago

Can you please re-factor this into the style I requested in the question? I have no clue how to incorporate this style into my current code.

Gordon Linoff Over a year ago

@ChrisMelville . . . I have no idea what your comment means. This code should run as is (although you should probably list the columns that you want explicitly rather than using *).

Chris Melville Over a year ago

Thanks Gordon. I see where you're going with this - and it basically worked, but for my lack of familiarity with how to employ this coding style. @kfinity used your logic and factored it into the form I understand. Still, have an up-vote :)

Boneist · Accepted Answer · 2019-05-08 13:29:16Z

3

Is this what you mean? I just rewrote Gordon's answer with old Oracle join syntax and your code style.

SELECT
    CASE.ID,
    CASE.TYPE,
    CASE.DATE_RAISED,
    MIN_E.DATE AS MIN_EVENT_DATE
FROM
    CASE,
    (SELECT EVENT.*,
        ROW_NUMBER() OVER (PARTITION BY EVENT.CASE_ID ORDER BY EVENT.DATE DESC) AS SEQNUM
        FROM
            EVENT
        WHERE 
            EVENT.DATE >= CURRENT_DATE
    ) MIN_E
WHERE
    CASE.DATE_RAISED > DATE '2019-01-01'
    AND MIN_E.CASE_ID (+) = CASE.ID
    AND MIN_E.SEQNUM (+) = 1;

edited May 8, 2019 at 13:29

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

answered May 8, 2019 at 12:37

kfinity

9,1411 gold badge15 silver badges22 bronze badges

1 Comment

Gordon Linoff Over a year ago

The use of the archaic outer join syntax that even Oracle doesn't want anyone to use any more causes a physical reaction in my stomach.

vasu2005s · Accepted Answer · 2021-05-12 12:45:51Z

0

Create object type with columns you want and return it from subquery. Your query will be like

SELECT
    CASE.ID,
    CASE.TYPE,
    CASE.DATE_RAISED,
    (
    SELECT
        t_your_new_type ( MIN(EVENT.DATE) , min ( EVENT.your_another_column ) )
    FROM
        EVENT
    WHERE
        EVENT.CASE_ID = CASE.ID
        AND EVENT.DATE >= CURRENT_DATE
    ) AS MIN_EVENT_DATE
FROM
    CASE
WHERE
    CASE.DATE_RAISED > '2019-01-01'

answered May 12, 2021 at 12:45

vasu2005s

211 bronze badge

Collectives™ on Stack Overflow

Oracle SQL - return multiple columns from subquery

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related