Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Keep Getting Error "Invalid Identifier"

Posted on 2009-04-19
5
Medium Priority
?
543 Views
Last Modified: 2013-12-07
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

0
Comment
Question by:amaher84
  • 3
  • 2
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 24183798
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
 

Author Comment

by:amaher84
ID: 24187406
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 24187428
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
 

Author Closing Comment

by:amaher84
ID: 31572113
Thanks so much for your help
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24187825
glad I could help
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this article, we’ll look at how to deploy ProxySQL.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question