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;