SQL语句执行顺序问题

[wjf8882300]

执行下面的语句报类型转换错误，错误之处已用红色标记。以下错误

SELECT M.KPIID FROM TB_KPIS AS M
WHERE M.KPIID IN 
(
		SELECT A.KPIID FROM TB_KPIS AS A WHERE A.KPIFrequencyType=4--按天
		UNION
		SELECT B.KPIID FROM TB_KPIS AS B WHERE B.KPIFrequencyType=1 AND datediff(day,Convert(datetime,B.KPIFrequencyValue),getdate())=0--按年
		UNION
		SELECT C.KPIID FROM TB_KPIS AS C WHERE C.KPIFrequencyType=2 AND Convert(INT,C.KPIFrequencyValue)=day(getdate())--按月
		UNION
		SELECT D.KPIID FROM TB_KPIS AS D WHERE D.KPIFrequencyType=3 AND charindex(convert(varchar(10),datepart(weekday,getdate())),D.KPIFrequencyValue)!=0
)

但是如果我只执行其中的子句，则完全正确。以下正确

		SELECT A.KPIID FROM TB_KPIS AS A WHERE A.KPIFrequencyType=4--按天
		UNION
		SELECT B.KPIID FROM TB_KPIS AS B WHERE B.KPIFrequencyType=1 AND datediff(day,Convert(datetime,B.KPIFrequencyValue),getdate())=0--按年
		UNION
		SELECT C.KPIID FROM TB_KPIS AS C WHERE C.KPIFrequencyType=2 AND Convert(INT,C.KPIFrequencyValue)=day(getdate())--按月
		UNION
		SELECT D.KPIID FROM TB_KPIS AS D WHERE D.KPIFrequencyType=3 AND charindex(convert(varchar(10),datepart(weekday,getdate())),D.KPIFrequencyValue)!=0

按理说要等我子句执行完，才会去执行外层语句。而单独执行子语句是正确的，但外面加一层之后就出错。不知道原因出在哪里？

[--小F--]

难道有不是时间字段的字符存在?

[水族杰纶]

應該是數據的問題,語句本身沒有問題
B.KPIFrequencyValue 是不是有非日期的數據

[让你望见影子的墙]

B.KPIFrequencyValue

是什么类型？

[水族杰纶]

--try
SELECT M.KPIID FROM TB_KPIS AS M
WHERE M.KPIID IN 
(
        SELECT A.KPIID FROM TB_KPIS AS A WHERE A.KPIFrequencyType=4--按天
        UNION
        SELECT B.KPIID FROM TB_KPIS AS B WHERE B.KPIFrequencyType=1 and isdate(B.KPIFrequencyValue)=1 AND datediff(day,Convert(datetime,B.KPIFrequencyValue),getdate())=0--按年
        UNION
        SELECT C.KPIID FROM TB_KPIS AS C WHERE C.KPIFrequencyType=2 AND Convert(INT,C.KPIFrequencyValue)=day(getdate())--按月
        UNION
        SELECT D.KPIID FROM TB_KPIS AS D WHERE D.KPIFrequencyType=3 AND charindex(convert(varchar(10),datepart(weekday,getdate())),D.KPIFrequencyValue)!=0
)

[guguda2008]

;WITH MU AS (
       SELECT A.KPIID FROM TB_KPIS AS A WHERE A.KPIFrequencyType=4--按天
        UNION
        SELECT B.KPIID FROM TB_KPIS AS B WHERE B.KPIFrequencyType=1 AND datediff(day,Convert(datetime,B.KPIFrequencyValue),getdate())=0--按年
        UNION
        SELECT C.KPIID FROM TB_KPIS AS C WHERE C.KPIFrequencyType=2 AND Convert(INT,C.KPIFrequencyValue)=day(getdate())--按月
        UNION
        SELECT D.KPIID FROM TB_KPIS AS D WHERE D.KPIFrequencyType=3 AND charindex(convert(varchar(10),datepart(weekday,getdate())),D.KPIFrequencyValue)!=0
)


SELECT M.KPIID FROM TB_KPIS AS M
WHERE M.KPIID IN (SELECT KPIID FROM MU)

这样写试试

[wjf8882300]

[Quote=引用 4 楼 wufeng4552 的回复:]
SQL code--trySELECT M.KPIIDFROM TB_KPISAS MWHERE M.KPIIDIN
(SELECT A.KPIIDFROM TB_KPISAS AWHERE A.KPIFrequencyType=4--按天UNIONSELECT B.KPIIDFROM TB_KPISAS BWHERE B.KPIFrequencyType=1andisdate(B.KPIFre?-
[/Quote]
你说了很有道理，问题基本解决了。但是Convert(INT,C.KPIFrequencyValue)时又报个错，问题是C.KPIFrequencyValue可能不是整数，问怎么判断C.KPIFrequencyValue是整数。我用ISNUMERIC(C.KPIFrequencyValue)好像不行。请教高手！

[wjf8882300]

问题解决，用的是临时表