下面是我原来用的sql,现在我想实现这么个功能,当A.INDI_CODE='4002301'时改为'0031001',当A.INDI_CODE='4002332'时改为'0065701',其余的不变,我试着用“case when then when then else end”来实现,但是没有获取到数据,可能是我对这个方法不熟悉吧,特此请教高手来帮忙!SELECT A.INDI_CODE AS indiCode,
A.INDI_NAME_CH AS indiNameCh,
A.DATETIME_CODE AS dateTimeCode,
C.DATETIME_NAME AS dateTimeName,
A.INDI_VALUE AS indiValue,
A.GRP1_ITEM_ID AS grpItemId,
A.GRP1_NAME_CH AS grpNameCh,
A.GRP1_ITEM_NAME_CH AS grpItemNameCh,
A.TIME_POINT_CODE AS timePointCode,
A.TIME_POINT_NAME AS timePointName,
C.QUARTER_NAME AS quarterNameFROM ME_DATA_INDI_VALUE_GRP A,ME_DES_INDICATOR B,ME_DES_DATATIME CWHERE A.INDI_CODE=B.INDI_CODE AND A.DATETIME_CODE=C.DATETIME_CODE
AND A.GRP1_ITEM_ID = ?
AND A.DATETIME_CODE = ?ORDER BY A.INDI_CODE,A.DATETIME_CODE,A.TIME_POINT_CODE
A.INDI_NAME_CH AS indiNameCh,
A.DATETIME_CODE AS dateTimeCode,
C.DATETIME_NAME AS dateTimeName,
A.INDI_VALUE AS indiValue,
A.GRP1_ITEM_ID AS grpItemId,
A.GRP1_NAME_CH AS grpNameCh,
A.GRP1_ITEM_NAME_CH AS grpItemNameCh,
A.TIME_POINT_CODE AS timePointCode,
A.TIME_POINT_NAME AS timePointName,
C.QUARTER_NAME AS quarterNameFROM ME_DATA_INDI_VALUE_GRP A,ME_DES_INDICATOR B,ME_DES_DATATIME CWHERE A.INDI_CODE=B.INDI_CODE AND A.DATETIME_CODE=C.DATETIME_CODE
AND A.GRP1_ITEM_ID = ?
AND A.DATETIME_CODE = ?ORDER BY A.INDI_CODE,A.DATETIME_CODE,A.TIME_POINT_CODE
A.INDI_NAME_CH AS indiNameCh,
A.DATETIME_CODE AS dateTimeCode,
C.DATETIME_NAME AS dateTimeName,
A.INDI_VALUE AS indiValue,
A.GRP1_ITEM_ID AS grpItemId,
A.GRP1_NAME_CH AS grpNameCh,
A.GRP1_ITEM_NAME_CH AS grpItemNameCh,
A.TIME_POINT_CODE AS timePointCode,
A.TIME_POINT_NAME AS timePointName,
C.QUARTER_NAME AS quarterNameFROM ME_DATA_INDI_VALUE_GRP A,ME_DES_INDICATOR B,ME_DES_DATATIME CWHERE A.INDI_CODE=B.INDI_CODE AND A.DATETIME_CODE=C.DATETIME_CODE
AND A.GRP1_ITEM_ID = ?
AND A.DATETIME_CODE = ?ORDER BY A.INDI_CODE,A.DATETIME_CODE,A.TIME_POINT_CODE
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO CASEEMPNOWHEN7369THEN'AHA,IT''
----- ---------- --------- ----- ----------- --------- --------- ------ ------------------------------
7369 SMITH CLERK 7902 1980-12-17 800.10 20 aha,it's you
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 not you
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 not you
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 not you
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 not you
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 not you
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 not you
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 not you
7839 KING PRESIDENT 1981-11-17 5000.00 10 not you
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 not you
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 not you
7900 JAMES CLERK 7698 1981-12-3 950.00 30 not you
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 not you
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 not you
14 rows selected
SQL>
这个SELECT (CASE A.INDI_CODE WHEN '4002301' THEN '0031001' WHEN '4002332' THEN '0065701' ELSE A.INDI_CODE END) AS indiCode,和这个:SELECT (CASE WHEN A.INDI_CODE='4002301' THEN '0031001' WHEN A.INDI_CODE='4002332' THEN '0065701' ELSE A.INDI_CODE END)AS indiCode,都没什么错误吧?
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO CASEWHENEMPNO=7369THEN'AHA,IT'
----- ---------- --------- ----- ----------- --------- --------- ------ ------------------------------
7369 SMITH CLERK 7902 1980-12-17 800.10 20 aha,it's you
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 not you
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 not you
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 not you
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 not you
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 not you
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 not you
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 not you
7839 KING PRESIDENT 1981-11-17 5000.00 10 not you
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 not you
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 not you
7900 JAMES CLERK 7698 1981-12-3 950.00 30 not you
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 not you
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 not you
14 rows selected
SQL>