今天碰到同样的SQL,只是连接DB的方式不同而造成以下语句不能正常执行。
/**/
/* Formatted on 2007/06/06 10:24 (Formatter Plus v4.8.7) */
SELECT ID, LEVELS, NVL (NAME, ' ゼ㏑ ' ) NAME, url, style, owner, seq,
( CASE LEVELS
WHEN ' B '
THEN SUBSTR (ID, 1 , 2 )
ELSE SUBSTR (ID, 3 , 2 )
END
) AS PARENT,
ATTRIBUTE
FROM a4rsys_menu
WHERE SUBSTR (ID, 1 , 2 ) = ' 18 '
AND (LEVELS = ' B ' OR (LEVELS = ' C ' AND ATTRIBUTE NOT IN ( ' D ' , ' T ' )))
ORDER BY LEVELS, PARENT, seq, SUBSTR (style, 1 , 1 )
SELECT ID, LEVELS, NVL (NAME, ' ゼ㏑ ' ) NAME, url, style, owner, seq,
( CASE LEVELS
WHEN ' B '
THEN SUBSTR (ID, 1 , 2 )
ELSE SUBSTR (ID, 3 , 2 )
END
) AS PARENT,
ATTRIBUTE
FROM a4rsys_menu
WHERE SUBSTR (ID, 1 , 2 ) = ' 18 '
AND (LEVELS = ' B ' OR (LEVELS = ' C ' AND ATTRIBUTE NOT IN ( ' D ' , ' T ' )))
ORDER BY LEVELS, PARENT, seq, SUBSTR (style, 1 , 1 )
连接DB方式:
成功:Application("SYSDB") = "Provider=MSDAORA.1;User ID=MCSAP1;Password=MCSAP1;data source=PNBRS"
失败:Application("MADB") = "Provider=OraOLEDB.Oracle.1;User ID=MASAP1;Password=MASAP1;data source=PNBRS"
总结:
OleDb 方式不支持 CASE When Function