CODEWITHSUNDEEP
sqlsql-serverselectjoinsyntax-error
user3115933
user3115933

Reputation: 4443

SQL Server syntax error when joining CTEs

I am using SQL Server 2014 and I am having trouble with my SQL query below. It seems that there is a syntax error somewhere but I just can't find it.

I am joining two select queries from two separate views, namely ReservationList and ARACCOUNT.

USE MyDatabase

;WITH q1 AS (
    select * 
    from ReservationList
    WHERE [Market Segment Code] <> 'COMP' AND 
    NOT (Status] = 'CANCELED' OR [Status] = 'NOSHOW' OR [Status] = 'WAITLIST') AND 
    NOT [Departure Date] < '2014-07-01' AND 
    NOT [Market Segment Code] = 'COMP' AND 
    NOT [Arrival Date] = [Departure Date]
)a

q2 AS (
    SELECT 
        [PMSConfirmationNumber],
        [FOC ACCOUNT],
        [AR ACCOUNT NAME]
    FROM 
        ARACCOUNT
)b

Select 
    q1.* 
from 
    q1
    JOIN q2 ON q1.PmsConfirmatioNnumber = q2.foh_PmsConfirmationNumber

Error msg is as follows: 

Msg 102, Level 15, State 1, Line 21
Incorrect syntax near 'a'.
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near ')'.

I also need the fields I selected from ARACCOUNT to appear in the final output. How do I modify my query to include my q2 selections?

Upvotes: 2

Views: 112

Answers (1)

Mureinik
Mureinik

Reputation: 311163

You're missing a comma between the two CTEs. Also, drop the alias - you don't need them when defining a CTE:

;WITH q1 AS (


 select * 

 from ReservationList

 WHERE [Market Segment Code] <> 'COMP'

 AND NOT ([Status] = 'CANCELED' OR [Status] = 'NOSHOW' OR [Status] = 'WAITLIST')

 AND NOT [Departure Date] < '2014-07-01'

 AND NOT [Market Segment Code] = 'COMP'

 AND NOT [Arrival Date] = [Departure Date]

), -- alias dropped and comma added here

q2 AS (

SELECT [PMSConfirmationNumber],[FOC ACCOUNT],[AR ACCOUNT NAME]

 FROM ARACCOUNT

) -- alias dropped

Select q1.*, q2.* from q1
JOIN q2 ON q1.PmsConfirmatioNnumber = q2.foh_PmsConfirmationNumber

Upvotes: 5

Related Questions