oracle case when then else end

来源:互联网 发布:专业校对软件 编辑:程序博客网 时间:2024/04/30 11:50
select subject_name,       to_number(subject_index) subject_index,       case         when data1 like '%年%' then          data1         else          to_char(round(nvl(data1,0), 2), '999999990.99')       end as data1,       case         when data2 like '%年%' then          data2         else          to_char(round(nvl(data2,0), 2), '999999990.99')       end as data2,       case         when data3 like '%年%' then          data3         else          to_char(round(nvl(data3,0), 2), '999999990.99')       end as data3  from (select tabs.fina_sub_id fina_sub_id,               fs.subject_name subject_name,               fs.subject_code,               fs.subject_index,               fs.financial_table,               tabs.datas,               REGEXP_SUBSTR(datas, '[^,]+', 1, 1, 'i') data1,               REGEXP_SUBSTR(datas, '[^,]+', 1, 2, 'i') data2,               REGEXP_SUBSTR(datas, '[^,]+', 1, 3, 'i') data3          from (select fina_sub_id, max(datas) datas                  from (SELECT cust_id,                               fina_sub_id,                               WMSYS.WM_CONCAT(subject_data) OVER(PARTITION BY fina_sub_id ORDER BY financial_date) datas,                               row_number() over(PARTITION BY fina_sub_id ORDER BY financial_date) rum                          from financial_data                         where cust_id = '4028808755e9357c0155fbc3fdb20628') tab                 group by fina_sub_id) tabs         inner join financial_subjects fs            on tabs.fina_sub_id = fs.id         order by fs.subject_index) finaltable order by subject_index

0 0