Oracle query stumped - derived table

Question

It's been a long time since I've done more than the most basic sql queries. But I ran into this one today and have spent a few hours on it and am stuck with my derived table attempt (this is for an Oracle db). Looking for a few tips. Thx.

TABLE: dtree
DataID  Name
-------------
10001   A.doc
10002   B.doc
10003   C.doc
10004   D.doc

TABLE: collections
CollectionID   DataID
---------------------
201     10001
201     10002   
202     10003
203     10004

TABLE: rimsNodeClassification
DataID  RimsSubject  RimsRSI  Status
---------------------------------------
10001   blah         IS-03  Active
10002   blah         LE-01  Active
10003   blah         AD-02  Active
10004   blah         AD-03  Active

TABLE:  rsiEventSched 
RimsRSI  RetStage  DateToUse  RetYears
--------------------------------------
IS-03   SEM-PHYS   95       1
IS-03   ACT       NULL      2
LE-01   SEM-PHYS   94       1
LE-01   INA-PHYS   95       2
LE-01   ACT       NULL      NULL
LE-01   OFC       NULL      NULL
LE-02   SEM-PHYS   94       2

Trying to query on CollectionID=201

INTENDED RESULT:
DataID  Name    RimsRSI  Status  SEMPHYS_DateToUse  INAPHYS_DateToUse      SEMPHYS_RetYears  INAPHYS_RetYears
-------------------------------------------------------------------------------------------------------
10001   A.doc   IS-03    Active   95               null                     1               null
10002   B.doc   Le-01    Active   94               95                   1                 2

Could you be a bit more clear as to what exactly your question is? — Hannes Johansson, Jul 26 '15 at 17:49

score 1 · Accepted Answer · answered Jul 26 '15 at 17:55

You don't need a Derived Table, just join the tables (the last using a Left join) and then apply a MAX(CASE) aggregation:

select c.DataID, t.Name, rnc.RimsRSI, rnc.Status,
   max(case when res.RetStage = 'SEM-PHYS' then res.DateToUse end) SEMPHYS_DateToUse,
   max(case when res.RetStage = 'INA-PHYS' then res.DateToUse end) INAPHYS_DateToUse,
   max(case when res.RetStage = 'SEM-PHYS' then res.RetYears end) SEMPHYS_RetYears,
   max(case when res.RetStage = 'INA-PHYS' then res.RetYears end) INAPHYS_RetYears
from collections c
join dtree t
  on c.DataID = t.DataID
join rimsNodeClassification rnc
  on c.DataID = rnc.DataID
left join rsiEventSched res 
  on rnc.RimsRSI = res.RimsRSI
where c.CollectionID= 201
group by c.DataID, t.Name, rnc.RimsRSI, rnc.Status

Thanks dnoeth. Very helpful, I was getting caught up trying to select from the derived table where I was using the max case to merge the multiple rows into one row with multiple columns. :) — GeneralZod, Jul 27 '15 at 02:31

Oracle query stumped - derived table

1 Answers1