Home » SQL & PL/SQL » SQL & PL/SQL » need Query Help
need Query Help [message #415485] Tue, 28 July 2009 01:34 Go to next message
harrryrk@gmail.com
Messages: 3
Registered: July 2009
Junior Member
hi

I have a similar query like the following

select empname,empnum
from employee emp
where empnum > 0
and
(select count(*) from
(select division_id,division_num from
empdiv div where div.division_id = emp.division_id
)
) > 0

when I execute the query in Oracle 11g it is throwing following exception

ORA-00904: "emp"."division_id". invalid identifier

and which is executing successfully on SQLSERVER 2005.

please give me some help regarding this problem.

thanks,
harry
Re: need Query Help [message #415493 is a reply to message #415485] Tue, 28 July 2009 01:46 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
What happens if you code it as

select empname,empnum
from employee emp
where empnum > 0
and exists (select null from empdiv div 
                        where div.division_id = emp.division_id)

Re: need Query Help [message #415507 is a reply to message #415485] Tue, 28 July 2009 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Use SQL*Plus and copy and paste your session.
Always post a test case we can reproduce.

Regards
Michel
Re: need Query Help [message #415512 is a reply to message #415493] Tue, 28 July 2009 02:19 Go to previous messageGo to next message
harrryrk@gmail.com
Messages: 3
Registered: July 2009
Junior Member
actually I copied a small part of my huge query
where the error occurs.

actually it looks like...

select empname,empnum
from employee emp
where empnum > 0
and
(select count(*) from
(select division_id,division_num from
empdiv div where div.division_id = emp.division_id
)
) =
(select count(*) from empdept dept where
dept.dept_id = emp.dept_id)
AND
.........

please ignore the table names I've taken those names to explain my problem only.


Re: need Query Help [message #415524 is a reply to message #415512] Tue, 28 July 2009 03:22 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ It is still not formatted
2/ Your problem is "emp"."division_id". invalid identifier

Regards
Michel
Previous Topic: No data showing up in synonyms
Next Topic: Tunning of pl/sql package (merged 6)
Goto Forum:
  


Current Time: Wed Dec 07 16:42:11 CST 2016

Total time taken to generate the page: 0.16645 seconds