Oracle SQL report with different condition-CodePudding

i'm use this code. Me need to join to one report separated data. I'm use WITH.

First row for choose one day before. Next rows for choosing different errors by user.

    WITH 
        evt as (select * from evt_t where to_char(dzins, 'DD.MM') = to_char((sysdate - 1), 'DD.MM')),
        EvaNamP as (select count(*), substr(AnwNamMld, instr(AnwNamMld, '/')   1, LENGTH(AnwNamMld)) as "UserName", evanam from evt where evt.evanam = 'EvaNamP' group by AnwNamMld, evanam),
        EvaNamE as (select count(*), substr(AnwNamMld, instr(AnwNamMld, '/')   1, LENGTH(AnwNamMld)) as "UserName", evanam from evt where evt.evanam = 'EvaNamE' group by AnwNamMld, evanam),
        LvsSrvE as (SELECT count(*), substr(AnwNamMld, instr(AnwNamMld, '/')   1, LENGTH(AnwNamMld)) as "UserName", evanam from evt where evt.evanam = 'LvsSrvE' group by AnwNamMld, evanam)
    
        
select * from EvaNamP, EvaNamE, LvsSrvE

But in results group function not works. I'm seen more than 3000 results, but in table result 240 rows. why is that?

Or maybe i'm choosing wrong way to make report?

Me need seen for example:

UserName |  EvaNamP | EvaNamE | LvsSrvE
User           23        1        9

CodePudding user response：

I presume that's because you produced Cartesian join (cross join). There are 3 tables involved and no join conditions so ... all rows from all tables are joined to all rows in all other tables (that's why they call it a "cross" join).

I don't know what to join with what; maybe usernames, or evanam values, but - just for example:

with 
  evt as ...,
  evanamp ...,
  evaname ...,
  lvssrve ...
select * 
from evanamp p join evaname e on p.evanam = e.evanam and p."UserName" = e."UserName"
               join lvssrve l on l.evanam = e.evanam and l."UserName" = e."UserName"

CodePudding user response：

If I understand your requirement correctly, you don't need three separate subqueries, you can simply use conditional aggregation, e.g. something like:

WITH evt AS (SELECT substr(anwnammld, instr(anwnammld, '/')   1, length(anwnammld)) AS "UserName",
                    evanam
             FROM   evt_t
             WHERE  to_char(dzins, 'DD.MM') = to_char((SYSDATE - 1), 'DD.MM')
             AND    evanam IN ('EvaNamP', 'EvaNamE', 'LvsSrvE'))
SELECT "UserName",
       COUNT(CASE WHEN evt.evanam = 'EvaNamP' THEN 1 END) evanamp_count,
       COUNT(CASE WHEN evt.evanam = 'EvaNamE' THEN 1 END) evaname_count,
       COUNT(CASE WHEN evt.evanam = 'LvsSrvE' THEN 1 END) lvssrve_count
FROM   evt;