please refine the design of tables and recreate your index. if it is not possible ,just recreate the statement as below: (if there is any syntax error or obvoius logical mistake,please correct for me. i am just telling no more than my solution)------------ SELECT A.GJLDATE GJLDATE, A.GJLGDID GJLGDID, GOODSBASE.GBCNAME, GOODSBASE.GBBARCODE, GOODSPRICE.GPHSJJ, GOODSPRICE.GPSJ, SUM(A.HB) AS JHSL, -1*SUM(A.HB) AS XSSL, SUM(B.GJLQMSL) KCSL FROM (SELECT A.GJLDATE GJLDATE, A.GJLGDID GJLGDID, DECODE(A.GJLTRAN,'1',A.GJLSL,'2',A.GJLSL,'3',A.GJLSL,'4',A.GJLSL,'5',A.GJLSL,'6',A.GJLSL,0) as hb FROM GOODSJXCLIST A where A.GJLMFID = '2020099' AND ( A.GJLDATE >= TO_DATE('2002-10-01','YYYY-MM-DD') AND A.GJLDATE <= TO_DATE('2002-10-13','YYYY-MM-DD')) ) A, (SELECT GJLGDID,GJLDATE,GJLQMSL FROM GOODSJXCLIST WHERE GJLSEQ=(SELECT MAX(GJLSEQ) FROM GOODSJXCLIST GROUP BY GJLDATE,GJLGDID) ) B, GOODSPRICE,GOODSBASE WHERE A.GJLGDID=GOODSPRICE.GPGDID AND A.GJLGDID=B.GJLGDID AND A.GJLDATE=B.GJLDATE AND A.GJLGDID=GOODSBASE.GBID GROUP BY A.GJLDATE, A.GJLGDID, GOODSBASE.GBCNAME, GOODSBASE.GBBARCODE, GOODSPRICE.GPHSJJ, GOODSPRICE.GPSJ ORDER BY A.GJLDATE
if it is not possible ,just recreate the statement as below:
(if there is any syntax error or obvoius logical mistake,please
correct for me. i am just telling no more than my solution)------------
SELECT A.GJLDATE GJLDATE,
A.GJLGDID GJLGDID,
GOODSBASE.GBCNAME,
GOODSBASE.GBBARCODE,
GOODSPRICE.GPHSJJ,
GOODSPRICE.GPSJ,
SUM(A.HB) AS JHSL,
-1*SUM(A.HB) AS XSSL,
SUM(B.GJLQMSL) KCSL
FROM
(SELECT A.GJLDATE GJLDATE,
A.GJLGDID GJLGDID,
DECODE(A.GJLTRAN,'1',A.GJLSL,'2',A.GJLSL,'3',A.GJLSL,'4',A.GJLSL,'5',A.GJLSL,'6',A.GJLSL,0) as hb
FROM GOODSJXCLIST A
where A.GJLMFID = '2020099' AND
( A.GJLDATE >= TO_DATE('2002-10-01','YYYY-MM-DD') AND A.GJLDATE <= TO_DATE('2002-10-13','YYYY-MM-DD'))
) A,
(SELECT GJLGDID,GJLDATE,GJLQMSL FROM GOODSJXCLIST WHERE GJLSEQ=(SELECT MAX(GJLSEQ) FROM GOODSJXCLIST GROUP BY GJLDATE,GJLGDID) ) B,
GOODSPRICE,GOODSBASE
WHERE A.GJLGDID=GOODSPRICE.GPGDID AND
A.GJLGDID=B.GJLGDID AND
A.GJLDATE=B.GJLDATE AND
A.GJLGDID=GOODSBASE.GBID
GROUP BY A.GJLDATE,
A.GJLGDID,
GOODSBASE.GBCNAME,
GOODSBASE.GBBARCODE,
GOODSPRICE.GPHSJJ,
GOODSPRICE.GPSJ
ORDER BY A.GJLDATE