Invalid identifier

[rjrichardson]

I'm having problems with a script containing subqueries. The error is ERROR at line 3:
ORA-00904: "C"."AMTPID": invalid identifier

Could someone please let me know what I'm missing?


select groupid, benpkg,
medtir, finyer, finper,sum(submed), sum(amtpid) from
(select m.groupid, m.benpkg, m.medtir, m.finyer,
m.finper, sum(m.submed) as submed, sum(c.amtpid)
from memhis m
where groupid = '009944' and finyer = 2008
group by m.groupid, m.benpkg, m.medtir, m.finyer, m.finper,
(select sum(c.amtpid) as amtpid
from clmhis c
where m.groupid = c.groupid
and trim(m.benpkg) = trim(c.prodid)
group by c.groupid, c.prodid, to_char(c.datpid,'yyyy'),
to_char(c.datpid,'mm')));

[ld_be]

Hello


Maybe this will help you to find your results?

select m.groupid, m.benpkg, m.medtir, m.finyer, m.finper, sum(m.submed) as sum_ submed,
(select sum(c.amtpid)
from clmhis c
where m.groupid = c.groupid
and trim(m.benpkg) = trim(c.prodid)
group by c.groupid, c.prodid, to_char(c.datpid,'yyyy'), to_char(c.datpid,'mm')) as sum_amtpid
from memhis m
where groupid = '009944' and finyer = 2008
group by m.groupid, m.benpkg, m.medtir, m.finyer, m.finper


Greetings
ld_be

[rjrichardson]

Thanks but that returns an error as well:
select m.groupid, m.benpkg, m.medtir, m.finyer, m.finper, sum(m.submed) as sum_ submed,
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected.

I used to be able to do scripts like this but haven't done one in so long I just can't get it right!

[skhanal]

You are referencing a table alias C which is not in FROM clause

select groupid, benpkg,
medtir, finyer, finper,sum(submed), sum(amtpid) from
(select m.groupid, m.benpkg, m.medtir, m.finyer,
m.finper, sum(m.submed) as submed, sum(c.amtpid)
from memhis m
where groupid = '009944' and finyer = 2008
group by m.groupid, m.benpkg, m.medtir, m.finyer, m.finper,
(select sum(c.amtpid) as amtpid
from clmhis c
where m.groupid = c.groupid
and trim(m.benpkg) = trim(c.prodid)
group by c.groupid, c.prodid, to_char(c.datpid,'yyyy'),
to_char(c.datpid,'mm')));

[ld_be]

Thanks but that returns an error as well:
select m.groupid, m.benpkg, m.medtir, m.finyer, m.finper, sum(m.submed) as sum_ submed,
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected.

I used to be able to do scripts like this but haven't done one in so long I just can't get it right!

I see now that I placed a space and that was wrong.
sum_ submed must be sum_submed