MYSQL cannot create a view that has a subquery in the FROM clause > db

본문 바로가기

db

MYSQL cannot create a view that has a subquery in the FROM clause

페이지 정보

작성자 서방님 댓글 0건 조회 25회 작성일 11-06-30 14:49

본문

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.

댓글목록

등록된 댓글이 없습니다.

Total 447건 1 페이지
게시물 검색

회원로그인

접속자집계

오늘
138
어제
365
최대
592
전체
40,112

그누보드5
Copyright © seobangnim.com All rights reserved.
자바스크립트를 활성화 하세요![ 브라우저에서 자바스크립트를 활성화하는 방법 ]