case end构成语法开始和结束
when 条件满足执行 then 否则执行 else
case
when ba.org is not null and ba.org = 'GD' then
'GD'
else
'OL'
end "ORDER_SOURCE"
decode(表达式,结果1,表达式/结果…)
decode函数第一个为先决条件,其他字段表示条件表达式或者结果,
select decode(1,2,3,4,5,1,7) r from dual;--返回值是7
decode(1,2,3,4) 返回值是4 没有满足的情况取默认值 4
decode(1,1,3,4) 返回值是3
decode(crk.risklevel,
1,
'C1',
2,
'C2',
3,
'C3',
4,
'C4',
5,
'C5',
'C1') "CUSTOMER_RISK_LEVEL"
where bsb.bankid = decode(t.tradechannel, 0, t.bankid, cc.bankid)
(+) 等同于 left join on , 如果没有(+) 会过滤掉匹配不上的数据, 这里只过滤掉 t表的,反之加了(+)就是加上 ba 表中没有的数据.已 t为主,返回t中所有记录
where t.serialno = ba.serno(+)
and t.custaccount = cc.custaccount(+)
and t.account = c.account
and t.issueid = i.issueid
按照执行格式转成日期类型.
to_date(bs.sysvalue, 'yyyymmdd')
将字段转存成数值类型,该函数会剔除字段值前面所有的0 例如 0000012358 to_number 之后 12358
to_number(rc.bancsno) "CUSTOMER_ID"
将时间类型字段按照格式转成字符型字段
to_char(t.tradedate, 'YYYYMMDD') "ESTABLISH_DATE"
判断字段值是否为空,是的话去候选字段,否则返回fp.price
nvl(fp.price, p.offerprice) "NAV"
TRUNC函数返回以指定元素格式截去一部分的日期值。
trunc(i.recorddate) >= trunc(to_date(bs.sysvalue, 'yyyymmdd'))
sign函数返回一个运算结果, 大于0 返回1, 小于0返回-1,等于0返回0
select sign(1012030123-12312412) from dual; -- 结果返回1
实际项目SQL代码
nvl(to_char(case
when t.attr = 0 and f.islockperiodissue != 1 and
t.trancode not in (0, 1, 2) then
null
else
decode(ba.fudate, null, t.tradedate, ba.fudate)
end,
'HHmmss'),
to_char(t.tradedate, 'hhmmss')) "ESTABLISH_TIME"
where t.serialno = ba.serno(+)
and t.custaccount = cc.custaccount(+)
and t.account = c.account
and t.issueid = i.issueid
and i.serialcode = f.serialcode(+)
and bs.syscode = 'sys.date'
and t.account = crk.account(+)
and bsb.bankid = decode(t.tradechannel, 0, t.bankid, cc.bankid)
and not ((t.trancode = 0 and t.state = 0 and
trunc(i.recorddate) >= trunc(to_date(bs.sysvalue, 'yyyymmdd')) and
i.state in (1, 4)) or
(t.trancode = 2 and t.payamtdate is not null and t.state = 0 and
trunc(t.payamtdate) <> trunc(to_date(bs.sysvalue, 'yyyymmdd'))))
decode(t.memo, '', '0000', '9999') "RETURN_CODE"