2

I want to use STRAGG function in this env.: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production, PL/SQL Release 11.2.0.4.0 - Production, CORE 11.2.0.4.0 Production, TNS for Linux: Version 11.2.0.4.0 - Production, NLSRTL Version 11.2.0.4.0 - Production:

I have a view called V_USER_ROLE_DESC

LOGIN   DESC      

joanet  BS in ANDROID.C.3
joanet  DOB in ANDROID.C.3
joanet  DO in ANDROID.C.3
joanet  BS in ANDROID.C.4
joanet  UA in ANDROID.C.4
joanet  OV in ANDROID.C.4
joanet  OI in ANDROID.C.4
joanet  DO in ANDROID.C.4
joanet  DHoU in ANDROID.C.4
joanet  AOP in ANDROID.C.4

Executin this query 

select
  login ,
  sys.STRAGG( UNIT_ROLE_DESC || ' - ' ) as string
from
  V_USER_ROLE_DESC
where login = 'joanet'
group by
  login
;

This is the result:

joanet  BS in ANDROID.C.3 - DOB in ANDROID.C.3 - DO in ANDROID.C.3 - BS in ANDROID.C.4 - UA in ANDROID.C.4 - OV in ANDROID.C.4 - OI in ANDROID.C.4 - DO in ANDROID.C.4 - DHoU in ANDROID.C.4 - AOP in ANDROID.C.4 -

But without the where clause ...

   select
      login ,
      sys.STRAGG( UNIT_ROLE_DESC || ' - ' ) as string
    from
      V_USER_ROLE_DESC
    --where login = 'joanet'
    group by
      login
    ;

this is the result:

...
colau   DOB in ANDROID.D.2 - 
joanet  DOB in ANDROID.D.2 - 
sisa    DOB in ANDROID.D.2 - 
...

Using 

SELECT login ,listagg(unit_role_desc,' - ') WITHIN GROUP (ORDER BY unit_role_desc) AS STRING FROM v_user_role_desc WHERE 1=1 GROUP BY LOGIN;

I have this error

ORA-01489: result of string concatenation is too long

Nuñito Calzada
  • 4,394
  • 47
  • 174
  • 301

1 Answers1

4

I would suggest using LISTAGG instead of STRAGG. LISTAGG is superior because it allows to specify order of aggregated items. 

SELECT login
      ,listagg(unit_role_desc,' - ')  WITHIN GROUP (ORDER BY unit_role_desc) AS STRING
  FROM v_user_role_desc
 WHERE 1=1
-- AND login = 'joanet'
 GROUP BY LOGIN;

See also a working example:

WITH
 v_user_role_desc AS (
 SELECT 'sisa' login
        ,'BS in ANDROID.C.3' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
 SELECT 'sisa' login
        ,NULL UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
 SELECT 'joanet' login
        ,'BS in ANDROID.C.3' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'DOB in ANDROID.C.3' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'DO in ANDROID.C.3' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'BS in ANDROID.C.4' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'UA in ANDROID.C.4' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'OV in ANDROID.C.4' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'OI in ANDROID.C.4' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'DO in ANDROID.C.4' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'DHoU in ANDROID.C.4' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'AOP in ANDROID.C.4' UNIT_ROLE_DESC 
    FROM dual) 
SELECT login
      ,listagg(unit_role_desc,' - ')  WITHIN GROUP (ORDER BY unit_role_desc) AS STRING
  FROM v_user_role_desc
 WHERE 1=1
-- AND login = 'joanet'
 GROUP BY LOGIN;
Andris Krauze
  • 2,092
  • 8
  • 27
  • 39
  • The point is that I have more than 1000 users. What's is the point doing 1 by 1 ? I want to have all of them in 1 query – Nuñito Calzada May 20 '16 at 07:40
  • WITH clause was my way of replicating dataset as I don't have the v_user_role_desc view in my database. Use just the SELECT part of my example. – Andris Krauze May 20 '16 at 07:42
  • ah, OK, sorry. Like this ? WITH v_user_role_desc AS ( SELECT login, UNIT_ROLE_DESC from V_USER_ROLE_DESC ) SELECT login ,listagg(unit_role_desc,' - ') WITHIN GROUP (ORDER BY unit_role_desc) AS STRING FROM v_user_role_desc WHERE 1=1 GROUP BY LOGIN; – Nuñito Calzada May 20 '16 at 07:45
  • You don't need the WITH clause. it's enough to do it like this: SELECT login ,listagg(unit_role_desc,' - ') WITHIN GROUP (ORDER BY unit_role_desc) AS STRING FROM v_user_role_desc WHERE 1=1 GROUP BY LOGIN; See my updated answer. – Andris Krauze May 20 '16 at 07:47
  • Then I have this error: ORA-01489: result of string concatenation is too long – Nuñito Calzada May 20 '16 at 07:48
  • 1
    This is because concatenated output is longer than 4000 characters. You might save some chars by using single sybol "-" instead of three. If you really need more than 4000, then try some options mention in this post: http://stackoverflow.com/questions/13795220/listagg-function-result-of-string-concatenation-is-too-long – Andris Krauze May 20 '16 at 07:54