Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Okay, I'm moving a database from SQL Server (which I already miss) to MySql 5.6. I'm having a problem creating a list of valid dates. In SQL Server I just used CTEs to build up numbers, then months, then years, then days of the month, then cross join'd everything together to create a valid list. I'd rather do that then create a giant table listing all valid dates from the start to the end of time. Converting it to a set of views, however, doesn't seem to be working:

create view `Numbers` as
    select 0 N 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;
create view `Months` as
    select n.N+1 MonthNum union all select 11 union all select 12
    from `Numbers` n;

Error from MySql: Error Code: 1109. Unknown table 'n' in field list.

share|improve this question
    
Totally off topic here, but I'm curious as to why you are switching. –  mrdenny Mar 15 '13 at 0:41
    
Because SQL server licensing is hideously expensive for a startup. And now that we started a MySql migration, we're seriously considering Postgres instead as it's more capable than MySql and doesn't seem to have reliability issues that I'd hoped they would have ironed out in the 6 years since I worked with it... –  Chris Wiegand May 9 '13 at 23:11
    
Makes total sense. Have you looked at BizSpark from Microsoft? It's a program to get you all your Microsoft licenses for free for I believe 3 years. microsoft.com/bizspark/default.aspx Might make life easier. –  mrdenny May 12 '13 at 17:12

1 Answer 1

You have to rewrite the second View as follows:

create view `Months` as
    select N+1 MonthNum from `Numbers`
    union all select 11
    union all select 12
;

Here is proof

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create view `Numbers` as
    ->     select 0 N 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;
Query OK, 0 rows affected (0.05 sec)

mysql> create view `Months` as
    ->     select N+1 MonthNum from `Numbers`
    ->     union all select 11
    ->     union all select 12
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql>

Behold the output of the Query

mysql> select * from Months;
+----------+
| MonthNum |
+----------+
|        1 |
|        2 |
|        3 |
|        4 |
|        5 |
|        6 |
|        7 |
|        8 |
|        9 |
|       10 |
|       11 |
|       12 |
+----------+
12 rows in set (0.00 sec)

mysql>

Give it a Try !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.