执行下面的语句报类型转换错误,错误之处已用红色标记。以下错误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
按理说要等我子句执行完,才会去执行外层语句。而单独执行子语句是正确的,但外面加一层之后就出错。不知道原因出在哪里?
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
按理说要等我子句执行完,才会去执行外层语句。而单独执行子语句是正确的,但外面加一层之后就出错。不知道原因出在哪里?
B.KPIFrequencyValue 是不是有非日期的數據
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
)
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)这样写试试