sql - ORA-00904 "invalid identifier"但查询中存在标识符

Question

我正在一个故障报告 Oracle 数据库中工作，试图从中获取故障信息。

我查询的主表是Incident，其中包含事件信息。事件中的每条记录在 WorkOrder 表中可以有任意数量的记录(或没有)，并且 WorkOrder 中的每条记录在 WorkLog 表中可以有任意数量的记录(或没有)。

此时我想要做的是，对于事件中的每条记录，找到 MXRONSITE 字段中具有最小值的工作日志，并且对于该工作日志，从工作订单中返回 MXRONSITE 时间和报告日期。我使用 MIN 子查询完成了此操作，但事实证明，多个工作日志可能具有相同的 MXRONSITE 时间，因此我拉回的记录比我想要的多。我尝试为其创建一个子子查询，但现在它说我在 WHERE 行中的 WOL1.WONUM 有一个无效标识符 (ORA-00904)，即使该标识符已在其他地方使用。

感谢任何帮助。请注意，查询中还有其他内容，但查询的其余部分是独立工作的，而这在完整查询中或单独工作时不起作用。

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)

澄清一下，我想要的是:

• 对于事件中的每个错误，
• 工作日志表中最早的 MXRONSITE(如果存在这样的值)，
• 对于该工作日志，来自 WorkOrder 表的关联记录的信息。

由于事件记录具有多个工作订单，并且工作订单具有多个工作日志(可能具有相同的 MXRONSITE 时间)，因此情况变得复杂。

<小时/>

经过一些尝试，我找到了一个(几乎)可行的解决方案:

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')

然而，这要慢得多，而且仍然不太正确，因为事实证明，一个事件可以有多个具有相同现场时间的工作订单(aaargh!)。

<小时/>

根据要求，这是一个示例输入，以及我想从中获得的内容(对格式表示歉意)。请注意，虽然 TICKETID 和 WONUM 是主键，但它们是字符串而不是整数。 WORKLOGID 是一个整数。

事件表:
TICKETID/描述/FieldX
1/WORD1/S
2/WORD2/P
3/WORDX/
4//问

工单表:
WONUM/ORIGRECORDID/报告日期
11/1/2015-01-01
12/2/2015-01-01
13/2/2015-02-04
14/3/2015-04-05

工作日志表:
工作日志/记录 key /MXRONSITE
101/11/2015-01-05
102/12/2015-01-04
103/12/
104/12/2015-02-05
105/13/

输出:
TICKETID/WONUM/WORKLOGID
1/11/101
2/12/102
3//
4//
(工作日志 101 链接到 TICKETID 1，具有非空 MXRONSITE，并且来自工作订单 11)(工作日志 102-105 链接到 TICKETID 2，其中 102 具有最低 MXRONSITE，并且是工作订单 12)(没有与故障103或104关联的工作日志，因此工单和工作日志字段为空)

<小时/>

圣诞节后袭击!

我找到了一个有效的解决方案:

我找到的方法是使用多个WITH查询，如下:

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
)

因此，对于每个工作订单查找第一个日期，然后对于每个工作订单和日期查找最低的工作日志 ID，然后连接两个表。然后在更高级别重复此操作，以按事件查找数据。

但是，这种方法无法在合理的时间内发挥作用，因此虽然它可能适合较小的数据库，但对于我正在使用的庞然大物来说却没有好处。

Answer 1

我会使用row_number函数来做到这一点:

^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