能在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哪位幫幫啊????????????
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) else null end)=9
or (case when xoq.vendor='220020' then substr(xoq.item,2,1) else null end)=8
or (case when xoq.vendor='220030' then substr(xoq.item,2,1) else null end)=7
substr(xoq.item,2,5) model
from xwp_out_quantity xoq
where xoq.vendor='&s_vendor'
and ( ( xoq.vendor='220010' and substr(xoq.item,2,1)='7' )
or ( xoq.vendor='220020' and substr(xoq.item,2,1)='8' )
or ( xoq.vendor='220030' and substr(xoq.item,2,1)='9' )
);-- 注意数值打上引号,用等于(=),别用 in(7) 的格式!
-- 从你写的这个SQL语句,可以看出:你的SQL功底不是一般的差!
substr(xoq.item,2,5) model
from xwp_out_quantity xoq
where xoq.vendor='&s_vendor'
and substr(xoq.item,2,1) = decode( xoq.vendor,'220010','9','220020','8','220030','7')