oracle 多条件处理以及多字段拼接
SELECT to_char(DI.ETD, 'yyyy-MM-dd HH:mm:ss') as ETD,
to_char(DI.ETD, 'MM') as ETDMONTH,
DI.VESSEL_CODE,
DI.VOYAGE_CODE,
DI.DI_NO,
DI.DISCHARGE_PORT,
main.PORT_NAME,
to_char(DI.DIST_PORT_ETA, 'yyyy-MM-dd HH:mm:ss') as DIST_PORT_ETA,
DI.LOT_CONDITION,
DI.VIN,
DI.MODEL,
DI.TYPE,
DI.OP,
DI.HES_COLOR,
DI.INTERIOR_COLOR,
DI.MTOC_QTY,
DI.CASE_NO,
decode(DI.UPDATE_FLAG,0,'无变化',1,'UPDATE',2,'DELETE',3,'ADD') as UPDATE_FLAG,
to_char(DI.ORDER_DATE, 'yyyy-MM-dd HH:mm:ss') as ORDER_DATE,
carTYpe.CAR_CODE,
carTYpe.CAR_NAME,
ve.KEY_NO,
ve.ENGINE_NO,
to_char(DI.ETD,'yyyy-MM-dd') as LOAD_FIN_DATE,
ve.CURRENT_STATUS,
look.LOOKUP_VALUE_NAME,
CONCAT(RPAD(DI.MODEL, 11, ' '), CONCAT(RPAD(DI.TYPE, 4, ' '), CONCAT(RPAD(DI.OP, 3, ' '), CONCAT(
RPAD(DI.HES_COLOR, 8, ' '), RPAD(DI.INTERIOR_COLOR, 2, ' '))))) as MTOC
FROM T_EPS_BU_DI_DETAIL DI
decode()函数用法
例:decode(DI.UPDATE_FLAG,0,'无变化',1,'UPDATE',2,'DELETE',3,'ADD') as UPDATE_FLAG,
解析如下:
当DI.UPDATE_FLAG为0时赋值无变化;
当DI.UPDATE_FLAG为1时赋值UPDATE;
当DI.UPDATE_FLAG为2时赋值DELETE;
当DI.UPDATE_FLAG为3是赋值ADD
RPAD()函数用法
CONCAT(RPAD(DI.MODEL, 11, ' '), CONCAT(RPAD(DI.TYPE, 4, ' '), CONCAT(RPAD(DI.OP, 3, ' '), CONCAT(
RPAD(DI.HES_COLOR, 8, ' '), RPAD(DI.INTERIOR_COLOR, 2, ' '))))) as MTOC
解析如下:
将以上model、type、op...等五个字段拼接在一起,切各字段有指定长度,长度不够的通过 空格 补全。