0

I have successfully created a VIEW in SQL Server as follows. But when I try the same in MySql, Error coming.

SQL SERVER Command:

CREATE VIEW [dbo].[vw_PurchParent] as

WITH cte AS 
 (
  SELECT a._Id, a._parentId, a._name, a._IsLedger
  FROM tbl_ChartOfAcc a
  WHERE _Id = 1
  UNION ALL
  SELECT a._Id, a._parentid, a._Name, a._IsLedger
  FROM tbl_ChartOfAcc a JOIN cte c ON a._parentId = c._id
  )
  SELECT *
  FROM cte where _IsLedger = 'Y'
GO

Error in MySQL:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cte AS( SELECT a._Id, a._parentId, a._name, ' at line 1

Server version: 5.6.41-84.1 - Percona Server (GPL), Release 84.1, Revision b308619

1

You need to use 8.0 for CTE in MySQL

  • This WITH defines a CTE, it has nothing to do with WITH CHECK OPTION. – ypercubeᵀᴹ May 12 at 9:03
  • @ypercube I am aware of that - it doesn't mean that there isn't a bug etc – Cez May 12 at 9:04
  • No Luck... :( Same Error... "near '(WITH cte AS ( SELECT a._Id, a._parentId, a._name, a._IsLedger FROM tbl' at line 5" – Arun Madhav May 12 at 9:06
  • @arun as per dba.stackexchange.com/questions/237944/… - which version are you using? – Cez May 12 at 9:09
  • Ok @Cez, I'll upgrade and Inform. Thank you – Arun Madhav May 12 at 9:28
5

First of all, MySQL has been supporting CTEs since relatively recently, starting from version 8.0 (as has been mentioned by others). The syntax error you are getting seems to indicate that you are using an older version. Therefore, your database server requires an upgrade1.

Apart from that, there are issues with your CREATE VIEW statement that need to be addressed in order to make it work in MySQL.

  1. Square bracket delimiters.

    Use of square brackets to delimit names, like in the statement's first line:

    CREATE VIEW [dbo].[vw_PurchParent] as
    

    is specific to Transact-SQL. For MySQL, you need to replace them with either double quotes (") or backticks (`). Or, in this case, you can just omit them altogether, because the name contains only letters and an underscore, which are allowed to be used in names without delimiting.

  2. Schema name.

    In SQL Server, databases contain schemas, and schemas contain objects (tables, views, functions, stored procedures etc.). Therefore, the full name of an object consists of three parts:

    database.schema.object
    

    When it only has two parts, like in this case ([dbo].[vw_PurchParent]), then it is interpreted as

    schema.object
    

    However, MySQL does not support schemas in databases. When an object reference has two parts, it is interpreted as

    database.object
    

    Therefore, it is likely that you will want to drop the dbo. part of the view name when adjusting the script for MySQL.

  3. Missing keyword RECURSIVE.

    The CTE in this view's definition is a recursive CTE2. MySQL supports recursive CTEs but, unlike SQL Server, it requires that the RECURSIVE keyword be specified when one or more CTEs in the WITH clause are recursive.

    Therefore, the WITH line of the definition will need to be rewritten as

    WITH RECURSIVE cte AS
    
  4. The GO keyword.

    The GO keyword at the end of your script is specific to the SQL Server platform, although it is not part of the Transact-SQL dialect per se. Rather it is an instruction interpreted by some of SQL Server client tools (SSMS, sqlcmd, the now deprecated osql). It indicates the end of a batch of statements. In this case, it should simply be replaced with a semicolon (;).

    Note that it is also a good practice in general to always end a statement with a semicolon, which is the standard statement delimiter in SQL.


1 Alternatively, you could also consider switching to MariaDB. MariaDB is a fork of MySQL, and as such it is compatible with MySQL in many respects, including the SQL dialect aspect. At the same time, though, MariaDB is known to often introduce advanced language features – which includes CTE, window functions and others – before MySQL does. For instance, as already mentioned, CTEs have been introduced to MySQL in version 8.0, which was released one year ago (in April 2018). In contrast, MariaDB has been supporting CTEs for about 2-3 years, as of writing this. (CTEs were first added to version 10.2 in 2016; the version became a stable release in 2017.)

2 It represents a UNION ALL query with a self-reference in the second leg, which is the specific structure that a recursive CTE is required to have.

  • 1
    And those are just the tip of the iceberg when it comes to syntax and feature differences between RDBMS vendors. – Rick James May 12 at 15:23
  • 2
    @RickJames: Absolutely. If even a single statement produces this many differences, then addressing all of them should take a good chapter of a book. – Andriy M May 12 at 15:32
  • @AndriyM Thank you... I have tried creating the view by considering the points (1,2 and 4) except the RECURSIVE command. It's understood that I have to use the latest version of MySql for this view to create. – Arun Madhav May 13 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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