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
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.