ORA-22818: subquery expressions not allowed here - Group BY

Gurus,

How do I get around the fact that I can't add a subquery to my group by clause and get the result I need?

SELECT kat.katkeyi,
vgb.vgbbezc ,
dok.dokkeyi,
abp.abpbezc,
dok.dokinfc,
NVL (zplbezc, 'No TimePlan'),
DOK.DOKOWNC,
stragg (dok.doknlc),
(SELECT AFL.AFLFLDC
FROM AFL
INNER JOIN FTY
ON AFL.AFLFTYKEYI = FTY.FTYKEYI
WHERE FTY.FTYBEZC = 'LTtableID'
AND AFL.AFLARTKEYI = ART.ARTKEYI
AND AFL.AFLKAVKEYI = ART.ARTKAVKEYI
) AS lttableid
FROM kat, dok, vgb, abp, zpl
WHERE zpl.zplabpkeyi(+) = abp.abpkeyi
AND kat.katvgbkeyi = vgb.vgbkeyi
AND dok.dokkatkeyi = kat.katkeyi
GROUP BY kat.katkeyi, vgb.vgbbezc, dok.dokkeyi, abp.abpbezc, dok.dokinfc, zpl.zplbezc, DOK.DOKOWNC,
(SELECT AFL.AFLFLDC
FROM AFL
INNER JOIN FTY
ON AFL.AFLFTYKEYI = FTY.FTYKEYI
WHERE FTY.FTYBEZC = 'LTtableID'
AND AFL.AFLARTKEYI = ART.ARTKEYI
AND AFL.AFLKAVKEYI = ART.ARTKAVKEYI);

ORA-22818: subquery expressions not allowed here

Oracle Database

Last Comment

xbox360dp

8/22/2022 - Mon

Gerwin Jansen

6/29/2012

Can you change the subquery in the group by to lttableid?

slightwv (䄆 Netminder)

6/29/2012

You cannot use the alias in the same level that you create it in a group by.

Try this:

SELECT katkeyi,
  vgbbezc ,
  dokkeyi,
  abpbezc,
  dokinfc,
  zplbezc,
  DOKOWNC,
  stragg (dok.doknlc),
  lttableid
from (
SELECT kat.katkeyi,
  vgb.vgbbezc ,
  dok.dokkeyi,
  abp.abpbezc,
  dok.dokinfc,
  NVL (zplbezc, 'No TimePlan') zplbezc,
  DOK.DOKOWNC,
  dok.doknlc,
(SELECT AFL.AFLFLDC
  FROM AFL
  INNER JOIN FTY
  ON AFL.AFLFTYKEYI  = FTY.FTYKEYI
  WHERE FTY.FTYBEZC  = 'LTtableID'
  AND AFL.AFLARTKEYI = ART.ARTKEYI
  AND AFL.AFLKAVKEYI = ART.ARTKAVKEYI
  ) AS lttableid
FROM kat, dok, vgb, abp, zpl
WHERE zpl.zplabpkeyi(+) = abp.abpkeyi
AND kat.katvgbkeyi      = vgb.vgbkeyi
AND dok.dokkatkeyi      = kat.katkeyi
)
GROUP BY katkeyi, vgbbezc, dokkeyi, abpbezc, dokinfc, zplbezc, DOKOWNC, lttableid