Query output as per request

chandra_1986 · Feb 9, 2021 10:18AM

HI Experts,

Below is the query used:-, oracle version: 12.1.0.2.0

SELECT TO_CHAR(LOGINLOGOUTTIME,'mm/dd/yyyy:hh24') AS LOGIN_LOGOUT_TIME, ROLE, COUNT( DISTINCT uid ) as UID_COUNT

FROM AT_AGENT WHERE LOGINLOGOUTTIME >= TRUNC(SYSDATE)

AND ROLE IN ('IEFS_SE_TECH','IEFS_SW_TECH','IEFS_MW_TECH','IEFS_W_TECH','TFS_SW_TECH''TFS_SE_TECH',

'TFS_W_TECH','TFS_MW_TECH','DTV_AFC','DTV_HSP','DTV_TECH','BS_MW_TECH','BS_SE_TECH','BS_SW_TECH','BS_W_TECH')

GROUP BY ROLE, TO_CHAR(LOGINLOGOUTTIME,'mm/dd/yyyy:hh24')

ORDER by LOGIN_LOGOUT_TIME desc

output we are getting like:

02/09/2021:04 DTV_AFC 2

02/09/2021:04 DTV_HSP 8

02/09/2021:04 DTV_TECH 3

02/09/2021:04 IEFS_MW_TECH 3

02/09/2021:04 IEFS_SE_TECH 14

02/09/2021:04 IEFS_SW_TECH 7

we need the output like:

Login_logout_time ROLE UID_COUNT

02/09/2021:04 DTV_AFC,DTV_HSP,DTV_TECH,IEFS_MW_TECH,IEFS_SE_TECH,IEFS_SW_TECH 37

RogerT · Feb 10, 2021 12:37PM

It would really help if you provided data + structure but:

with at_agent(loginlogouttime, role, "UID") as (select trunc(sysdate) + 1/24, 'IEFS_SE_TECH' , 47 from dual union all
                        select trunc(sysdate) + 1/24, 'IEFS_SW_TECH' , 47 from dual union all
                        select trunc(sysdate) + 1/24, 'IEFS_SW_TECH' , 48 from dual union all
                        select trunc(sysdate) + 2/24, 'TFS_SW_TECH'  , 48 from dual)
select LoginLogoutTime
   ,LISTAGG(ROLE,',') within group (order by role) as RoleList
   ,MAX(UID_COUNT)
 from (select DISTINCT trunc(loginlogouttime,'HH24') as LoginLogoutTime
       ,count(distinct "UID") over (PARTITION BY trunc(loginlogouttime,'HH24')) as UID_COUNT
       ,role
     from at_agent
     where LOGINLOGOUTTIME >= TRUNC(SYSDATE)
      and ROLE in ('IEFS_SE_TECH'
            , 'IEFS_SW_TECH'
            , 'IEFS_MW_TECH'
            , 'IEFS_W_TECH'
            , 'TFS_SW_TECH'
            , 'TFS_SE_TECH'
            , 'TFS_W_TECH'
            , 'TFS_MW_TECH'
            , 'DTV_AFC'
            , 'DTV_HSP'
            , 'DTV_TECH'
            , 'BS_MW_TECH'
            , 'BS_SE_TECH'
            , 'BS_SW_TECH'
            , 'BS_W_TECH'))
 group by LoginLogoutTime
 order by LoginLogoutTime desc;

should do it. Just remove the with clause, as you have an at_agent table.

Difference to your original post ... if a user (47) is logged in within one our with two different roles, the user get only counted once.

hth

Frank Kulash · Feb 9, 2021 11:05AM

Hi,

Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data.

If you can show what the problem is using commonly available tables (like the tables in the Oracle-supplied SCOTT schema) then you don't need to post any sample data; just the results and explanation.

Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

See: How to Ask Questions in Developer Community Spaces (oracle.com)

It looks like the LISTAGG function might do what you want. See LISTAGG (oracle.com)

BEDE · Feb 9, 2021 12:03PM

What about the below:

SELECT TO_CHAR(LOGINLOGOUTTIME,'mm/dd/yyyy:hh24') AS LOGIN_LOGOUT_TIME

,listagg(ROLE,',') within group(order by role) roles

, COUNT( DISTINCT uid ) as UID_COUNT

FROM AT_AGENT WHERE LOGINLOGOUTTIME >= TRUNC(SYSDATE)

AND ROLE IN ('IEFS_SE_TECH','IEFS_SW_TECH','IEFS_MW_TECH','IEFS_W_TECH','TFS_SW_TECH''TFS_SE_TECH',

'TFS_W_TECH','TFS_MW_TECH','DTV_AFC','DTV_HSP','DTV_TECH','BS_MW_TECH','BS_SE_TECH','BS_SW_TECH','BS_W_TECH')

GROUP BY TO_CHAR(LOGINLOGOUTTIME,'mm/dd/yyyy:hh24')

ORDER by LOGIN_LOGOUT_TIME desc

;

chandra_1986 · Feb 9, 2021 12:07PM

Thanks experts, we are getting the output from above query like:

02/09/2021:04 DTV_AFC 2

02/09/2021:04 DTV_HSP 8

02/09/2021:04 DTV_TECH 3

02/09/2021:04 IEFS_MW_TECH 3

02/09/2021:04 IEFS_SE_TECH 14

02/09/2021:04 IEFS_SW_TECH 7

chandra_1986 · Feb 9, 2021 12:46PM

Thanks experts,

Gets below error:

SELECT TO_CHAR(LOGINLOGOUTTIME,'mm/dd/yyyy:hh24') AS LOGIN_LOGOUT_TIME

,listagg(ROLE,',') within group(order by ROLE) ROLE

, COUNT( DISTINCT UID ) as UID_COUNT

FROM AT_AGENT WHERE LOGINLOGOUTTIME >= TRUNC(SYSDATE)

AND ROLE IN ('IEFS_SE_TECH','IEFS_SW_TECH','IEFS_MW_TECH','IEFS_W_TECH','TFS_SW_TECH''TFS_SE_TECH',

'TFS_W_TECH','TFS_MW_TECH','DTV_AFC','DTV_HSP','DTV_TECH','BS_MW_TECH','BS_SE_TECH','BS_SW_TECH','BS_W_TECH')

GROUP BY TO_CHAR(LOGINLOGOUTTIME,'mm/dd/yyyy:hh24')

ORDER by LOGIN_LOGOUT_TIME desc

ORA-12801: error signaled in parallel query server P000

ORA-01489: result of string concatenation is too long

12801. 00000 - "error signaled in parallel query server %s"

*Cause: A parallel query server reached an exception condition.

*Action: Check the following error message for the cause, and consult

your error manual for the appropriate action.

*Comment: This error can be turned off with event 10397, in which

case the server's actual error is signaled instead.

RogerT · Feb 9, 2021 1:17PM

You get a function result from listagg which exceeds the limit of the datatype returned by this function (varchar2).

So ... what is the purpose of a comma-separated list, that has a size 4000 Byte?

If you really need this, then search the forum for clob-version of listagg (or move to a newer oracle version).

hth

BEDE · Feb 9, 2021 1:20PM

Yea. That means that the resulting string exceeds what can be held in a varchar2 column. So, that kind of output you propose will not help...

To see more exactly what caused that error, run:

SELECT TO_CHAR(LOGINLOGOUTTIME,'mm/dd/yyyy:hh24') AS LOGIN_LOGOUT_TIME

,cout(distinct role) cnt_role

, COUNT( DISTINCT uid ) as UID_COUNT

FROM AT_AGENT WHERE LOGINLOGOUTTIME >= TRUNC(SYSDATE)

AND ROLE IN ('IEFS_SE_TECH','IEFS_SW_TECH','IEFS_MW_TECH','IEFS_W_TECH','TFS_SW_TECH''TFS_SE_TECH',

'TFS_W_TECH','TFS_MW_TECH','DTV_AFC','DTV_HSP','DTV_TECH','BS_MW_TECH','BS_SE_TECH','BS_SW_TECH','BS_W_TECH')

GROUP BY TO_CHAR(LOGINLOGOUTTIME,'mm/dd/yyyy:hh24')

having cout(distinct role)>500

ORDER by LOGIN_LOGOUT_TIME desc

;

This should show for what time you have more than 500 roles, which case will most likely cause that error.

mathguy · Feb 9, 2021 2:46PM

https://community.oracle.com/tech/developers/discussion/comment/16784379#Comment_16784379

The resulting string is too long, because it is not what the OP requires. The list in his example is a list of DISTINCT roles. In your query, they will appear multiple times.

mathguy · Feb 9, 2021 2:50PM

Questions:

How were you able to use UID as a column name, not in double-quotes? At least in Oracle 12.2, that is a reserved keyword - it can't be a non-quoted column name.
Your output must be generating counts much higher than 37 for some hours, to get a LISTAGG longer than 4000 bytes with those "roles". Right?

Anyway: to solve your problem, take your existing query. It is the correct first step.

Then in an OUTER QUERY group by "hour" alone, and take the SUM of counts and the LISTAGG of roles. At this point the roles are already distinct (because you already grouped by them in the INNER query), so you will get what you need.

chandra_1986 · Feb 9, 2021 2:58PM

I ran above query: -

got below output:-

all rows fetched : 0 in 0074 sec.

chandra_1986 · Feb 10, 2021 9:38AM

experts, can you please suggest.

Forum Stats

Quick Links

Categories

Query output as per request

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Query output as per request

Best Answer

Answers