Proper syntax following the use of LISTAGG

Question

I am working on a query for Oracle that utilizes LISTAGG in it. I would like to include a sub-select statement following the use of LISTAGG but keep coming up short on syntax, and I think it has to do with the "GROUP BY" line. I am not sure of the proper syntax for the field embedded in the sub-select statement in the GROUP BY line of code.

The LISTAGG is needed as I need the field "PATCH_AREA" compressed into one line instead of several records for each PATCH_AREA.

The below query works fine as long as the commented out section is not included. I need to add the commented out line.

select
katpf7c as "Event ID",
artanrc as "Item Number",
artbezc as "Description",

prsvf18c as "Product Presentation",
prsvf19c as "Page Type",
doksnei as "Page Number",
LISTAGG(KAVBEZC,',') within GROUP (ORDER BY KAVBEZC) PATCH_AREA ,
provf10c as "Block Number",
prsvf17c as "Notes From Agency",
prsvf16c as "VBU Name"  --,

--(SELECT afl.aflfldc FROM fty inner join afl ON afl.aflftykeyi = fty.ftykeyi WHERE fty.ftybezc = 'MDVDesc' and AFL.AFLARTKEYI = ART.ARTKEYI and AFL.AFLKAVKEYI = ART.ARTKAVKEYI) as "MDVDesc",

from art
            inner join aez ON art.artkavkeyi = aez.aezkavkeyi and ART.ARTKEYI = AEZ.AEZARTKEYI
            inner join pro on pro.prokavkeyi = aez.aezkavkeyi and PRO.PROKEYI = AEZ.AEZPROKEYI
            full outer join DOK on PRO.PROKAVKEYI = DOK.DOKKAVKEYI and PRO.PROSNEI = DOK.DOKSNEI
            inner join prs on prsartkeyi = artkeyi and prskavkeyi = artkavkeyi
            join kav on kavkeyi = artkavkeyi
            join kat on kavkatkeyi = katkeyi
            full outer join psz on pszprokeyi = prokeyi and pszkavkeyi = prokavkeyi

where katkeyi in (5985)

group by katpf7c,artanrc,artbezc,prsvf18c,prsvf19c,doksnei,provf10c,prsvf17c,prsvf16c

order by artanrc

Answer 1

I'm not sure why your question is about LISTAGG(). It seems to have nothing to do with the issue.

Your subquery is:

(SELECT afl.aflfldc
 FROM fty inner join
      afl
      ON afl.aflftykeyi = fty.ftykeyi
 WHERE fty.ftybezc = 'MDVDesc' and
       AFL.AFLARTKEYI = ART.ARTKEYI and
------------------------^ correlation columns
       AFL.AFLKAVKEYI = ART.ARTKAVKEYI
------------------------^
) as "MDVDesc",

I have highlighted the correlation columns. In an aggregation query, these should be in the GROUP BY but they are not. Hence, Oracle is confused. You are saying to aggregate by some columns. Then you want a correlation by others.

I think an aggregation function will fix the problem:

(SELECT afl.aflfldc
 FROM fty inner join
      afl
      ON afl.aflftykeyi = fty.ftykeyi
 WHERE fty.ftybezc = 'MDVDesc' and
       AFL.AFLARTKEYI = MAX(ART.ARTKEYI0 and
------------------------^ correlation columns
       AFL.AFLKAVKEYI = MAX(ART.ARTKAVKEYI)
------------------------^
) as "MDVDesc",

But to be honest, I don't generally see the need for a subquery like this in an aggregation query; there is usually another way to write the logic.