3

I have a enquiry about creating view in MySQL workbench. I have error: " View's SELECT contains a subquery in the FROM clause" when saving.

This is a copy of my statement:

CREATE VIEW viewMorningReport AS 
select z.AllocationDate, z.LocationName, z.StationName, a.00000100, b.01000200, c.02000300  from
(SELECT DISTINCT AllocationDate, LocationName, StationName FROM satsschema.employeeslot
where LocationName = 'T2 PML'
and StationName is not null) z
left outer join 
(SELECT AllocationDate, LocationName, StationName, EmpName AS '00000100' FROM     satsschema.employeeslot
WHERE Assigned = true
and (EmpTime = '00:00:00' && EmpTime < '01:00:00')) a
on z.LocationName = a.LocationName and z.StationName = a.StationName
left outer join
(SELECT  AllocationDate, LocationName, StationName, EmpName AS '01000200' FROM    satsschema.employeeslot
WHERE Assigned = true
and (EmpTime = '01:00:00' && EmpTime < '02:00:00')) b
on a.LocationName = b.LocationName and a.StationName = b.StationName
left outer join
(SELECT  AllocationDate, LocationName, StationName, EmpName AS '02000300' FROM satsschema.employeeslot
WHERE Assigned = true
and (EmpTime = '02:00:00' && EmpTime < '03:00:00')) c
on b.LocationName = c.LocationName and b.StationName = c.StationName

Any idea where went wrong?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Philemon
  • 117
  • 1
  • 11
  • 2
    possible duplicate of [MySQL: View with Subquery in the FROM Clause Limitation](http://stackoverflow.com/questions/206062/mysql-view-with-subquery-in-the-from-clause-limitation) – OMG Ponies Sep 04 '12 at 03:57
  • 1
    Possible duplicate of [MySQL: View with Subquery in the FROM Clause Limitation](http://stackoverflow.com/questions/206062/mysql-view-with-subquery-in-the-from-clause-limitation) – Christophe Weis Feb 02 '17 at 07:49

2 Answers2

2

As the official documentation says

E.4. Restrictions on Views

(..)

Subqueries cannot be used in the FROM clause of a view.

One option could be creating a View for each subquery. Another one, is modify your view, to avoid subquerys in the from clause

Community
  • 1
  • 1
Gonzalo.-
  • 12,512
  • 5
  • 50
  • 82
0

you can create views of subquery and call him in principal view

ahmed3991
  • 1