Error Code: 1248. Every derived table must have its own alias No solution found for query

Question

I am getting an error while using this query in MySQL.

The query logic is correct and I have tried it in Oracle and it's running fine, but I'm getting an error when running this in MySQL.

I looked at previous questions on StackOverflow, but didn't find something to help me.

Here is the query:

select * from
  (select PM.ID, PM.Name, PM.TIMEOUT, PMS.PROCESS_MONITOR_ID, PMS.PROCESS_START_DATE
    from RATOR_IMP.PROCESS_MONITOR as PM
    JOIN RATOR_IMP.PROCESS_MONITOR_STATISTIC as PMS
    ON PM.ID = PMS.PROCESS_MONITOR_ID
    WHERE PM.ENABLED=1 and (PM.NAME='SDRRATINGENGINE11' or PM.NAME='WORKFLOWENGINE1')
    order by PMS.PROCESS_START_DATE desc)
limit 10000;

And here is the error:

Error Code: 1248. Every derived table must have its own alias 
No soultion found for query

Solution 1

You need to provide an alias for the subquery, like so:

select * from
(select PM.ID, PM.Name, PM.TIMEOUT, PMS.PROCESS_MONITOR_ID, PMS.PROCESS_START_DATE
 from RATOR_IMP.PROCESS_MONITOR as PM
 JOIN RATOR_IMP.PROCESS_MONITOR_STATISTIC as PMS
 ON PM.ID = PMS.PROCESS_MONITOR_ID
 WHERE PM.ENABLED=1 and (PM.NAME='SDRRATINGENGINE11' or PM.NAME='WORKFLOWENGINE1')
 order by PMS.PROCESS_START_DATE desc) as s
limit 10000;

From the documentation,

Subqueries are legal in a SELECT statement's FROM clause. The actual syntax is:

SELECT ... FROM (subquery) [AS] name ...

The [AS] name clause is mandatory, because every table in a FROM clause must have a name. Any columns in the subquery select list must have unique names.

Solution 2

Yes you need to specify an alias for the derived data

select x.* from
(select PM.ID, PM.Name, PM.TIMEOUT, PMS.PROCESS_MONITOR_ID, PMS.PROCESS_START_DATE
from RATOR_IMP.PROCESS_MONITOR as PM
JOIN RATOR_IMP.PROCESS_MONITOR_STATISTIC as PMS
ON PM.ID = PMS.PROCESS_MONITOR_ID
WHERE PM.ENABLED=1 and (PM.NAME='SDRRATINGENGINE11' or PM.NAME='WORKFLOWENGINE1')
order by PMS.PROCESS_START_DATE desc)x <-- here 
limit 10000; 

Comments

[Admin]

Thank you :) its running now. Just a small query to ask which is not related to this question. I want to use insert statement and store the data returning from this query to new table. But dont know how to use insert using the above select statement. Please hel p me