ORA-00942 table or view does not exist

Question

I have a sql query in db oracle I want to output max value. SQL query contains unique field values

Sql query below contains duplicate rows:

select 
field1,field2,field3,field4,field5,field6,field7,field8,field9,MAX(field10) as field10

from(

select 
field1,field2,field3,field4,field5,field6,field7,field8,field9,field10

from table

)tabl

GROUP BY field1,field2,field3,field4,field5,field6,field7,field8,field9

I corrected sql request but it doesn't work.
Sql query below an error message is displayed :ORA-00942 table or view does not exist.

select

field1,field2,field3,field4,field5,field6,field7,field8,field9

,(SELECT MAX(field10)  FROM tabl ) as field10

from(
select 
field1,field2,field3,field4,field5,field6,field7,field8,field9,field10

from table
)tabl

how can i fix it? Thanks for your help!

,(SELECT MAX(field10) FROM tabl ) as field10 => You mean table not tabl?? Because the alias tabl is not valid in inner select field. — Utsav
table is displayed blue because it is a SQL keyword. do you mean the keyword or is it the name of one of your tables? — miracle173
The format of your SQL code is rather poor. can you impreove it? is there an line number(column number in your error message? — miracle173

Utsav Utsav · Accepted Answer · 2017-05-25T12:59:57

First of all, your first query can not have duplicates, as you are grouping by all columns and using aggregate function of remaining column, field10.

Now coming back you your error. The reason for error is ,(SELECT MAX(field10) FROM tabl ) as field10. Because tabl is an alias for outer table and you are using it inside the select clause, hence the error.

Also you can further simplify it like below and it will also not give you duplicates.

select 
field1,field2,field3,field4,field5,field6,
  field7,field8,field9,MAX(field10) as field10
from table
GROUP BY field1,field2,field3,field4,field5,field6,
 field7,field8,field9

ORA-00942 table or view does not exist

1 Answers