0

I get the following error:

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

with this SQL:

SELECT DISTINCT 
    Name, t1.[value] AS DateTime
FROM 
    BmaCare.Questionnaire qu
LEFT JOIN 
    BmaCare.Pregnancy pr ON qu.PregnancyId = pr.Id
CROSS APPLY 
    OPENJSON(qu.Data, '$.actions')
        WITH (
             entries NVARCHAR(MAX) '$.entries' AS JSON) j
CROSS APPLY 
    OPENJSON(j.entries)
        WITH (
              [key] NVARCHAR(100) '$.key',
              [value] NVARCHAR(100) '$.value') t1
WHERE
    t1.[key] = 'Admission.DateTime' OR t1.[key] = 'Discharge.DateTime' 
    AND t1.[value] IS NOT NULL
    AND pr.Uuid = '50E8835B-4ECE-4333-9B5B-DD6AF611989C'
ORDER BY 
    DateTime DESC

A solution for this has been given here: Incorrect syntax near the keyword 'with'. If this statement is a common table expression which amounts to preceding every WITH by a semicolon. Very simple. When I do so in my SQL string, I get

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '('.

What do I do to get the query working?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Cooz
  • 83
  • 1
  • 2
  • 10
  • i feel like you're running this against an older version of SQL – JamieD77 Jun 24 '19 at 14:31
  • 1
    @a_horse_with_no_name not when using `OPENJSON` ([Example 4 - Combine relational rows and JSON elements with CROSS APPL](https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-2017#example-4---combine-relational-rows-and-json-elements-with-cross-apply)). I would guess the OP isn't using SQL Server 2016+, as the others have. – Thom A Jun 24 '19 at 14:36
  • I use SQL Server 2017 - so that's not it. Should 've mentioned it. – Cooz Jun 25 '19 at 10:58

1 Answers1

0

Your database must be in compatibility level 130 (SQL Server 2016) or above in order to use OPENJSON. Otherwise, you will get a syntax error indicating the misplace WITH statement.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • It is. I use SQL Server 2017. – Cooz Jun 25 '19 at 10:55
  • @Cooz, are you certain your database compatibility level is 130 or 140? Verify with `SELECT compatibility_level FROM sys.databases WHERE database_id = DB_ID();`. I get the same error when I try to run your script in a lower compatibility level but it parses successfully in 140. – Dan Guzman Jun 25 '19 at 11:37
  • The compatibility level is 140. – Cooz Jun 25 '19 at 14:27
  • @Cooz, I successfully ran the exact query in your question after creating the tables with the columns referenced in the query. The only cause I can think of is that your are running the query in a database other than the one that returned the 140 compatibility level. – Dan Guzman Jun 25 '19 at 14:37