How to fix Incorrect syntax error in CTE?

Related searches

When I do this query, it gives me the error "incorrect syntax near t1".

The query works if it's not within the CTE. How can you do this in CTE?

WITH CTE
AS (
    SELECT DISTINCT t1.id
    FROM ticket t1
    WHERE...

    UNION

    SELECT t1.id
    FROM ticket t1
        ,ticket t2
    WHERE t1.id = t2.matching_ticket_id
        AND t2.id NOT IN (
            SELECT ticket_id
            FROM aa
                ,pa
            WHERE aa.id = pa.account_id
            )
    ) t1 --> incorrect syntax near t1
JOIN (
    SELECT DISTINCT t1.id
    FROM ticket t1
    WHERE t1.id NOT IN (
            SELECT ticket_id
            FROM aa
                ,pa
            WHERE aa.id = pa.account_id
            )
    ) t2 ON t2.id = t1.id --> incorrect syntax near t2

UNION

(
    SELECT DISTINCT t1.id
    FROM cd_ticket t1
    WHERE...

    UNION

    SELECT t1.id
    FROM cd_ticket t1
        ,cd_ticket t2
    WHERE t1.id = t2.matching_ticket_id
        AND t2.id NOT IN (
            SELECT ticket_id
            FROM cd_aa
                ,cd_pa
            WHERE aa.id = pa.account_id
            )
    ) t1 --> incorrect syntax near t1
JOIN (
    SELECT DISTINCT t1.id
    FROM cd_ticket t1
    WHERE t1.id NOT IN (
            SELECT ticket_id
            FROM cd_aa
                ,cd_pa
            WHERE aa.id = pa.account_id
            )
    ) t2 ON t2.id = t1.id --> incorrect syntax near t2

It looks like you are trying to JOIN directly to the CTE; that is not how they work.

If you want join to a CTE:

WITH CTE AS ( somequery )
SELECT stuff
FROM aTable AS a INNER JOIN CTE AS c ON a.somefield = CTE.somefield

If you want the CTE to represent two joined subqueries, they need to be within the CTE:

WITH CTE AS (
   SELECT stuff 
   FROM (
      subquery1
   ) AS q1 
   INNER JOIN (
      subquery2
   ) AS q2 ON ....)
SELECT stuff 
FROM CTE

CTE incorrect syntax near ")", If you do need it, simply move down the SELECT statement to be the final statement in your query. WITH table1 AS (QUERY1) , table2 AS (QUERY2) , myCTE (COLUMNS OF THE CTE) AS (CTE_QUERY) SELECT * FROM myCTE), ,anotherCTE That would be attempting to alias the CTE as "a". But the name of a CTE is given at the beginning (in your case it is ResultCTE). So there is no need to alias it as part of the CTE definition. Also, you don't use the CTE. The definition of a CTE must be followed by a SELECT, INSERT, UPTATE, DELETE, or MERGE statement that uses the CTE.

You are confusing "table expressions" with "common table expressions", so you are mixing the syntax of both of them, into something that is not a legal SQL statement.

See the difference, and make up your mind on which one to use:

Table Expression (aka "derived table", "inline view", etc.):

select *
from (
  select ... -- my complex select #1 here
) a
join (
  select ... -- my complex select #2 here
) b on a.column_a = b.column_b

Table expressions appear in the FROM clause and they are named just after the closing parenthesis. The SELECT statement is written at the beginning.

Common Table Expression (CTE):

with 
a as (
  select ... -- my complex select #1 here
),
b as (
  select ... -- my complex select #2 here
)
select * from a join b on a.column_a = b.column_b

As you see Common Table Expressions are declared first, the aliases are defined before the open parenthesis, and they are separated by commas. The main SELECT is written at the end. One advantage of CTEs is that you can use the common table expressions many times, in subsequent CTEs, and in the final/main SELECT.

incorrect syntax near WITH SQL Server Custom SQL, incorrect syntax near WITH SQL Server Custom SQL. I have several custom queries I need to use on a Tableau workbookif Tableau can't do this we have to � select * from cte_test; Also, the semicolon for the WITH is only necessary if there are other statements preceding the CTE. And you should terminate them with a semicolon, not start the CTE with one.

You declare a CTE but then go straight to a union which requires a preceding SELECT.

Your second query has a unncessary trailing bracket with a t1 alias but I assume you want that as a derived table so It will need an opening bracket before the SELECT

You probably want something like this

WITH CTE AS
(
    select distinct t1.id from ticket t1 where ...
)
SELECT id FROM CTE
union
SELECT * FROM 
(
select  t1.id
from    ticket t1,
        ticket t2
where   t1.id = t2.matching_ticket_id and
        t2.id not in  ( select  ticket_id from aa, pa where aa.id = pa.account_id )
)  t1

join

etc......

Also, as a side, you should steer away from the old JOIN syntax:

SELECT ticket_id
FROM   aa, pa
WHERE  aa.id = pa.account_id

and re-write as

SELECT ticket_id
FROM   aa
       JOIN oa
          ON aa.id=pa.account_id

Incorrect syntax near the keyword 'with', Hello Guys, I'm trying to create a CTE, but facing this issue while creating. Can anyone please guide me on this. What I� Msg 102, Level 15, State 1, Procedure sp_MSaddreplsymmetrickey, Line 42 Incorrect syntax near 'TRIPLE_DES'.

Incorrect syntax near the , Incorrect syntax near the keyword 'with'. The Reason for this error is when you are calling CTE Comman Table Expression Inside a Batch you have to start the� 1) Create direct query dataset with a CTE - let it error, then hit Edit Queries. 2) COPY/PASTE the dataset with the CTE, and Edit the query in the new one so that it selects out the same columns as the CTE result set, only '' AS [Column1], '' AS [Column2], etc

CTE Incorrect Syntax – SQLServerCentral, CTE Incorrect Syntax – Learn more on the SQLServerCentral forums. I am trying to create a CTE, but I get an error message saying Incorrect syntax. Please tell me what I'm doing wrong No problem, it is my pleasure. You haven't provided value for Id_Teacher and Adress.You need to provide a value or you can pass NULL or just ignore the column from the INSERT statement. 1. Ignoring Columns

First of all you have to download FileZilla. Then you will have to enter all the necessary information which is required In the syntax error message there must be a path. Next, you have to follow that path. After this you will be able to determine exactly which file or folder is the root cause

Comments
  • Explain the logic you want to implement. Sample data and desired results would help.
  • If you are running the actual query then WHERE... will give an error.
  • Here is an improved version of your query and the best approach of solving this issue is to go part by part by commenting some of the query at the bottom - dbfiddle.uk/…
  • Stop aliasing everything as just t1 and t2! It's fine to use them as table aliases inside your CTEs and expressions, but you'll run into problems reusing them as outer aliases as well.