Forum Stats

  • 3,770,233 Users
  • 2,253,082 Discussions
  • 7,875,370 Comments

Discussions

Query output as per request

chandra_1986
chandra_1986 Member Posts: 285 Blue Ribbon

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

Best Answer

«13

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    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
    BEDE Oracle Developer Member Posts: 2,303 Gold Trophy

    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
    chandra_1986 Member Posts: 285 Blue Ribbon

    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
    chandra_1986 Member Posts: 285 Blue Ribbon

    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
    RogerT Member Posts: 1,855 Gold Trophy

    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
    BEDE Oracle Developer Member Posts: 2,303 Gold Trophy

    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
    mathguy Member Posts: 10,165 Blue Diamond


    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
    mathguy Member Posts: 10,165 Blue Diamond

    Questions:

    1. 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.
    2. 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
    chandra_1986 Member Posts: 285 Blue Ribbon

    I ran above query: -

    got below output:-

    all rows fetched : 0 in 0074 sec.

  • chandra_1986
    chandra_1986 Member Posts: 285 Blue Ribbon

    experts, can you please suggest.