22,209
社区成员
发帖
与我相关
我的任务
分享
SELECT *
FROM OPENQUERY([ETEAMQC2], '
select * from SALES.V_PROD_DESC2 ') AS derivedtbl_1
WHERE (PART_NO in
(SELECT EBSITEM_NUMBER collate SQL_Latin1_General_CP1_CI_AS
FROM ProductCorrespond
WHERE (ProductID = 2736)))
4. SELECT * FROM OPENQUERY([ETEAMQC2] , 'select * from SALES.V_PROD_DESC2 WHERE Part_no = (''90NB0921-M00080'')')
declare @sql varchar(max)='';
if exists(SELECT 1 FROM ProductCorrespond WHERE (ProductID = 2736))
SELECT @sql=@sql+','''+EBSITEM_NUMBER collate SQL_Latin1_General_CP1_CI_AS +''''
FROM ProductCorrespond
WHERE (ProductID = 2736)
set @sql=STUFF(@sql,1,1,'')
set @sql='select * from SALES.V_PROD_DESC2 where PART_NO in ('+@sql+')'
set @sql='SELECT * FROM OPENQUERY([ETEAMQC2] , '''+replace(@sql,'''','''''')+''')'
select @sql
-- exec (@sql)
实际执行代码如下,提示错误信息:
is too long. Maximum length is 8000.
SELECT * FROM OPENQUERY([ETEAMQC2] , 'select * from SALES.V_PROD_DESC2 where PART_NO in (''VP-9NWTA320Y'',''VP-9NWTA321Y'',''VP-9NWTA322Y'',''VP-9NWTA323Y'',''VP-9NWTA324Y'',''VP-9NWTA325Y'',''VP-9NWTA326Y'',''VP-9NWTA327Y'',''VP-9NWTA328Y'',''VP-9NWTA329Y''.........)')
declare @sql varchar(max)='';
if exists(SELECT 1 FROM ProductCorrespond WHERE (ProductID = 2736))
begin
SELECT @sql=@sql+','''+EBSITEM_NUMBER collate SQL_Latin1_General_CP1_CI_AS +''''
FROM ProductCorrespond
WHERE (ProductID = 2736);
set @sql=STUFF(@sql,1,1,'');
set @sql='select * from SALES.V_PROD_DESC2 where PART_NO in ('+@sql+')' ;
SELECT *
FROM OPENQUERY([ETEAMQC2], @sql ) AS derivedtbl_1
end
大概意思就是这样,可能还有更高效的连接方式,需要调试。但你自己明白目的是把关联条件或者说是where条件传到oracle那么,所有工作干完了,再直接把结果传回来就行了。这样,只要优化oracle的语句就行了。