Home » SQL & PL/SQL » SQL & PL/SQL » Getting Error inListagg (Oracle10g)
Getting Error inListagg [message #645511] Mon, 07 December 2015 22:42 Go to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
 
  select 'market' as flag,
                listagg('<option value=''' || id || '''>' || group_name ||
                         '</option>', '') within group(order by id) as val
         from   (select to_char(id) id,
                         trim(group_name) group_name
                  from   gad_group
                  where  active = 1)


when trying this query I am getting Result string too long" . IS thr any other alternate solution.
please help

Report message to a moderator

Re: Getting Error inListagg [message #645512 is a reply to message #645511] Mon, 07 December 2015 22:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ssyr22 wrote on Mon, 07 December 2015 20:42
  select 'market' as flag,
                listagg('<option value=''' || id || '''>' || group_name ||
                         '</option>', '') within group(order by id) as val
         from   (select to_char(id) id,
                         trim(group_name) group_name
                  from   gad_group
                  where  active = 1)


when trying this query I am getting Result string too long" . IS thr any other alternate solution.
please help


We don't have your tables.
We don't have your data.
We don't have your requirements.

How are we to know what solution you desire/expect?

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: Getting Error inListagg [message #645513 is a reply to message #645512] Mon, 07 December 2015 22:59 Go to previous messageGo to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
my data is like this please refer this excel file.

205.00 gname14 50002262,50003288,50004737,50004758,50000583,50004734,50004735,50004747,50003763 2/17/2014 9:58:35 PM -99.00 7/28/2015 12:30:46 AM 1 0 1 2.00 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 1.00 192.168.19.24 192.168.19.4 puneserver 0 41166445.00
224.00 aaasignuser 50004786,50002262,2179922,50000304,50004779,50004432,50004788,50004791,50004792,50000184 3/24/2014 10:07:39 PM 991.00 10/21/2015 3:47:07 PM 1 1 1 2.00 1 1 1 1 1 1 1 1 1 1 TU:2,3,4,5,6|WE:2,3,4,5,6|TH:2,3,4,5,6 0 1 0 0 0 2.00 192.168.19.24 192.168.19.4 puneserver 1 41458536.00
225.00 aadayparting 50004767,50002262,1470117,2179922,50000304,50004779,50004757,50004432,50004788,50004792,
50004755,50000184 3/24/2014 10:09:58 PM 991.00 10/21/2015 3:47:07 PM 1 1 1 2.00 1 1 1 1 1 411014 1 1 1 1 1 TU:4,5,6,7,8,9,10,11|WE:4,5,6,7,8,9,10,11|TH:4,5,6,7,8,9,10,11 0 1 0 0 0 2.00 192.168.19.24 192.168.19.4 puneserver 1 41458536.00
226.00 zipverification 3/24/2014 11:52:29 PM -99.00 7/28/2015 12:30:46 AM 1 1 1 2.00 1 1 1 1 1 411014 1 1 1 1 1 0 1 0 0 0 1.00 192.168.19.24 192.168.19.4 puneserver 1 40717589.00
234.00 testgrp 9thapr2014 50003288,50004758,50000583 1025.00 4/9/2014 11:53:08 PM -99.00 7/28/2015 12:30:46 AM 1 1 1 1.00 1 AF,AX,AL,AO,AQ 1 AU-NS,BR-AL,BR-AP,CA-NF,CA-NT,CA-NS,CA-NU,CN-AH,CN-BJ,CN-GS,
GB-EN,GB-NI,JP-20 1 525,532,790,583,634 1 203,204,205,206 1 4110022 1 1,3,6,4,13 11,12,33,37,38,39,36 1 Cable,DSL,Dialup 1 airtel,alpha_internet,aol,arnet 1 1 WE:4|TH:8|FR:10 6524,2152,6525,2154 0 247,242,241,1,3,9,14,10,8,4 FR:N/A:BACALAN,GB:SC:BADCALL,US:NV:CALIENTE,US:NJ:CALIFON,US:KY:CALIFORNIA,US:MD:CALIFORNIA,
US:MO:CALIFORNIA,US:PA:CALIFORNIA 1 0 0 0 2.00 1 41166445.00
238.00 userId 50004779 1104.00 5/20/2014 7:52:24 PM -99.00 7/28/2015 12:30:46 AM 1 1 1 2.00 1 1 1 1 1 1 1 1 1 1 0 1 0 0 0 1.00 192.168.19.24 192.168.19.4 puneserver 1 40823200.00
239.00 sc_group_21May2014 50003288,1470117 1104.00 5/21/2014 10:21:26 PM -99.00 7/28/2015 12:30:46 AM 0 1 1 2.00 1 1 1 1 1 1 1 1 1 1 0 1 0 0 0 2.00 1 40826590.00
242.00 group name test 50003288,50004758,1470117 1352.00 6/20/2014 11:19:36 PM -99.00 7/28/2015 12:30:46 AM 1 1 1 2.00 1 1 1 1 1 1 1 1 1 1 0 1 0 0 0 2.00 1 41166445.00
243.00 test12asd 50003353,50004758,1470117,2280489,50003763,50003215 1025.00 6/23/2014 10:13:48 PM -99.00 7/28/2015 12:30:46 AM 1 1 1 2.00 1 1 1 1 1 1 1 1 1 1 0 1 0 0 0 2.00 1 41166445.00
245.00 New test for the assign user 50003288,50004758 1352.00 6/30/2014 4:38:08 PM -99.00 7/28/2015 12:30:46 AM 1 1 1 2.00 1 1 1 1 1 1 1 1 1 1 0 1 0 0 0 2.00 1 41166445.00
191.00 gname3 50000596,2598554,50002193,50004124 2/13/2014 7:15:26 PM -99.00 7/28/2015 12:30:46 AM 1 testkeyvaltestkeyvaltestkeyvaltestkeyvaltestkeyvaltestkeyvaltestkeyvaltestkeyvaltestkeyvaltest
keyvaltestkeyvaltestkeyvaltestkeyvaltes tkeyvaltestkeyvaltestkeyvaltestkeyvaltestkeyvaltestkeyvaltestkeyvaltestkeyvaltestkeyval 0 19575737,67411890,364826629 1 2.00 0 AX,AL,DZ,AS,AD,AO,AI,AQ,AG,AR,AM 0 AU-AC,AU-NS,AU-NT,AU-QL,AU-SA,AU-TA,AU-VI 0 532,790,644,583,634,743,524,520,635,800,512 0 203,204,205,206,207,208,209,210,212,213,214,215,216,217,218 0 411001 0 3,6,4,13,43,21,22,11,20,14,12 10,11,12,33,34,13,-29,37,38 0 DQ,SQ,Cable,DSL,Dialup,FiOS 1 airtel,alpha_internet,aol,arnet,bell canada,compuserve,covad,dti,dreamnet,earthlink,fibertel,hinet,reserved,ibm,iij4u,Double quotes,
single Codes,idecnet,infosphere,nifty,eurociber 1 1 MO:1,3,6,8,10|TU|WE|TH:1,3,6,8,10|FR|SA:1,3,6,8,10|SU:1,3,6,8,10 421,4468,8035,5129,7670,7671,7669,4606,4607,4608,4609,4611,4600,4601,4602,4603,4605,3159,2274,
3158,4576,2889 0 testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion testexclusion 267,241,266,1,3,223 GB:WA:ABERCANAID,GB:WA:ABERCARN,GB:WA:ABERCASTLE,AU:NSW:ACACIACREEK,AU:QLD:ACACIARIDGE,
CA:AB:ACADIAVALLEY,US:CA:ACAMPO,GB:EN:ACASTERM ALBIS,FR:N/A:ACELASCA,FR:N/A:ACELLASCA,CH:N/A:ACQUACALDA,CA:NS:ADVOCATEHARBOUR,FR:N/A:AHAXEALCIETTEBASCASSAN 0 0 0 0 2.00 192.168.19.67 192.168.19.4 puneserver http://www.google.com,http://www.google.com,http://www.google.com,http://www.google.com,http://www.google.com,
http://www.google.com,h ttp://www.google.com,http://www.google.com,http://www.google.com,
http://www.google.com,http://www.google.com,http://www.google.com,ht tp://www.google.com,http://www.google.com,http://www.google.com,http://www.google.com,http://www.google.com, 0 41166445.00

[Updated on: Tue, 08 December 2015 01:24] by Moderator

Report message to a moderator

Re: Getting Error inListagg [message #645514 is a reply to message #645513] Mon, 07 December 2015 23:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
OK so what should result from this data?

Report message to a moderator

Re: Getting Error inListagg [message #645517 is a reply to message #645511] Mon, 07 December 2015 23:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

http://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=6608

Report message to a moderator

Re: Getting Error inListagg [message #645519 is a reply to message #645517] Mon, 07 December 2015 23:50 Go to previous messageGo to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
I am getting error if i pass ID value to the above query its works fine.. but without specifying ID its gives error "
ORA-01489:result of string concatenation is too long"

Report message to a moderator

Re: Getting Error inListagg [message #645522 is a reply to message #645519] Tue, 08 December 2015 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

I gave you a solution.

Report message to a moderator

Re: Getting Error inListagg [message #645607 is a reply to message #645511] Wed, 09 December 2015 10:24 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
ssyr22 wrote on Tue, 08 December 2015 10:12

when trying this query I am getting Result string too long"


You might just be hitting the SQL limit for varchar2 data type of 4000 bytes. Are you OK with XML solution?

Report message to a moderator

Re: Getting Error inListagg [message #645609 is a reply to message #645607] Wed, 09 December 2015 11:01 Go to previous message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

This is exactly what we told him in the 2 links we provided him in his next topic where you came from.

Report message to a moderator

Previous Topic: Load data
Next Topic: Fiscal Year data counts
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jul 03 07:03:15 CDT 2026
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.