Link to home
Start Free TrialLog in
Avatar of xbox360dp
xbox360dp

asked on

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
Avatar of Gerwin Jansen
Gerwin Jansen
Flag of Netherlands image

Can you change the subquery in the group by to lttableid?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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

Open in new window

Avatar of xbox360dp

ASKER

Slightwv,

I get an error when I attempt to execute the SQL you posted.

ORA-00904: "DOKOWNC": invalid identifier
Does DOKOWNC exist in the dok table?

I cannot ensure the SQL will run since I do not have your tables.
Yes ... it seems to error out of the whatever column is before "LTTABLEID".
I did notice a small error but I don't think it is causing your error.

Change:
stragg (dok.doknlc),

to:
stragg (doknlc),
This didn't work.
>>This didn't work.
Post your error please, just saying something does not work isn't helping... You have one open question from slightwv:
Does DOKOWNC exist in the dok table?
Yes ... it seems to error out of the whatever column is before "LTTABLEID".

ORA-00904: "DOKOWNC": invalid identifier
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the effort slightwv!

I decided to take the subqueries out all together.