Oracle Top 5 Problems of Top 5 Sites

Question

Hi all I'm new to this board, before I always viewed anonymously but now I have a specific Problem I cannot find the answer.

I have two statements which do it individually very good

Statement 1: Select Sites with most Problems

WITH ordered_query AS
(SELECT debitorid, objectid, count(incident) as CTINCI
   FROM   ssrs_tblx_sla STSLA
   WHERE Debitorid = :Debitor 
   group by debitorid, objectid
   ORDER BY count(Incident) DESC, debitorid)
SELECT debitorid, objectid, CTINCI
FROM   ordered_query
WHERE  rownum <= 5

Statement 2: Most Errorcodes for a Site

WITH ordered_query AS
  (SELECT debitorid, objectid, errorcode, count(incident) as CTINCI
   FROM   ssrs_tblx_sla STSLA
   WHERE Debitorid = :Debitor 
   and   objectid = :Objectid 
   group by debitorid, objectid, errorcode
   ORDER BY count(Incident) DESC, debitorid)
SELECT debitorid, objectid, errorcode, CTINCI
FROM   ordered_query
WHERE  rownum <= 5

In both Statements I use oracle parameters in the where statement.

Now I want to comibe them and tried this by a LEFT JOIN Statement, but it seems I cannot use values of Statement 1 in the where clause in Statement 2.

GOAL: I want to get the Top 5 Sites with the highest amount Incidents and per site of them the Top 5 of hightst amount Incidents per errorcode

Anyone an Idea what I do wrong ? I'm a little bit confused

select  T5S.Debitorid
,       T5S.Objectid
,       T5S.CTINCI as SiteTotal
,       T5P.ERRORCODE
,       T5P.CTINI as ERCTotal
FROM
        (WITH ordered_query AS
            (SELECT debitorid, objectid, count(incident) as CTINCI
            FROM   ssrs_tblx_sla STSLA
            WHERE Debitorid = :Debitor 
            group by debitorid, objectid
            ORDER BY count(Incident) DESC, debitorid)
        SELECT debitorid, objectid, CTINCI
        FROM   ordered_query
        WHERE  rownum <= 5
        ) T5S
LEFT JOIN
        (WITH ordered_query AS
            (SELECT debitorid, objectid, errorcode, count(incident) as CTINCI
            FROM   ssrs_tblx_sla STSLA
            WHERE Debitorid = T5S.Debitorid 
            and   objectid = T5S.Objectid 
            group by debitorid, objectid, errorcode
            ORDER BY count(Incident) DESC, debitorid)
        SELECT debitorid, objectid, errorcode, CTINCI
        FROM   ordered_query
        WHERE  rownum <= 5
        ) T5P
ON      T5S.Debitorid = T5P.DebitorId and T5S.objectid=T5P.objectid

The error code oracle give me is:

ORA-00904: "T5S"."OBJECTID": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Fehler in Zeile: 20 Spalte: 56

score 0 · Answer 1 · answered Feb 24 '21 at 19:34

To me, it looks as this (but I might be wrong; can't test it, I don't have your tables):

with 
ordered_query as
  (select debitorid, objectid, count(incident) as ctinci
     from ssrs_tblx_sla stsla
    where debitorid = :debitor 
    group by debitorid, objectid
    order by count(incident) desc, debitorid
  ),
t5s as
  (select debitorid, objectid, ctinci
     from ordered_query
    where rownum <= 5
  ),    
ordered_query_2 as
  (select debitorid, objectid, errorcode, count(incident) as ctinci
     from ssrs_tblx_sla stsla join t5s on t5s.debitorid = stsla.debitorid
                                      and t5s.objectid = stsla.objectid
    group by debitorid, objectid, errorcode
    order by count(incident) desc, debitorid)
  ),
t5p as
  (select debitorid, objectid, errorcode, ctinci
     from ordered_query_2
    where rownum <= 5
  )
--
select  t5s.debitorid,
        t5s.objectid,
        t5s.ctinci as sitetotal,
        t5p.errorcode,
        t5p.ctini as erctotal,
from t5s left join t5p t5s.debitorid = t5p.debitorid and t5s.objectid=t5p.objectid;

Oracle Top 5 Problems of Top 5 Sites

1 Answers1