Keep Getting Error "Invalid Identifier"

Hello,
I Keep getting this error: ora-00904: Invalid Identifier "INC.AGENTID" when I run the SQL below. Not sure where I need to register the temp table inc or if that is what I have to do.  How do I get rid of this error?

Thanks
with inc as
(select agentID as agentID_incentive, person, CREATEDATE, CLOSEDATE, sum(TOTALINCENTIVEONE) as TOTALINCENTIVEONE, 
LOCATION, LEVELT
from (select p.agent_idstring as agentID, p.person, trunc(p.createdate, 'MONTH') as CREATEDATE, 
trunc(p.m_date, 'MONTH') as CLOSEDATE, 
 
case when p.cycl_foo = '0' and = 'N' then 
((p.dollars_gross - p.dollars_returned) * b.m_current)  
when p.cycl_foo = '1' and = 'N' then 
((p.dollars_gross - p.dollars_returned) * b.bucket1)  
when p.cycl_foo = '2' and = 'N' then 
((p.dollars_gross - p.dollars_returned) * b.bucket2)  
when p.cycl_foo = '3' and = 'N' then 
((p.dollars_gross - p.dollars_returned) * b.bucket3)  
when p.cycl_foo = '4' and = 'N' then 
((p.dollars_gross - p.dollars_returned) * b.bucket4)   
when p.cycl_foo = '5' and = 'N' then 
((p.dollars_gross - p.dollars_returned) * b.bucket5)  
when p.cycl_foo = '6' and = 'N' then  
((p.dollars_gross - p.dollars_returned) * b.bucket6)  
when p.cycl_foo = '7' and = 'N' then  
((p.dollars_gross - p.dollars_returned) * b.bucket7)   
when p.cycl_foo = '8' and = 'N' then  
((p.dollars_gross - p.dollars_returned) * b.bucket7)   
end as TOTALPAID,
 
he.location as LOCATION,
he.Level as LEVELT
 
from m_paymentstable p
join m_buckettable b on trunc(p.m_date, 'MONTH') = b.m_date
And trunc(p.createdate, 'MONTH') >= trunc(trunc(sysdate, 'MONTH')-180, 'MONTH')
join m_hourstable he on p.racf_idstring = he.agentID
)  
group by agentID, person, CREATEDATE, CLOSEDATE, SITE, PROCESS
),
 
assgdtime as
(select case when agt.m_input in ('CT', 'MP', 'NPO', 'PP', 'IPP', 'IMP', 'INPO', 'ICT') 
and trunc(inc.createdate, 'MONTH') = trunc(agt.m_date, 'MONTH')
then sum(agt.totalcalls)
end as TOTALRPC
from m_assgdtable agt where inc.agentID = agt.agentlogin)
 
select * from inc, assgdtime

Open in new window

amaher84Asked:
Who is Participating?
 
sdstuberCommented:
you would need to group by the agent id and month, right now you're summing across all agents and all months.


(select inc.agentid, inc.createdate, sum(agt.totalcalls) as TOTALRPC
from m_assgdtable agt, inc where agt.agentlogin = inc.agentId
and inc.createdate = trunc(agt.m_date, 'MONTH'))
and agt.m_input in ('CT', 'MP', 'NPO', 'PP', 'IPP', 'IMP', 'INPO', 'ICT')
group by inc.agentid,inc.createdate)
0
 
sdstuberCommented:
two things...

you have aliased agentid to be agenitid_incentive  
so

inc.agentid  doesn't exist

also check your case statement,  you "and" clauses are incomplete
0
 
amaher84Author Commented:
Thanks sdstuber, that helped me get rid of the error and get some output of data. This is what I changed the code to:

assgdtime as
(select sum(agt.totalcalls) as TOTALRPC
from m_assgdtable agt, inc where agt.agentlogin = inc.agentId
and inc.createdate = trunc(agt.m_date, 'MONTH'))
and agt.m_input in ('CT', 'MP', 'NPO', 'PP', 'IPP', 'IMP', 'INPO', 'ICT'))

I'm able to get output but unfortunately it puts the same data for every person for everymonth, where i was hoping the above would output the data for that agent based off of the create date.  

Can I ask you why the code above would do that?
0
 
amaher84Author Commented:
Thanks so much for your help
0
 
sdstuberCommented:
glad I could help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.