你首先在SQLPLUS里面调试这条语句是不是正确,8I以后已经支持CASE语句了sql>SELECT JOB_NO,strCODE,strSEINO, SUM( CASE WHEN TAT_KBN='2' THEN EN_GKU ELSE 0 END CASE) AS KAZEI, SUM( CASE WHEN TAT_KBN='3' THEN EN_GKU ELSE 0 END) AS ZEIGI, SUM( CASE WHEN TAT_KBN='2' THEN EN_GKU ELSE 0 END)*0.05 AS SHOHI, SUM( CASE WHEN TAT_KBN='1' THEN EN_GKU ELSE 0 END) AS TATEK, (SUM( EN_GKU ) + SUM( CASE WHEN TAT_KBN='2' THEN EN_GKU ELSE 0 END)*0.05 AS EN_GAI, strUSER_ID,SYSDATE,strTERM_ID,strUSER_ID,SYSDATE,strTERM_ID FROM TZ_GYO_MEI GROUP BY JOB_NO; 不过我还是觉得decode使用比较方便
INSERT INTO TZ_GYOSYA
( JOB_NO, GYOSYA_CD, G_SEI_NO, KAZEI_GKU, ZEIGI_GKU,
SHOHI_GKU, TATEK_GKU, EN_GAI_GKU, ADD_USER_ID, ADD_DT,
ADD_TERM_ID, UPD_USER_ID, UPD_DT, UPD_TERM_ID )
SELECT JOB_NO,strCODE,strSEINO,
decode(TAT_KBN,'2',EN_GKU,0) KAZEI,
decode(TAT_KBN,'3',EN_GKU,0) ZEIGI,
decode(TAT_KBN,'2',EN_GKU,0) SHOHI,
decode(TAT_KBN,'1',EN_GKU,0) TATEK,
(SUM( EN_GKU ) + SUM( DECODE(TAT_KBN,'2',EN_GKU,0)*0.05 AS EN_GAI,
strUSER_ID,SYSDATE,strTERM_ID,strUSER_ID,SYSDATE,strTERM_ID
FROM TZ_GYO_MEI
GROUP BY JOB_NO;
EXCEPTION
WHEN OTHERS THEN
retCodeOut := -1 ;
retSqlCodeOut := SQLCODE ;
retSqlErrmOut := SQLERRM ;
retCharOut := 'PKG_HGYM0810.P_HGYM0810_CONNECT - ERR(001)' ;
RETURN ;
END;
____________________
BEGIN
INSERT INTO TZ_GYOSYA
( JOB_NO, GYOSYA_CD, G_SEI_NO, KAZEI_GKU, ZEIGI_GKU,
SHOHI_GKU, TATEK_GKU, EN_GAI_GKU, ADD_USER_ID, ADD_DT,
ADD_TERM_ID, UPD_USER_ID, UPD_DT, UPD_TERM_ID )
SELECT JOB_NO,strCODE,strSEINO,
decode(TAT_KBN,'2',EN_GKU,0) KAZEI,
decode(TAT_KBN,'3',EN_GKU,0) ZEIGI,
decode(TAT_KBN,'2',EN_GKU,0)*0.05 SHOHI,
decode(TAT_KBN,'1',EN_GKU,0) TATEK,
(SUM( EN_GKU ) + SUM( DECODE(TAT_KBN,'2',EN_GKU,0)*0.05 AS EN_GAI,
strUSER_ID,SYSDATE,strTERM_ID,strUSER_ID,SYSDATE,strTERM_ID
FROM TZ_GYO_MEI
GROUP BY JOB_NO;
EXCEPTION
WHEN OTHERS THEN
retCodeOut := -1 ;
retSqlCodeOut := SQLCODE ;
retSqlErrmOut := SQLERRM ;
retCharOut := 'PKG_HGYM0810.P_HGYM0810_CONNECT - ERR(001)' ;
RETURN ;
END;
=================================================================
修改如下:
(sum( EN_GKU) + sum( Decode (TAT_KBN,'2',EN_GKU,0) )*0.05) EN_GAI
--------------------------
BEGIN
INSERT INTO TZ_GYOSYA
( JOB_NO, GYOSYA_CD, G_SEI_NO, KAZEI_GKU, ZEIGI_GKU,
SHOHI_GKU, TATEK_GKU, EN_GAI_GKU, ADD_USER_ID, ADD_DT,
ADD_TERM_ID, UPD_USER_ID, UPD_DT, UPD_TERM_ID )
SELECT JOB_NO,strCODE,strSEINO,
decode(TAT_KBN,'2',EN_GKU,0) KAZEI,
decode(TAT_KBN,'3',EN_GKU,0) ZEIGI,
decode(TAT_KBN,'2',EN_GKU,0)*0.05 SHOHI,
decode(TAT_KBN,'1',EN_GKU,0) TATEK,
(sum( EN_GKU) + sum( Decode (TAT_KBN,'2',EN_GKU,0) )*0.05) EN_GAI
strUSER_ID,SYSDATE,strTERM_ID,strUSER_ID,SYSDATE,strTERM_ID
FROM TZ_GYO_MEI
GROUP BY JOB_NO;
EXCEPTION
WHEN OTHERS THEN
retCodeOut := -1 ;
retSqlCodeOut := SQLCODE ;
retSqlErrmOut := SQLERRM ;
retCharOut := 'PKG_HGYM0810.P_HGYM0810_CONNECT - ERR(001)' ;
RETURN ;
END;
SUM( CASE WHEN TAT_KBN='2' THEN EN_GKU ELSE 0 END CASE) AS KAZEI,
SUM( CASE WHEN TAT_KBN='3' THEN EN_GKU ELSE 0 END) AS ZEIGI,
SUM( CASE WHEN TAT_KBN='2' THEN EN_GKU ELSE 0 END)*0.05 AS SHOHI,
SUM( CASE WHEN TAT_KBN='1' THEN EN_GKU ELSE 0 END) AS TATEK,
(SUM( EN_GKU ) + SUM( CASE WHEN TAT_KBN='2' THEN EN_GKU ELSE 0 END)*0.05 AS EN_GAI,
strUSER_ID,SYSDATE,strTERM_ID,strUSER_ID,SYSDATE,strTERM_ID
FROM TZ_GYO_MEI
GROUP BY JOB_NO;
不过我还是觉得decode使用比较方便