asked on

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

Sean Stuber

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

amaher84

ASKER

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?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

amaher84

ASKER

Thanks so much for your help

Sean Stuber

glad I could help

Keep Getting Error &quot;Invalid Identifier&quot;

Keep Getting Error "Invalid Identifier"