Oracle-valeurs Listagg uniques
Je suis nouveau à L'utilisation de Listagg. Le script suivant fonctionne par rapport à lui me donne une liste de valeurs mais, la liste duplique les valeurs.
est-il possible d'utiliser Listagg pour retourner seulement une liste unique de valeurs.
j'utilise oracle 10g.
select distinct ds.catnr,ds.planqty, ds.ordnr, ds.posnr, ds.segnr,
listagg(case when not li.paco is null then (select unique max(li1.paco) from leos_item li1 where li.av_part_no = li1.av_part_no) end, ', ') within group (order by pd.part_no) inq_no
from oes_delsegview ds, oes_address ad, oes_opos op, oes_nrbom nr, scm_prodtyp sp, leos_item li, part_description pd
where ds.delnr = ad.key
and ad.adr = ds.deladr
and ds.pos_o_status not in ('9', 'D')
and ds.pos_c_status not in ('9', 'D')
and ds.seg_o_status not in ('9', 'D')
and ds.seg_c_status not in ('9', 'D')
and ds.cunr in ('W31170','W31172')
and ds.pos_type != 'RC'
and ds.ordnr = op.ordnr
and ds.posnr = op.posnr
and ds.catnr = pd.catnr
and ds.prodtyp = pd.prodtyp
and ds.packtyp = pd.packtyp
and ds.catnr = nr.p_catnr (+)
and ds.prodtyp = nr.p_prodtyp (+)
and ds.packtyp = nr.p_packtyp (+)
and nr.c_prodtyp = sp.prodtyp (+)
and sp.prodgrp (+) = 'COMP'
and substr(nr.c_prodtyp,1,2) not in ('MT','LF')
and nr.c_catnr = li.catnr (+)
and nr.c_prodtyp = li.prodtyp (+)
and nr.c_packtyp = li.packtyp (+)
and pd.catnr = '9780007938797'
group by ds.catnr,ds.planqty, ds.ordnr, ds.posnr, ds.segnr
le résultat de ma Listagg est:
14/061127-12, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16
ce que je voudrais voir est:
14/061127-12, 14/061127-16
Toute aide serait très apprécié.
1
demandé sur
Haytem BrB
2016-02-09 20:51:14
4 réponses
j'ai supprimé le premier distinct
depuis votre déjà group by
tous les champs de votre Select
requête, et remplacé le case when
par un select
requête:
select ds.catnr,ds.planqty, ds.ordnr, ds.posnr, ds.segnr,
listagg((select distinct max(li1.paco) from leos_item li1 where li.av_part_no = li1.av_part_no and li.paco is not null), ', ') within group (order by pd.part_no) inq_no
from oes_delsegview ds, oes_address ad, oes_opos op, oes_nrbom nr, scm_prodtyp sp, leos_item li, part_description pd
where ds.delnr = ad.key
and ad.adr = ds.deladr
and ds.pos_o_status not in ('9', 'D')
and ds.pos_c_status not in ('9', 'D')
and ds.seg_o_status not in ('9', 'D')
and ds.seg_c_status not in ('9', 'D')
and ds.cunr in ('W31170','W31172')
and ds.pos_type != 'RC'
and ds.ordnr = op.ordnr
and ds.posnr = op.posnr
and ds.catnr = pd.catnr
and ds.prodtyp = pd.prodtyp
and ds.packtyp = pd.packtyp
and ds.catnr = nr.p_catnr (+)
and ds.prodtyp = nr.p_prodtyp (+)
and ds.packtyp = nr.p_packtyp (+)
and nr.c_prodtyp = sp.prodtyp (+)
and sp.prodgrp (+) = 'COMP'
and substr(nr.c_prodtyp,1,2) not in ('MT','LF')
and nr.c_catnr = li.catnr (+)
and nr.c_prodtyp = li.prodtyp (+)
and nr.c_packtyp = li.packtyp (+)
and pd.catnr = '9780007938797'
group by ds.catnr,ds.planqty, ds.ordnr, ds.posnr, ds.segnr
2
répondu
Haytem BrB
2016-02-09 18:11:45
j'ai utilisé une fonction regexp_replace pour supprimer les doublons dans ma listagg;
regexp_replace(
listagg((select distinct max(li1.paco) from leos_item li1 where li.av_part_no = li1.av_part_no and li.paco is not null), ', ') within group (order by pd.part_no)
,'([^,]+)(,)+', '') inq_no
semble fonctionner correctement.
0
répondu
SMORF
2017-05-23 12:06:52
vous pouvez le faire via RegEx replacement. Voici un exemple:
-- Citations Per Year - Cited Publications main query. Includes list of unique associated core project numbers, ordered by core project number.
SELECT ptc.pmid AS pmid, ptc.pmc_id, ptc.pub_title AS pubtitle, ptc.author_list AS authorlist,
ptc.pub_date AS pubdate,
REGEXP_REPLACE( LISTAGG ( ppcc.admin_phs_org_code ||
TO_CHAR(ppcc.serial_num,'FM000000'), ',') WITHIN GROUP (ORDER BY ppcc.admin_phs_org_code ||
TO_CHAR(ppcc.serial_num,'FM000000')),
'(^|,)(.+)(,)+', '')
AS projectNum
FROM publication_total_citations ptc
JOIN proj_paper_citation_counts ppcc
ON ptc.pmid = ppcc.pmid
AND ppcc.citation_year = 2013
JOIN user_appls ua
ON ppcc.admin_phs_org_code = ua.admin_phs_org_code
AND ppcc.serial_num = ua.serial_num
AND ua.login_id = 'EVANSF'
GROUP BY ptc.pmid, ptc.pmc_id, ptc.pub_title, ptc.author_list, ptc.pub_date
ORDER BY pmid;
0
répondu
DKroot
2016-04-21 00:28:36