ORA-00904 "invalid identifier" but identifier exists in query

I'm working in a fault-reporting Oracle database, trying to get fault information out of it.

The main table I'm querying is Incident, which includes incident information. Each record in Incident may have any number of records in the WorkOrder table (or none) and each record in WorkOrder may have any number of records in the WorkLog table (or none).

What I am trying to do at this point is, for each record in Incident, find the WorkLog with the minimum value in the field MXRONSITE, and, for that worklog, return the MXRONSITE time and the REPORTDATE from the work order. I accomplished this using a MIN subquery, but it turned out that several worklogs could have the same MXRONSITE time, so I was pulling back more records than I wanted. I tried to create a subsubquery for it, but it now says I have an invalid identifier (ORA-00904) for WOL1.WONUM in the WHERE line, even though that identifier is in use elsewhere.

Any help is appreciated. Note that there is other stuff in the query, but the rest of the query works in isolation, and this but doesn't work in the full query or on its own.

SELECT    
      WL1.MXRONSITE as "Date_First_Onsite",
      WOL1.REPORTDATE as "Date_First_Onsite_Notified"
FROM Maximo.Incident
LEFT JOIN (Maximo.WorkOrder WOL1
           LEFT JOIN Maximo.Worklog WL1
                  ON WL1.RECORDKEY = WOL1.WONUM) 
       ON WOL1.ORIGRECORDID = Incident.TICKETID 
          AND WOL1.ORIGRECORDCLASS = 'INCIDENT'
WHERE (WL1.WORKLOGID IN 
       (SELECT MIN(WL3.WORKLOGID) 
        FROM (SELECT MIN(WL3.MXRONSITE), WL3.WORKLOGID  
              FROM Maximo.Worklog WL3 WHERE WOL1.WONUM = WL3.RECORDKEY)) 
       or WL1.WORKLOGID is null)

To clarify, what I want is:

For each fault in Incident,
the earliest MXRONSITE from the Worklog table (if such a value exists),
For that worklog, information from the associated record from the WorkOrder table.

This is complicated by Incident records having multiple work orders, and work orders having multiple work logs, which may have the same MXRONSITE time.

After some trials, I have found an (almost) working solution:

WITH WLONSITE as (
SELECT
MIN(WLW.MXRONSITE) as "ONSITE",
WLWOW.ORIGRECORDID as "TICKETID",
WLWOW.WONUM as "WONUM"
FROM
MAXIMO.WORKLOG WLW
INNER JOIN
MAXIMO.WORKORDER WLWOW
ON
WLW.RECORDKEY = WLWOW.WONUM
WHERE
WLWOW.ORIGRECORDCLASS = 'INCIDENT'
GROUP BY 
WLWOW.ORIGRECORDID, WLWOW.WONUM
)

select
incident.ticketid,
wlonsite.onsite,
wlonsite.wonum
from
maximo.incident

LEFT JOIN WLONSITE
ON WLONSITE.TICKETID = Incident.TICKETID

WHERE 
(WLONSITE.ONSITE is null or WLONSITE.ONSITE = (SELECT MIN(WLONSITE.ONSITE) FROM WLONSITE WHERE WLONSITE.TICKETID = Incident.TICKETID AND ROWNUM=1))
AND Incident.AFFECTEDDATE >= TO_DATE ('01/12/2015', 'DD/MM/YYYY')

This however is significantly slower, and also still not quite right, as it turns out a single Incident can have multiple Work Orders with the same ONSITE time (aaargh!).

As requested, here is a sample input, and what I want to get from it (apologies for the formatting). Note that while TICKETID and WONUM are primary keys, they are strings rather than integers. WORKLOGID is an integer.

Incident table:
TICKETID / Description / FieldX
1 / WORD1 / S
2 / WORD2 / P
3 / WORDX /
4 / / Q

Work order table:
WONUM / ORIGRECORDID / REPORTDATE
11 / 1 / 2015-01-01
12 / 2 / 2015-01-01
13 / 2 / 2015-02-04
14 / 3 / 2015-04-05

Worklog table:
WORKLOGID / RECORDKEY / MXRONSITE
101 / 11 / 2015-01-05
102 / 12 / 2015-01-04
103 / 12 /
104 / 12 / 2015-02-05
105 / 13 /

Output:
TICKETID / WONUM / WORKLOGID
1 / 11 / 101
2 / 12 / 102
3 / /
4 / /
(Worklog 101 linked to TICKETID 1, has non-null MXRONSITE, and is from work order 11) (Worklogs 102-105 linked to TICKETID 2, of which 102 has lowest MXRONSITE, and is work order 12) (No work logs associated with faults 103 or 104, so work order and worklog fields are null)

Post Christmas attack!

I have found a solution which works:

The method I found was to use multiple WITH queries, as follows:

WLMINL AS (
  SELECT 
  RECORDKEY, MXRONSITE, MIN(WORKLOGID) AS "WORKLOG"
  FROM MAXIMO.WORKLOG
  WHERE WORKLOG.CLASS = 'WORKORDER'
  GROUP BY RECORDKEY, MXRONSITE
),
WLMIND AS (
  SELECT
  RECORDKEY, MIN(MXRONSITE) AS "MXRONSITE"
  FROM MAXIMO.WORKLOG
  WHERE WORKLOG.CLASS = 'WORKORDER'
  GROUP BY RECORDKEY
),
WLMIN AS (
  SELECT
  WLMIND.RECORDKEY AS "WONUM", WLMIND.MXRONSITE AS "ONSITE",   WLMINL.WORKLOG AS "WORKLOGID"
  FROM
  WLMIND
  INNER JOIN
  WLMINL
  ON
  WLMIND.RECORDKEY = WLMINL.RECORDKEY AND WLMIND.MXRONSITE =   WLMINL.MXRONSITE
)

Thus for each work order finding the first date, then for each work order and date finding the lowest worklogid, then joining the two tables. This is then repeated at a higher level to find the data by incident.

However this method does not work in a reasonable time, so while it may be suitable for smaller databases it's no good for the behemoths I'm working with.

Solution

I would do this with row_number function:

^SQLFiddle

select ticketid, case when worklogid is not null then reportdate end d1, mxronsite d2
  from (
    select i.ticketid, wo.reportdate, wl.mxronsite, wo.wonum, wl.worklogid,
           row_number() over (partition by i.ticketid 
                                  order by wl.mxronsite, wo.reportdate) rn
      from incident i 
        left join workorder wo on wo.origrecordid = i.ticketid 
                              and wo.origrecordclass = 'INCIDENT'
        left join worklog wl   on wl.recordkey = wo.wonum )
  where rn = 1 order by ticketid