Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00942 ERROR
ORA-00942 ERROR [message #215690] Tue, 23 January 2007 09:34 Go to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Note that in order to debug my code I used process of elimination by cutting my qry down to one table then keep on adding to my code. Here is to illustrate my point:

SQL> r
1 select count(*) from detail_main
2 where f_id like '011' -- like or = same thing
3* or f_id like '012'

COUNT(*)
----------
159086

Any input would be greately appreciated!

Thanks in advance.

-------
My_Bug

The error msg. is a bogus one if I reverse the order of the tables in my from clause I get the same error.

SQL> r
1 select * from d.d_lookup,
2 dt.t_tax,
3 mt.m_tax,
4 dm.detail_main
5 where dm.t_id = dt_id
6 and dm.f_id = '011'
7 or dm.f_id = '012'
8 and dt.f_id = '011'
9 or mtf_id = '012'
10* order by d.d_id
dm.detail_main -- bogus error table does exist
*
ERROR at line 4:
ORA-00942: table or view does not exist

other example:

SQL> ed
Wrote file afiedt.buf

1 select r.date, r.r_num, d_id, f_id, d_desc
2 from d.d_lookup,
3 r.rcpt,
4 dt.d_tax,
5 mt.m_tax,
6 dm.detail_main
7 where dm.r_num = r.r_num
8 and dm.d_id = d.d_id
9 and dm.f_id = '011'
10 or dm.f_id = '012'
11 and dt.f_type_id = '011'
12 or mt.f_id = '012'
13* order by dm.d_id
SQL> /
dm.detail_main
*
ERROR at line 6:
ORA-00942: table or view does not exist

here is to illustrate the bogus error msg:
If I moved my main table one position up the chain and my lookup down see error below:

d.d_lookup
*
ERROR at line 6:
ORA-00942: table or view does not exist

Re: ORA-00942 ERROR [message #215694 is a reply to message #215690] Tue, 23 January 2007 09:40 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
dm.detail_main -- bogus error table does exist

possible TABLE is exists but above table is NOT IN "DM" Schema.


------------------------
Your query alias define in wrong way.
Try below query.
select r.date, r.r_num, d_id, f_id, d_desc
 from  d_lookup d,
       rcpt r,
       d_tax dt,
       m_tax mt,
       detail_main dm
 where dm.r_num = r.r_num
 and   dm.d_id = d.d_id
 and   dm.f_id = '011'
 or    dm.f_id = '012'
 and   dt.f_type_id = '011'
 or    mt.f_id = '012'
 
 order by dm.d_id

regards
Taj

[Updated on: Tue, 23 January 2007 09:46]

Report message to a moderator

Re: ORA-00942 ERROR [message #215723 is a reply to message #215694] Tue, 23 January 2007 11:55 Go to previous message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thanks Taj. It is working beautifully.
Thanks!
Previous Topic: EXISTS Vs Correlated subquery
Next Topic: How to Display a Result based on Conditions?
Goto Forum:
  


Current Time: Wed Dec 07 20:27:28 CST 2016

Total time taken to generate the page: 0.12373 seconds