客户正式账套从SQL200升级到SQL2008后,以前正常查询的一段SQL,升级后查询数据有误。
如下面示例,传入一个指定的客户,其它条件全部为空即%,但在执行查询时,明明有记录关联不到So_somain表,但实际上却被查询出来了,我如果将AND (@cInvCode1 = '%')或AND (@sMoCode = '%' OR t1.cMoNo = @sMoCode)或AND (@cBatch1 = '%' OR t1.cBatchNo LIKE @cBatch1 )任意一个条件屏蔽掉,查询时又会过滤那些关联不到So_somain的数据,怎么会这么怪?(@cInvCode1、@sMoCode 、(@cBatch1 这几个条件明明就是%,为什么一放出来查询又会不一样?当然将上面的左关联改成inner join 也没问题。想了一个上午也没想明白!兄弟们帮帮忙,实在是急。
DECLARE @sMoCode VARCHAR(20), --生产订单
@iSortSeq INT, --生产订单工序号
@sSoCode VARCHAR(20), --销售订单
@cInvCode1 VARCHAR(20), --存货编号(始)
@cBatch1 VARCHAR(40), --批号(始)
@cCusCode VARCHAR(20) --客户 SELECT @cCusCode = '00184'IF ISNULL(@sMoCode,'') = ''
SET @sMoCode = '%'IF ISNULL(@sSoCode,'') = ''
SET @sSoCode = '%'IF ISNULL(@cInvCode1,'') = ''
SET @cInvCode1 = '%'IF @cBatch1 IS NULL
SET @cBatch1 = '%'
SET @cBatch1 = @cBatch1 + '%'IF ISNULL(@cCusCode,'') = ''
SET @cCusCode = '%'SELECT @cInvCode1 as '@cInvCode1',@iSortSeq as '@iSortSeq',@cCusCode as '@cCusCode',@sMoCode as '@sMoCode',t1.cdispno,t1.ilineid,t1.modid,t9.cCusCode,t8.OrderType,t8.OrderCode,t9.cSoCode,t3.cInvDefine1
FROM ZZ_MoSeqDisps t1 JOIN ZZ_MoSeqDisp t2 ON t1.cDispNo = t2.cDispNo
JOIN Inventory t3 ON t1.cInvCode = t3.cInvCode
JOIN mom_orderdetail t8 ON t1.MoDId = t8.MoDId
LEFT JOIN So_somain t9 ON t8.OrderType = 1 AND t8.OrderCode = t9.cSoCode
WHERE t1.bComplete = 0
AND t8.Status = 3
AND t1.iSourceType IN (3,5)
AND (@sMoCode = '%' OR t1.cMoNo = @sMoCode)
AND (@cBatch1 = '%' OR t1.cBatchNo LIKE @cBatch1 )
AND (@sSoCode = '%' OR t8.OrderCode = @sSoCode)
AND (@cCusCode = '%' OR (t9.cCusCode = @cCusCode))
--AND ( t9.cCusCode Like @cCusCode + '%')
AND (@cInvCode1 = '%')
如下面示例,传入一个指定的客户,其它条件全部为空即%,但在执行查询时,明明有记录关联不到So_somain表,但实际上却被查询出来了,我如果将AND (@cInvCode1 = '%')或AND (@sMoCode = '%' OR t1.cMoNo = @sMoCode)或AND (@cBatch1 = '%' OR t1.cBatchNo LIKE @cBatch1 )任意一个条件屏蔽掉,查询时又会过滤那些关联不到So_somain的数据,怎么会这么怪?(@cInvCode1、@sMoCode 、(@cBatch1 这几个条件明明就是%,为什么一放出来查询又会不一样?当然将上面的左关联改成inner join 也没问题。想了一个上午也没想明白!兄弟们帮帮忙,实在是急。
DECLARE @sMoCode VARCHAR(20), --生产订单
@iSortSeq INT, --生产订单工序号
@sSoCode VARCHAR(20), --销售订单
@cInvCode1 VARCHAR(20), --存货编号(始)
@cBatch1 VARCHAR(40), --批号(始)
@cCusCode VARCHAR(20) --客户 SELECT @cCusCode = '00184'IF ISNULL(@sMoCode,'') = ''
SET @sMoCode = '%'IF ISNULL(@sSoCode,'') = ''
SET @sSoCode = '%'IF ISNULL(@cInvCode1,'') = ''
SET @cInvCode1 = '%'IF @cBatch1 IS NULL
SET @cBatch1 = '%'
SET @cBatch1 = @cBatch1 + '%'IF ISNULL(@cCusCode,'') = ''
SET @cCusCode = '%'SELECT @cInvCode1 as '@cInvCode1',@iSortSeq as '@iSortSeq',@cCusCode as '@cCusCode',@sMoCode as '@sMoCode',t1.cdispno,t1.ilineid,t1.modid,t9.cCusCode,t8.OrderType,t8.OrderCode,t9.cSoCode,t3.cInvDefine1
FROM ZZ_MoSeqDisps t1 JOIN ZZ_MoSeqDisp t2 ON t1.cDispNo = t2.cDispNo
JOIN Inventory t3 ON t1.cInvCode = t3.cInvCode
JOIN mom_orderdetail t8 ON t1.MoDId = t8.MoDId
LEFT JOIN So_somain t9 ON t8.OrderType = 1 AND t8.OrderCode = t9.cSoCode
WHERE t1.bComplete = 0
AND t8.Status = 3
AND t1.iSourceType IN (3,5)
AND (@sMoCode = '%' OR t1.cMoNo = @sMoCode)
AND (@cBatch1 = '%' OR t1.cBatchNo LIKE @cBatch1 )
AND (@sSoCode = '%' OR t8.OrderCode = @sSoCode)
AND (@cCusCode = '%' OR (t9.cCusCode = @cCusCode))
--AND ( t9.cCusCode Like @cCusCode + '%')
AND (@cInvCode1 = '%')
解决方案 »
- 请教:同一张表中是不是只能存在一个bit类型非并带默认值的字段,有两个或者两个以上时当会受影响。
- 求如何画ER图以及如何转化ER图的资料(在线等待)
- 关于存储过程的问题
- 关于sqlserver2000 英文标准版
- 续 求出最近三天的日期。。。。。。。。
- 简单问题:用"select * into 新表 from 表1 "语句,怎么让生成表和“新表”表结构完全一样呢?
- 如何在存储过程中根据传入的参数(一条查询语句)将结果集导出到一个自动生成的EXCEL文件中??
- 不同数据库服务器间表间取数--在线等
- 动态SQL的返回值??
- 用 SQL Server 做 MIS/ERP 的朋友请进. 关于超大数据量 !
- sql2005 取得团队组织架构(有关树的问题)
- 日期转换
SELECT @cInvCode1 as '@cInvCode1',@iSortSeq as '@iSortSeq',@cCusCode as '@cCusCode',@sMoCode as '@sMoCode',t1.cdispno,t1.ilineid,t1.modid,t9.cCusCode,t8.OrderType,t8.OrderCode,t9.cSoCode,t3.cInvDefine1
FROM ZZ_MoSeqDisps t1 JOIN ZZ_MoSeqDisp t2 ON t1.cDispNo = t2.cDispNo
JOIN Inventory t3 ON t1.cInvCode = t3.cInvCode
JOIN mom_orderdetail t8 ON t1.MoDId = t8.MoDId
LEFT JOIN So_somain t9 ON t8.OrderType = 1 AND t8.OrderCode = t9.cSoCode
WHERE t1.bComplete = 0
AND t8.Status = 3
AND t1.iSourceType IN (3,5)
AND (@sMoCode = '%' OR t1.cMoNo = @sMoCode)
AND (@cBatch1 = '%' OR t1.cBatchNo LIKE @cBatch1 )
AND (@sSoCode = '%' OR t8.OrderCode = @sSoCode)
AND (@cCusCode = '%' OR (t9.cCusCode = @cCusCode))
--AND ( t9.cCusCode Like @cCusCode + '%')
AND ('%' = '%')