question

01107111 avatar image
0 Votes"
01107111 asked EchoLiu-msft commented

SQL error ON clause

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-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Next time you post a question, include the error message you get. This time, it appears that Olaf spotted the error nevertheless.

0 Votes 0 ·

Have you tried the following methods? Could you have any updates?

Echo

0 Votes 0 ·
OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered

AS [ea] ON [ob.OccurrenceId]

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

 AS [ea] ON ob.[OccurrenceId]





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @01107111,

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.