有谁会把以下db2转换在oracle下?我搞了几天都没搞定啊
DECLARE SQLBODY VARCHAR2;--
CURSOR bill_task WITH RETURN TO CALLER FOR S1;--
SET SQLBODY='
SELECT A.IHTID,
A.IWZID,
C.CWZBH,
C.CWZMC,
C.CXHGG,
C.CJLDW,
C.CTH,
C.CCZ,
A.NHTDJ,
A.NHTSL,
A.IID,
--库存数量
(CASE WHEN COALESCE(D.NRKSL,0)-COALESCE(D.NCKSL,0) <0 THEN 0 ELSE COALESCE(D.NRKSL,0)-COALESCE(D.NCKSL,0) END ) NKCSL ,
--需退货数量=库存数量
(CASE WHEN COALESCE(D.NRKSL,0)-COALESCE(D.NCKSL,0) <0 THEN 0 ELSE COALESCE(D.NRKSL,0)-COALESCE(D.NCKSL,0) END ) NXTHSL ,
--库存金额
(CASE WHEN COALESCE(D.NRKJE,0)-COALESCE(D.NCKJE,0) <0 THEN 0 ELSE COALESCE(D.NRKJE,0)-COALESCE(D.NCKJE,0) END ) NKCJE,
B.CHTBH,
B.IGYSID,
D.IGYSKCID,
D.DZJRKRQ,
G.CGYSMC,
G.CGYSBH
FROM HTMX A LEFT JOIN GYSKCHTMX D ON D.IHTMXID=A.IID,
--LEFT JOIN HTZXQK F ON F.IHTMXID=A.IID,
HT B,
WZ C,
GYS G,
VALLYHWZPZ H,
GYSKC I
WHERE A.IHTID=B.IID
AND A.IWZID=C.IID
--AND A.IZZBZ=0
--AND B.IZZBZ=0--在新建退货单时,即时合同被终止,物资仍然显示。FY修改,2007-6-1
AND COALESCE(D.NRKSL,0)-COALESCE(D.NCKSL,0)>0--本地库存大于0,SBB,2007-8-20.
AND I.IID=D.IGYSKCID
AND B.IZT=3
AND B.ICBLX='||CAST(PICBLX AS CHAR(1))||'
AND B.IGYSID=G.IID';--
IF PIGYSID>0 THEN
SET SQLBODY=SQLBODY||' AND B.IGYSID='||CAST (PIGYSID AS CHAR(10));--
END IF;--
IF PICKID>0 THEN
SET SQLBODY=SQLBODY||' AND I.ICKID='||CAST(PICKID AS CHAR(10));--
END IF;--
SET SQLBODY=SQLBODY||'
AND H.IWZPZID=C.IWZPZID
AND C.IWZPZID=H.IWZPZID
AND LOCATE('''||PCWZBH||''',C.CWZBH)=1
AND IYHID='||CAST (PIYHID AS CHAR(10))||'
' ;--
--p2:begin
--declare mycur cursor WITH RETURN TO CALLER for
--select sqlbody from yh fetch first 1 rows only;--
PREPARE S1 FROM SQLBODY;--
OPEN bill_task;--
--open mycur;--
--end p2;--
END;