这段放到查询分析器里面语法就通不过,END后面多了个, select a.prdtcode,a.scode, case a.Comp when '1A' THEN (SELECT m1_lotqty1 from ps04 where m1_prdtcode =a.scode and m1_whouse='A') --when '2A' THEN (SELECT * FROM OPENQUERY([192.168.1.2], 'SELECT m1_lotqty1 FROM ZZ1..PS04 where m1_prdtcode =a.scode and m1_whouse=''A'' ' ) ) END from bm06expand a left outer join tb_customer c on c.cus_no = a.hgbm Left outer join pm03 d On d.p3_no = c.cus_unit and d.p3_kind = 'BG' where a.version='0' and a.prdtcode = '981D219-124 ' order by a.scode 你再试验下吧
case a.Comp when '1A' THEN (SELECT m1_lotqty1 from ps04 where m1_prdtcode =a.scode and m1_whouse='A') --when '2A' THEN (SELECT * FROM OPENQUERY([192.168.1.2], 'SELECT m1_lotqty1 FROM ZZ1..PS04 where m1_prdtcode =a.scode and m1_whouse=''A'' ' ) ) 这句有问题 这句应该最终表示的是一个字段 就好像 select a.prdtcode,a.scode, 中的a.prdtcode 和a.scode 都是一个字段条件为'1A'的时候 字段为SELECT m1_lotqty1 from ps04 where m1_prdtcode =a.scode and m1_whouse='A' 也就是 m1_lotqty1 没问题 但是当条件是2A 的时候 你的语句变成了 SELECT * FROM OPENQUERY([192.168.1.2], 'SELECT m1_lotqty1 FROM ZZ1..PS04 where m1_prdtcode =a.scode and m1_whouse=''A'' ' ) 这句自然就有问题了 select * 你查询出来是几个字段呢,怎么能放到一个字段里去呢?
但是当条件是2A 的时候 你的语句变成了 SELECT * FROM OPENQUERY([192.168.1.2], 'SELECT m1_lotqty1 FROM ZZ1..PS04 where m1_prdtcode =a.scode and m1_whouse=''A'' ' ) 这句自然就有问题了 select * 你查询出来是几个字段呢,怎么能放到一个字段里去呢? 不是这个的问题,因为OPENQUERY后面有个sql语句SELECT m1_lotqty1 FROM OPENQUERY([192.168.1.2], 'SELECT m1_lotqty1 FROM ZZ1..PS04 where m1_prdtcode =a.scode and m1_whouse=''A'' ' ) 同样不行
select a.prdtcode,a.scode,
case a.Comp
when '1A' THEN (SELECT m1_lotqty1 from ps04 where m1_prdtcode =a.scode and m1_whouse='A')
--when '2A' THEN (SELECT * FROM OPENQUERY([192.168.1.2], 'SELECT m1_lotqty1 FROM ZZ1..PS04 where m1_prdtcode =a.scode and m1_whouse=''A'' ' ) )
END
from bm06expand a left outer join tb_customer c on c.cus_no = a.hgbm
Left outer join pm03 d On d.p3_no = c.cus_unit and d.p3_kind = 'BG'
where a.version='0' and a.prdtcode = '981D219-124 '
order by a.scode
你再试验下吧
when '1A' THEN (SELECT m1_lotqty1 from ps04 where m1_prdtcode =a.scode and m1_whouse='A')
--when '2A' THEN (SELECT * FROM OPENQUERY([192.168.1.2], 'SELECT m1_lotqty1 FROM ZZ1..PS04 where m1_prdtcode =a.scode and m1_whouse=''A'' ' ) ) 这句有问题 这句应该最终表示的是一个字段 就好像
select a.prdtcode,a.scode, 中的a.prdtcode 和a.scode 都是一个字段条件为'1A'的时候 字段为SELECT m1_lotqty1 from ps04 where m1_prdtcode =a.scode and m1_whouse='A' 也就是 m1_lotqty1 没问题
但是当条件是2A 的时候 你的语句变成了
SELECT * FROM OPENQUERY([192.168.1.2], 'SELECT m1_lotqty1 FROM ZZ1..PS04 where m1_prdtcode =a.scode and m1_whouse=''A'' ' )
这句自然就有问题了 select * 你查询出来是几个字段呢,怎么能放到一个字段里去呢?
SELECT * FROM OPENQUERY([192.168.1.2], 'SELECT m1_lotqty1 FROM ZZ1..PS04 where m1_prdtcode =a.scode and m1_whouse=''A'' ' )
这句自然就有问题了 select * 你查询出来是几个字段呢,怎么能放到一个字段里去呢?
不是这个的问题,因为OPENQUERY后面有个sql语句SELECT m1_lotqty1 FROM OPENQUERY([192.168.1.2], 'SELECT m1_lotqty1 FROM ZZ1..PS04 where m1_prdtcode =a.scode and m1_whouse=''A'' ' ) 同样不行