SELECT A2.RELITEM,D.DATEFLAG,B.PRJID
FROM TBM_ACTIVITY A2,TBM_PRJDATA D,TBM_PRJACTIVITY B,BAS_DPT D1,BAS_DPT D2
WHERE D.OBJECTID = B.ACTID AND B.PRJID=D.PRJID
AND D.DATEFLAG BETWEEN '201113' AND '201116' AND D.DATATYPE=1 AND D2.DPTID=INT(RTRIM(CHAR(1)))
AND B.ITEMID = 4164 AND D1.DPTID=INT(B.ITEMVALUE)
AND SUBSTR(D1.VIEWORDER,1,LENGTH(D2.VIEWORDER) )=(D2.VIEWORDER)
AND D.PRJID IN(SELECT A.PRJID FROM TBM_PRJLIST A,TBM_PRJLIST B WHERE A.PRJID=B.PRJID AND
B.ITEMID=4100 AND B.ITEMVALUE='0' AND A.ITEMID = 4103 AND A.ITEMVALUE IN (RTRIM('3')))
AND B.ACTID IN(SELECT ACTID FROM TBM_PRJACTIVITY WHERE ITEMID=4133 AND A2.ACTID=INT(ITEMVALUE))
AND D.ITEMID<>0
bas_dpt表中,dptid=1的部门vieworder的长度为2,把其中LENGTH(D2.VIEWORDER)换成2也报错,换成LENGTH(D2.VIEWORDER)-1或者1就没错了
这样改一下试试:
SUBSTR(D1.VIEWORDER,1,LENGTH(D2.VIEWORDER) )=(D2.VIEWORDER)
改为:
D1.VIEWORDER like D2.VIEWORDER + '%'
但这样可能效率不高