MYSQL cannot create a view that has a subquery in the FROM clause
Filed Under (Others, mysql) by admin on 14-07-2009
Error Code : 1349
View’s SELECT contains a subquery in the FROM clause
It’s a long standing MYSQL BUG
You cannot do this!
*******************************************************************
DROP VIEW IF EXISTS `db`.`empmain_vw`;
CREATE
VIEW `db`.`empmain_vw`
AS
select * from (select * from employees group by salary) a;
********************************************************************
In the code above we are trying to build a view called empmain_vw
But an error appears
Error Code : 1349
View’s SELECT contains a subquery in the FROM clause
This means you cannot create a view with a SUBQUERY in the FROM clause, no no no…… ohh! no!
Simple workaround (double task) is to create a view of the subquery first, lets create empsalary_vw
*******************************************************************
DROP VIEW IF EXISTS `db`.`empsalary_vw`;
CREATE
VIEW `db`.`empsalary_vw`
AS
select * from employees group by salary;
********************************************************************
Then combine it with your previously wanna build view, so lets create again empmain_vw
*******************************************************************
DROP VIEW IF EXISTS `db`.`empmain_vw`;
CREATE
VIEW `db`.`empmain_vw`
AS
select * from empsalary_vw;
********************************************************************
This one will work now coz there is no subquery in the FROM clause
The bug has been there i think for a long time, other DBMS is capable creating views even with subquery on clause oracle and etc.
