【Teradata SQL】一个字段为空即取另外一个字段（连续取4个字段）-case when

目标：如果col1为空则取col2的值，如果col2也为空则取col3的值，如果col3还为则取col4的值，如果四个字段均为空则取默认值

1.数据准备

create multiset table test1 
(
col_type varchar(20),
col1 varchar(10),
col2 varchar(20),
col3 varchar(30),
col4 varchar(10)
)no primary index;

insert into test1 values ('fetch_col1','col1','col2','col3','clo4'); --字段均有值
insert into test1 values ('fetch_col1','col1','','col3','clo4');--col2无值
insert into test1 values ('fetch_col1','col1','col2','','clo4');--col3无值
insert into test1 values ('fetch_col1','col1','col2','col3','');--col4无值

insert into test1 values ('fetch_col2','','col2','col3','clo4');--col1无值
insert into test1 values ('fetch_col2','','col2','','clo4');--col3无值
insert into test1 values ('fetch_col2','','col2','col3','');--col4无值
insert into test1 values ('fetch_col2','','col2','','');--仅col2有值，其余均无值

insert into test1 values ('fetch_col3','','','col3','clo4');--col1和col2无值
insert into test1 values ('fetch_col3','','','col3','');--仅col3有值，其余均无值

insert into test1 values ('fetch_col4','','','','clo4');--仅col4有值，其余均无值

insert into test1 values ('fetch_default','','','','');--全部无值

2.查询结果

sel 
col_type,
case 
   when col1 <>'' then col1 
   when col2 <> '' then col2
   when col3 <>'' then col3
   when col4 <>'' then col4
   else 'default'
end fetch_value
,col1,col2,col3,col4
from test1 order by 3 desc,4 desc,5 desc,6 desc;

 

转载于:https://www.cnblogs.com/badboy200800/p/10791305.html