With statement in PYODBC sql statement

Question

I am not able to execute a SQL statement with pyodbc if I use the With clause in the SQL statement.

This works:

import pyodbc

cnxn = pyodbc.connect('DSN=database;PWD=password' )
cursor = cnxn.cursor()

sql = """
    SELECT top 10 *                                 
    FROM    table
    """
qnnum = pd.read_sql(sql, cnxn)

This does NOT work:

import pyodbc

cnxn = pyodbc.connect('DSN=database;PWD=password' )
cursor = cnxn.cursor()

sql = """
    With A as(SELECT top 10 *                                   
    FROM    table)
    select * from A
    """
qnnum = pd.read_sql(sql, cnxn)

Answer 1

I have tested WITH clause using pyodbc(Python 3.7) on Teradata( 15.10.07.37) and it worked.

Comments are supported as well within the query string in the following form.

/* comment1 */

Hope that helps.

Answer 2

I was able to resolve the problem by simplifying the formatting and removing comments from the original code.