case when 和 coalesce

题目：
        求一语句实现以下功能，有COL1 ，COL2，COL3，COL4，COL5，COL6，COL7，及ST_SIZETEMPLATECODE.
如ST_COL1，ST_COL7的值为1.2.3.4.5.6.7则
ST_SIZETEMPLATECODE为1—7，如 ST_COL7为空，则ST_SIZETEMPLATECODE为1—6，简言之就是只要头尾两个值

数据：

COL1	COL2	COL3	COL4	COL5	COL6	COL7
1	s	d	e	f	s
a	d	f	e	w	q	h
r	w	r	q	i	i	p
a	d
a	s	d	f	f

要求结果：

ST_SIZETEMPLATECODE

1-s

a-h

r-p

a-d

a-f

答案：

【1】
select
col1 || '-' ||
case when col7 is not null then col7
when col6 is not null then col6
when col5 is not null then col5
when col4 is not null then col4
when col3 is not null then col3
when col2 is not null then col2
when col1 is not null then col1 end temp
from temp070524

【2】
SELECT
COL1 || '-' || COALESCE(COL7,COL6,COL5,COL4,COL3,COL2)
FROM TBL0524

『注意』
user@8i> create table t (id int, f blob)
2 /

Table created.

user@8i> insert into t (id) values (1)
2 /

1 row created.

user@8i> select
2      id
3      ,dbms_lob.getlength(f)
4      ,nvl(dbms_lob.getlength(f),0)
5 from t
6 /

        ID DBMS_LOB.GETLENGTH(F) NVL(DBMS_LOB.GETLENGTH(F),0)
---------- --------------------- ----------------------------
         1                                                  0

user@8i> select
2      id
3      ,dbms_lob.getlength(f)
4      ,coalesce(dbms_lob.getlength(f),0)
5 from t
6 /
    ,coalesce(dbms_lob.getlength(f),0)
     *
ERROR at line 4:
ORA-00904: invalid column name

user@9i> create table t (id int, f blob)
2 /

Table created.

user@9i> insert into t (id) values (1)
2 /

1 row created.

user@9i> select
2      id
3      ,dbms_lob.getlength(f)
4      ,nvl(dbms_lob.getlength(f),0)
5 from t
6 /

        ID DBMS_LOB.GETLENGTH(F) NVL(DBMS_LOB.GETLENGTH(F),0)
---------- --------------------- ----------------------------
         1                                                  0

user@9i> select
2      id
3      ,dbms_lob.getlength(f)
4      ,coalesce(dbms_lob.getlength(f),0)
5 from t
6 /

        ID DBMS_LOB.GETLENGTH(F) COALESCE(DBMS_LOB.GETLENGTH(F),0)
---------- --------------------- ---------------------------------
         1                                                       0