SQL error ON clause

first100 81 Reputation points
2021-04-28T10:07:00.303+00:00

Helllo,
i try to execute this query but i have an error at the ON clause : 

 SELECT [itm].[OccurrenceId], [itm].[ContainerId], [itm].[StoreId]
 FROM [businessStore].[ItemAtStore] AS [itm]
 INNER JOIN [businessStore].[BoxItem] AS [itm.BoxItem] ON [itm].[ItemBoxId] = [itm.BoxItem].[Id]
 INNER JOIN [businessStore].[ObjectLastDetail] AS [ob] ON [itm].[ItemBoxId] = [ob].[ObjectId]
 INNER JOIN [businessStoreOut].[OutRef] ON ob.OutRefId = OutRef.Id
 INNER JOIN [businessStore].[ItemBoxType] AS [itm.BoxItem.ItemBoxType] ON [itm.BoxItem].[ItemBoxTypeId] = [itm.BoxItem.ItemBoxType].[Id]
 LEFT JOIN ([businessStore].[OccurenceQuality] AS [ea] ON [ob.OccurrenceId] = [ea].[OccurrenceId])  -- this on give me an error in Sql "Incorrect syntax near ON"
 INNER JOIN [businessStore].[Quality] AS [at] ON [at].[Id] = [ea].[QualityId]) ON ([ob].[OccurrenceId] = [ea].[OccurrenceId]) AND ([at].[Code] = 'NEW')
 WHERE [itm].[ContainerId] =  @ContainerId

Can someone help me?
Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,808 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,558 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 41,001 Reputation points
    2021-04-28T10:27:11.69+00:00

    AS [ea] ON [ob.OccurrenceId]

    Little typo, you placed the table alias "ob" inside the bracket, should be outside. 

    AS [ea] ON ob.[OccurrenceId]
    1 person found this answer helpful.
    0 comments
  2. EchoLiu-MSFT 14,571 Reputation points
    2021-04-29T02:08:17.753+00:00

    Hi @first100 ,

    Welcome to the microsoft TSQL Q&A forum!

    The error may be AS [ea] ON [ob.OccurrenceId] mentioned by Olaf.

    When the table name or field name contains spaces or SQL Server reserved words (such as [Name]), you need to use [].In other cases, it is not necessary to add [] to the field name or table name.

    For the method of adding [] to the table name or field name in TSQL, please refer to the following example: 

        SELECT * FROM t t1  
    JOIN t t2 on t1.name=t2.name  
      
    SELECT * FROM t t1  
    JOIN t t2 on t1.name=t2.[name]  
      
    SELECT * FROM t t1  
    JOIN t t2 on t1.name=[t2].[name]

    If you have any question, please feel free to let me know.

    Regards
    Echo

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments