sql-server - t-sql语句语法错误

Question

需要有关t-sql语句语法错误的帮助。完整声明如下。
在sql-server 2005上使用sqlcmd。

-- oitm to oitg is a 1 to 1 link via the 64 matching columns
select g.[ItmsGrpNam], b.[ItmsGrpNam], count(1) 
from      oitg g
     join (select distinct 
           i.ItmsGrpCod as [ItmsGrpCod],
           i.QryGroup1 as [QryGroup1], 
           i.QryGroup2 as [QryGroup2] 
           from oitm i) p  -- pseudo intersect table for oitg-oitm links
     join oitb b  on b.[ItmsGrpCod] = p.[ItmsGrpCod]
where (g.ItmsGrpNam in  -- translate interect to oitg names
       (select x.ItmsGrpNam from oitg x where x.ItmsTypCod=1 and p.[QryGroup1]='Y') 
      )
group by g.ItmsGrpNam, b.ItmsGrpNam

到目前为止的发现。

select g.[ItmsGrpNam], b.[ItmsGrpNam], count(1) 
from      oitg g
     join (select distinct 
           i.ItmsGrpCod as [ItmsGrpCod],
           i.QryGroup1 as [QryGroup1], 
           i.QryGroup2 as [QryGroup2] 
           from oitm i) p  -- pseudo intersect table for oitg-oitm links
     join oitb b  on b.[ItmsGrpCod] = p.[ItmsGrpCod]

语句的这一部分给出以下错误。
消息102，级别15，状态1，服务器，第10行
'ItmsGrpCod'附近的语法不正确。

上面语句中的select将按原样返回。

3> select distinct
4>            i.ItmsGrpCod as [ItmsGrpCod],
5>            i.QryGroup1 as [QryGroup1],
6>            i.QryGroup2 as [QryGroup2]
7>            from oitm i
8> go
ItmsGrpCod QryGroup1 QryGroup2
---------- --------- ---------
       100 N         N
       101 N         Y
       102 N         Y
       103 N         Y
       104 N         Y
       105 N         Y
       106 N         Y
       107 N         N
       108 N         N
       108 Y         N
       110 N         Y
       111 N         N
       112 N         Y
       113 N         N

尝试用“内部联接”替换“联接”。已经开始用方括号括住列名。
ItmsGrpCod是oitm和oitb之间的fk，并且名称相同。 (在SAP Realm 中并非总是如此!)

对于那些感兴趣的人，这是我尝试围绕oitm的SAP B1设计缺陷进行编码(订购商品)
oitg(项目属性)没有相交表 hell 。

Answer 1

您第一次加入时缺少on语句。也许是这样的:

-- oitm to oitg is a 1 to 1 link via the 64 matching columns
select g.[ItmsGrpNam], b.[ItmsGrpNam], count(1) 
from      oitg g
     join (select distinct 
           i.ItmsGrpCod as [ItmsGrpCod],
           i.QryGroup1 as [QryGroup1], 
           i.QryGroup2 as [QryGroup2] 
           from oitm i) p  -- pseudo intersect table for oitg-oitm links
       on g.ItmsGrpCod = p.ItmsGrpCod
     join oitb b  on b.[ItmsGrpCod] = p.[ItmsGrpCod]
where (g.ItmsGrpNam in  -- translate interect to oitg names
       (select x.ItmsGrpNam from oitg x where x.ItmsTypCod=1 and p.[QryGroup1]='Y') 
      )
group by g.ItmsGrpNam, b.ItmsGrpNam