You Asked
Hello,
I am fairly new to Oracle and quite frankly a noob at writing code. I am writing a query to pull in data. One of the fields needs to be concatenated. However there a multiple duplicate records in the field resulting in many rows of data. The original code that was given to me used a select distinct with a nested query to pull the data. I got around the select distinct by using a regexp_replace. In addition, I need to concatenate the rows into one field. If I run:
SELECT
regexp_replace(
listagg(
MASTARS_GROUP.GROUP_NAME, ',') within group (ORDER BY MASTARS_GROUP.GROUP_NAME)
,'([^,]+)(,\1)*(,|$)', '\1\3') as "Approval Group Needed"
FROM CONTACT, MASTARS_GROUP, GROUP_PERSON
WHERE 0=0
AND CONTACT.IS_APPROVER = 'Y'
AND CONTACT.IS_VISIBLE = 'Y'
AND CONTACT.STATUS = 'W'
AND CONTACT.REQUEST_ID = :RequestId
AND MASTARS_GROUP.GROUP_ID = CONTACT.GROUP_ID
AND CONTACT.PERSON_ID = GROUP_PERSON.PERSON_ID
AND CONTACT.GROUP_ID NOT IN (SELECT GROUP_ID FROM CONTACT WHERE STATUS != 'W' AND REQUEST_ID = :RequestId AND GROUP_ID IS NOT NULL);
the output is fine. However, this is just for one record. I need to run this in a larger query and when I incorporated it into my query, I am back to the dreaded ORA-01489: result of string concatenation is too long error.
Here is my complete query:
select L.site_code,R.request_id, e.event_id,
TO_CHAR(CAST((FROM_TZ(CAST( r.create_date AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE),'mm/dd/yyyy HH24:MI:SS') as "Create US/Eastern",
TO_CHAR(CAST((FROM_TZ(CAST(e.schedule_start AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE),'mm/dd/yyyy HH24:MI:SS') as "Schedule Start US/Eastern",
TO_CHAR(CAST((FROM_TZ(CAST(e.schedule_stop AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE),'mm/dd/yyyy HH24:MI:SS') as "Schedule Stop US/Eastern",
e.schedule_start as "Schedule Start GMT", e.schedule_stop as "Schedule Stop GMT", r.approval_status,r.status, c.name as CompleteStatus,
(CASE when e.schedule_type = 'N' then 'Nightly'
when e.schedule_type = 'D' then 'Demand'
ELSE 'Other' END ) as "Schedule Type",
ec.description, dm.impact_type,n.text as work_desc,
(regexp_replace(listagg( mg.GROUP_NAME, ',') within group (ORDER BY mg.GROUP_NAME) ,'([^,]+)(,\1)*(,|$)', '\1\3')) as AprovalNeeded
from request R , event e, network n, location L, event_log eL, Completion_code c, event_code ec, item i, digital_media_risk_default dm, request_log rl, note_req n,contact ctc,mastars_group mg,group_person gp
where
R.request_id = e.request_id
and e.event_id = L.event_id
and r.request_id = i.request_id
and rl.request_id = e.request_id
and rl.work_desc = n.note_id
and n.network_id = r.network_id
AND e.EVENT_ID = eL.EVENT_ID
and e.event_code= dm.event_code(+)
and e.event_code = ec.event_code
AND eL.COMP_STATUS = c.COMPLETION_CODE_ID
and ctc.IS_APPROVER = 'Y'
AND ctc.IS_VISIBLE = 'Y'
AND ctc.STATUS = 'W'
AND ctc.REQUEST_ID = r.request_id
AND mg.GROUP_ID = ctc.GROUP_ID
AND ctc.PERSON_ID = gp.PERSON_ID
and n.network = 'DIGITAL MEDIA'
and r.approval_status in ('A','W')
and r.status not in ('C')
and FROM_TZ(CAST(e.schedule_start AS TIMESTAMP),'UTC') AT TIME ZONE 'US/Eastern' >= to_date(:StartRange,'mm/dd/yyyy hh24:mi:ss')
and FROM_TZ(CAST(e.schedule_start AS TIMESTAMP),'UTC') AT TIME ZONE 'US/Eastern' <= to_date(:StopRange,'mm/dd/yyyy hh24:mi ss')
and n.network_id not in (3351,2950)
group by L.site_code , r.request_id, e.event_id,r.create_date, e.schedule_start, e.schedule_stop, r.approval_status, r.status,c.name ,e.schedule_type,ec.description, dm.impact_type, n.text
order by e.schedule_start ,r.status desc
I am back to square one with the dreaded error. I see you have talked about custom defined functions and I wonder if that my do the trick.
thanks
and Connor said...
Here's a link to create your own listagg that will return a clob.
http://sql-plsql-de.blogspot.com.au/2014/01/sql-listagg-mit-clob-ausgabe-kein.html It's in German but the code is in English :-)
In terms of keeping the syntax simple, I'd use a WITH, so your query becomes something like:
WITH
my_query as
( select ...
from ...
)
select col, listagg_clob()
from my_query
group by col
So the grouping/concatention is separate from the extraction of the data.
(This is purely from a readability perpsective)
Rating
(6 ratings)
Is this answer out of date? If it is, please let us know via a Comment