Forum Stats

  • 3,733,981 Users
  • 2,246,853 Discussions
  • 7,856,964 Comments

Discussions

Query output as per request

chandra_1986
chandra_1986 Member Posts: 262 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

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,496 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,234 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: 262 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: 262 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,852 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,234 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: 9,773 Gold Crown


    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: 9,773 Gold Crown

    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: 262 Blue Ribbon

    I ran above query: -

    got below output:-

    all rows fetched : 0 in 0074 sec.

  • chandra_1986
    chandra_1986 Member Posts: 262 Blue Ribbon

    experts, can you please suggest.

  • chandra_1986
    chandra_1986 Member Posts: 262 Blue Ribbon

    Thanks experts, we need to include:


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

  • RogerT
    RogerT Member Posts: 1,852 Gold Trophy

    I am pretty sure you can manage that by your own.

    Alex Nuijten
  • chandra_1986
    chandra_1986 Member Posts: 262 Blue Ribbon

    Yes i tried experts,


    with :-

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

      ,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 AGENTROLE 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 LoginLogoutTime desc;

     

     ORA-00979: not a GROUP BY expression

    00979. 00000 - "not a GROUP BY expression"

    *Cause:   

    *Action:

    Error at Line: 262 Column: 11

     


     

     ORA-00979: not a GROUP BY expression

    00979. 00000 - "not a GROUP BY expression"

    *Cause:   

    *Action:

    Error at Line: 262 Column: 11

  • RogerT
    RogerT Member Posts: 1,852 Gold Trophy

    I am pretty sure line 262 points to your order by clause ...

    hth

  • mathguy
    mathguy Member Posts: 9,773 Gold Crown
    edited February 10

    I believe you are still modifying your code before you post it.

    In your latest reply, in the subquery, you are selecting ROLE. But in the WHERE clause you reference a different name, AGENTROLE. Based on your original question, I assume these are the same thing, not two different columns in the same table. If so, the subquery would throw an error. Is ROLE an alias for AGENTROLE? If it is, then you modified your query before posting it here. I won't be able to help you if you keep doing that.


    The error you reported references the ORDER BY condition at the very end. You could order by LOGIN_LOGOUT_TIME which is defined in the SELECT clause. The reason you can't order by LOGINLOGOUTTIME (which is the column from the subquery) is that in the outer query you didn't group by LOGINLOGOUTTIME, you grouped by a function of it. The best solution would be, in the outer query, to group by LOGINLOGOUTTIME (the exact column coming from the subquery), and only use TO_CHAR in the SELECT clause, not in GROUP BY. Then you could leave ORDER BY as is, since LOGINLOGOUTTIME will be a "group by" column.


    This will fix the query. But I don't know what your latest query has to do with the original one. In the original, you grouped by ROLE and "hour" and counted rows. In your new version, you do something very different - the subquery doesn't group by role, it just does some strange "distinct" things. I have no clue what the purpose is. My suggestion was to "group by hour" and SUM the counts and LISTAGG the roles coming from your existing query, EXACTLY AS WRITTEN, used as subquery. Why did you need to change the query you had in your original post?

  • chandra_1986
    chandra_1986 Member Posts: 262 Blue Ribbon

    Thanks experts,


    output is like:


    02/10/2021:00 BS_MW_TECH,BS_SE_TECH,BS_SW_TECH,BS_W_TECH,DTV_HSP,DTV_TECH,IEFS_MW_TECH,IEFS_SE_TECH,IEFS_SW_TECH,IEFS_W_TECH,TFS_SE_TECH,TFS_SW_TECH,TFS_W_TECH 151

    02/10/2021:01 BS_MW_TECH,BS_W_TECH,DTV_HSP,DTV_TECH,IEFS_MW_TECH,IEFS_SE_TECH,IEFS_SW_TECH,IEFS_W_TECH,TFS_SW_TECH,TFS_W_TECH 89



    we have requirement like output from second row like values from 00 hours + 01 hour value:


    so requested output like in second row(89+151)

    02/10/2021:00 BS_MW_TECH,BS_SE_TECH,BS_SW_TECH,BS_W_TECH,DTV_HSP,DTV_TECH,IEFS_MW_TECH,IEFS_SE_TECH,IEFS_SW_TECH,IEFS_W_TECH,TFS_SE_TECH,TFS_SW_TECH,TFS_W_TECH 151

    02/10/2021:01 BS_MW_TECH,BS_W_TECH,DTV_HSP,DTV_TECH,IEFS_MW_TECH,IEFS_SE_TECH,IEFS_SW_TECH,IEFS_W_TECH,TFS_SW_TECH,TFS_W_TECH 240

  • RogerT
    RogerT Member Posts: 1,852 Gold Trophy

    So the requirement changes with every post? Just in case you need it ... i left the uid_count and added a running summary.

     select TO_CHAR(LOGIN_LOGOUT_TIME,'mm/dd/yyyy:hh24') AS LOGINLOGOUTTIME
           ,LISTAGG(AGENTROLE,',') within group (order by AGENTROLE) as RoleList
           ,MAX(UID_COUNT) AS UID_COUNT
           ,SUM(MAX(UID_COUNT)) OVER (ORDER BY TO_CHAR(LOGIN_LOGOUT_TIME,'mm/dd/yyyy:hh24')) AS UID_COUNT_SUM
     from (select DISTINCT trunc(login_logout_time,'HH24') as login_logout_time
                 ,count(distinct "UID") over (PARTITION BY trunc(login_logout_time,'HH24')) as UID_COUNT
                 ,AGENTROLE
             from at_agent
            where LOGIN_LOGOUT_TIME >= TRUNC(SYSDATE)
              and AGENTROLE 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(LOGIN_LOGOUT_TIME,'mm/dd/yyyy:hh24')
     order by LOGINLOGOUTTIME;
    
  • chandra_1986
    chandra_1986 Member Posts: 262 Blue Ribbon

    getting below error while executing the query:-


    ORA-00904: "LOGIN_LOGOUT_TIME": invalid identifier

    00904. 00000 - "%s: invalid identifier"

    *Cause:   

    *Action:

    Error at Line: 332 Column: 15

  • RogerT
    RogerT Member Posts: 1,852 Gold Trophy

    I am confident you can solve that ... with just a little effort.

Sign In or Register to comment.