oracle数据库中decode函数的使用

语法

decode(条件，值1，返回值1，值2，返回值2，...值n,返回值n，缺省值) 函数含义

IF条件=值1THENRETURN(返回值1)ELSIF条件=值2THENRETURN(返回值2)......

ELSIF条件=值nTHENRETURN(返回值n)ELSE

RETURN(缺省值)ENDIF

注意，后⾯的缺省值是可以不写的。

select lb.contno,

decode(cont.conttype, '1', cont.appntno, '2', cont.insuredno),

decode(cont.conttype, '1', cont.appntname, '2', cont.insuredname),to_date(lb.create_time),

to_char(lb.sum_total, '9999999990.99'),to_char(lb.sum_price, '9999999990.99'),to_char(lb.sum_tax, '9999999990.99'),lb.sid,

lb.managecom

from LIS_BUSI_TRANSACTIONS lb, lccont contwhere cont.contno = lb.contnoand lb.invoiceflag in ('00')and lb.successflag = '1'and not exists

(select 1 from lcgrpcont c where c.grpcontno = lb.contno)and lb.sum_total > 0

and (cont.currency = '01' or cont.currency is null)and not exists (select 1

from ljagetendorse a, LIS_BUSI_TRANSACTIONS bwhere a.actugetno = b.sourceidand a.getflag = '1'

and b.ruleid in ('3', '6', '10', '12')and a.actugetno = lb.sourceidand b.sid = lb.sid)