I came up with this query now when i tried to create a view it failed to create
SELECT aDate FROM (
SELECT @maxDate - INTERVAL (a.a+(10*b.a)+(100*c.a)+(1000*d.a)) DAY aDate FROM
(SELECT 0 AS a 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) a,
(SELECT 0 AS a 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) b,
(SELECT 0 AS a 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) c,
(SELECT 0 AS a 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) d,
(SELECT @minDate := DATE_FORMAT(SYSDATE(), '%Y-01,01'), @maxDate := DATE_FORMAT(DATE_ADD(SYSDATE(), INTERVAL 1 YEAR), '%Y-01-01')) e
) f
WHERE aDate BETWEEN @minDate AND @maxDate
the error is this
Error Code : 1349
View's SELECT contains a subquery in the FROM clause
now my possible solution is to create views for every subquery, but if i do that it will give a big impact to its performance.
so i am asking how i can accomplish this without create new views for its subquery.
I will appreciate for any help will come.
thank you.