-4
SELECT * FROM (SELECT ((DATE_SUB(curdate(), INTERVAL 4 WEEK)) + INTERVAL c.number DAY) AS date_year,

(DATE_FORMAT(((DATE_SUB(curdate(), INTERVAL 4 WEEK)) + INTERVAL c.number DAY),'%w') + 1) as day
FROM (SELECT singles + tens + hundreds number FROM 
( SELECT 0 singles
UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
) singles JOIN 
(SELECT 0 tens
UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
) tens  JOIN 
(SELECT 0 hundreds
UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
) hundreds
) c  
WHERE c.number BETWEEN 0 and 364 ) a
juergen d
  • 201,996
  • 37
  • 293
  • 362
Gaspar Tomelden
  • 1
  • 1
  • 1
  • 2
    (1) Where's the question?, (2) Post as INDENTED code, (3) Reduce to smallest possible query that reproduces issue – obe Aug 27 '16 at 17:45
  • 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

1 Answers1

4

Although you did not ask any question, I try to guess: you get the error message in the title when you try to run your query in the question.

As mysql documentation on create view statement says:

A view definition is subject to the following restrictions:

Before MySQL 5.7.7, the SELECT statement cannot contain a subquery in the FROM clause.

So, either you upgrade your mysql to v5.7.7 or you cannot use subqueries in the from clause of a view. Consider using helper tables insted of the union subqueries.

Shadow
  • 33,525
  • 10
  • 51
  • 64