Nuñito de la Calzada Nuñito de la Calzada - 6 months ago 65
SQL Question

STRAGG in Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

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

Answer

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;
Comments