Subject: | view creation from select fails where select lacks table |
Date: | Thu, 9 Aug 2012 15:45:52 +0000 (UTC) |
To: | "bug-SQL-Translator [...] rt.cpan.org" <bug-sql-translator [...] rt.cpan.org> |
From: | Fred Lindberg <fred.lindberg [...] yudoglobal.com> |
SQL-Translator-0.11012
Parser::MySQL.pm
3.2.0-27-generic-pae #43-Ubuntu
This is perl 5, version 14, subversion 2 (v5.14.2)
Hi, I am using your very helpful module to parse MySQL-workbench output DDL and trying to convert to Oracle DDL. The third statement below fails to parse.
CREATE TABLE IF NOT EXISTS `CurrentEpoch` (`currentEpoch` INT);
DROP TABLE IF EXISTS `CurrentEpoch`;
CREATE OR REPLACE VIEW `CurrentEpoch` AS^M
select unix_timestamp() as currentEpoch
;
it looks like the grammar does not allow the select without a table:
view_select_statement : /[(]?/ /select/i view_column_def /from/i view_table_def /[)]?/
If I modify the SQL statement to include a dummy table, it parses:
CREATE OR REPLACE VIEW `CurrentEpoch` AS^M
select unix_timestamp() from Dual as currentEpoch
;
The table is optional in MySQL select statements:
http://dev.mysql.com/doc/refman/5.1/en/select.html
Parser::MySQL.pm
3.2.0-27-generic-pae #43-Ubuntu
This is perl 5, version 14, subversion 2 (v5.14.2)
Hi, I am using your very helpful module to parse MySQL-workbench output DDL and trying to convert to Oracle DDL. The third statement below fails to parse.
CREATE TABLE IF NOT EXISTS `CurrentEpoch` (`currentEpoch` INT);
DROP TABLE IF EXISTS `CurrentEpoch`;
CREATE OR REPLACE VIEW `CurrentEpoch` AS^M
select unix_timestamp() as currentEpoch
;
it looks like the grammar does not allow the select without a table:
view_select_statement : /[(]?/ /select/i view_column_def /from/i view_table_def /[)]?/
If I modify the SQL statement to include a dummy table, it parses:
CREATE OR REPLACE VIEW `CurrentEpoch` AS^M
select unix_timestamp() from Dual as currentEpoch
;
The table is optional in MySQL select statements:
http://dev.mysql.com/doc/refman/5.1/en/select.html
SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]]
Thanks!
Fred Lindberg