MySQL Create View WITH CTE

cteMySQLmysql-5.6view

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

Best Answer

You need to use 8.0 for CTE in MySQL

Related Question