CODEWITHSUNDEEP
databaseoracle-databasesubquery
SUVAM ROY
SUVAM ROY

Reputation: 123

"table or view does not exist" Comming in Oracle DB

NAME    PAYMENT
James   1000    
Kepler  2000    
Ronny   1300    
Edward  1500    
Patrick 1700    
John    1660    
Danny   1600    
Hemry   1234    
Harry   1236

select * from( select * from student ) t1 where t1.payment=(select max(payment) from t1)

this is showing wrong

select * from student where payment=(select max(payment) from student)

this is corre

But can anyone clarify why t1 is not correct? how to use t1 is where clause please tell

Upvotes: 0

Views: 133

Answers (3)

DanBot
DanBot

Reputation: 121

with t1 as (select max(payment) payment from student )

select name, student.payment from student ,t1 where student.payment = t1.

Upvotes: 1

Moudiz
Moudiz

Reputation: 7377

This causing error because T1 is not a table , from should be precedent with a table, you can use Common Table Expression

for example

with ct as (
select 1,2 from dual )

select * from ct

in your example

with st as ( select * from student)
select * from st where st.paymnet=(select max(payment) from st)

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143063

CTE is maybe the simplest option; T1 will be CTE itself, and you can then reference it afterwards:

with t1 as (select * from student)
select * from t1
where t1.payment = (select max(payment) from t1);

Upvotes: 1

Related Questions