能在where中用case嗎?????
現在數據表xwp_out_quantity 的數據如下:
vendor item
220010 A90215R
220010 A90200R220020 A80216R
220020 A80201R220030 A70218R
220030 A70204R想把這表的數據分拆:
當vendor=220010 時,把item第二位為9的都顯示出來;
當vendor=220020 時,把item第二位為8的都顯示出來;
當vendor=220030 時,把item第二位為7的都顯示出來;
select
substr(xoq.item,2,5) model
from xwp_out_quantity xoq
where xoq.vendor='&s_vendor'
and
( case
when xoq.vendor='220010' then substr(xoq.item,2,1) in (9)
when xoq.vendor='220020' then substr(xoq.item,2,1) in (8)
when xoq.vendor='220030' then substr(xoq.item,2,1) in (7)運行後,PLSQL Developer報錯:
mission keyword哪位幫幫啊????????????
現在數據表xwp_out_quantity 的數據如下:
vendor item
220010 A90215R
220010 A90200R220020 A80216R
220020 A80201R220030 A70218R
220030 A70204R想把這表的數據分拆:
當vendor=220010 時,把item第二位為9的都顯示出來;
當vendor=220020 時,把item第二位為8的都顯示出來;
當vendor=220030 時,把item第二位為7的都顯示出來;
select
substr(xoq.item,2,5) model
from xwp_out_quantity xoq
where xoq.vendor='&s_vendor'
and
( case
when xoq.vendor='220010' then substr(xoq.item,2,1) in (9)
when xoq.vendor='220020' then substr(xoq.item,2,1) in (8)
when xoq.vendor='220030' then substr(xoq.item,2,1) in (7)運行後,PLSQL Developer報錯:
mission keyword哪位幫幫啊????????????
2 where substr(xoq.item,2,1) in (case xoq.vendor when '220010' then '9' when '220020' then '8' when '220030' then '7' end)
3 and xoq.vendor=220020;
VENDOR ITEM
-------------------- --------------------
220020 A80216R
220020 A80201R也可以使用decodeSQL> select * from xwp_out_quantity xoq
2 where substr(xoq.item,2,1) in decode(xoq.vendor,'220010','9','220020','8','220030','7','')
3 and xoq.vendor=220020;
VENDOR ITEM
-------------------- --------------------
220020 A80216R
220020 A80201R