0

This is part of my code

    SELECT 
    JSON_VALUE (c.value,'$.orderNo') as WebOrderNo,
    JSON_VALUE (c.value,'$.orderStatus') as orderStatus,
    JSON_VALUE (c.value,'$.createDate') as Date,
    JSON_VALUE (c.value,'$.amountSum') as ListTotal,
    JSON_VALUE (c.value,'$.discount') as discount,
    JSON_VALUE (c.value,'$.user.email') as WebEmail,
    JSON_VALUE (c.value,'$.user.name') as OrderingAgent,
    JSON_VALUE (c.value,'$.user.phone') as WebPhone,    
    JSON_VALUE (c.value,'$.user.dealerID') as ShipToID, 
    JSON_VALUE (p.value,'$.quantity')as quantity,
    JSON_VALUE (p.value,'$.amount')as ExtendedPrice,
    JSON_VALUE (p.value,'$.LineNumber')as LineNo,
    JSON_VALUE (p.value,'$.Category')as Category,
    JSON_VALUE (p.value,'$.attributes."Side Stile Width"')as SSD,

I get error:

Incorrrect syntax near 'LineNo'. Expecting ID, QUOTED_ID, etc. JSON_VALUE (c.value,'$user.name') as LineNo

However, if I change it to

    JSON_VALUE (p.value,'$.LineNumber')as LineNumber,

The error goes away.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Pattie
  • 39
  • 8
  • 3
    Can you post a full repro on [db<>fiddle](https://sqlblog.org/fiddle)? (I think it's something other than the column ending in `No`, otherwise `WebOrderNo` would cause it first, right?) – Aaron Bertrand Mar 25 '22 at 22:15
  • https://stackoverflow.com/questions/4054511/what-exactly-does-the-t-sql-lineno-reserved-word-do – lptr Mar 25 '22 at 23:26
  • 3
    `LineNo` is a [Reserved Keyword](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql). You need to quote it, `[LineNo]`. You should be quoting `[Date]` too, since it's a system data type, but the parser usually lets you get away with it. – AlwaysLearning Mar 26 '22 at 00:11
  • Thank you Always Learning. That worked perfectly. I am new here. How do I mark this as the correct answer? – Pattie Mar 28 '22 at 15:20

0 Answers0