ORA-00904“无效标识符”，但查询中存在标识符

3 回答

• 0

  我会用 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
  

  回复于 2024-04-29T12:27:49+08:00
• 0

  嵌套子查询时，无法访问属于两个或更多级别的列;在您的语句中，最内层子查询中无法访问WL1 . （还有一个分组遗失，顺便说一句）

  这可能有效（不完全确定您期望的输出，但尝试它）：

  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 =
    ( SELECT MIN(WL3.WORKLOGID)
        FROM Maximo.WorkOrder WOL3
        LEFT JOIN Maximo.Worklog WL3
          ON WL3.RECORDKEY = WOL3.WONUM
        WHERE WOL3.ORIGRECORDID = WOL1.ORIGRECORDID
          AND WL3.MXRONSITE IS NOT NULL
    )
    OR WL1.WORKLOGID IS NULL AND NOT EXISTS 
       ( SELECT MIN(WL4.WORKLOGID)
           FROM Maximo.WorkOrder WOL4
           LEFT JOIN Maximo.Worklog WL4
             ON WL4.RECORDKEY = WOL4.WONUM
           WHERE WOL4.ORIGRECORDID = WOL1.ORIGRECORDID
             AND WL4.MXRONSITE IS NOT NULL )
  

  回复于 2024-04-29T12:27:49+08:00
• 0

  我可能没有关于你想要做什么的详细信息...如果你有一些样本输入和所需的输出，那将是一个很大的帮助 .

  也就是说，我认为分析函数可以提供很多帮助，不仅可以获得输出，还可以组织代码 . 以下是如何使用子查询中的 max 分析函数的示例 .

  再次，关于连接的细节可能是关闭的 - 如果你可以提供一些样本输入和输出，我敢打赌有人可以到达你想去的地方：

  with wo as (
    select
      wonum, origrecordclass, origrecordid, reportdate,
      max (reportdate) over (partition by origrecordid) as max_date
    from Maximo.workorder
    where origrecordclass = 'INCIDENT'
  ),
  logs as (
    select
      worklogid, mxronsite, recordkey,
      max (mxronsite) over (partition by recordkey) as max_mx
    from Maximo.worklog
  )
  select
    i.ticketid,
    l.mxronsite as "Date_First_Onsite",
    wo.reportdate as "Date_First_Onsite_Notified"
  from
    Maximo.incident i
    left join wo on
      wo.origrecordid = i.ticketid and
      wo.reportdate = wo.max_date
    left join logs l on
      wo.wonum = l.recordkey and
      l.mxronsite = l.max_mx
  

  • 编辑 -

  根据您的样品输入和所需的输出，这似乎给出了所需的结果 . 它确实在子查询中发生了一些爆炸，但希望分析函数的效率会抑制它 . 与使用group by相比，它们通常要快得多：

  with wo_logs as (
    select
      wo.wonum, wo.origrecordclass, wo.origrecordid, wo.reportdate,
      l.worklogid, l.mxronsite, l.recordkey,
      max (reportdate) over (partition by origrecordid) as max_date,
      min (mxronsite) over (partition by recordkey) as min_mx
    from
      Maximo.workorder wo
      left join Maximo.worklog l on wo.wonum = l.recordkey
    where wo.origrecordclass = 'INCIDENT'
  )
  select
    i.ticketid, wl.wonum, wl.worklogid,
    wl.mxronsite as "Date_First_Onsite",
    wl.reportdate as "Date_First_Onsite_Notified"
  from
    Maximo.incident i
    left join wo_logs wl on
      i.ticketid = wl.origrecordid and
      wl.mxronsite = wl.min_mx
  order by 1
  

  回复于 2024-04-29T12:27:49+08:00