Oracel SQL Note - decode / case when / distinct clob / RAC flow

select case when c1 = 0 then c2 else c1 end col from
( select 1 c1  from dual ) t1
,  ( select 2 c2 from dual ) t2;

 

--Sample2
select * from
(
 select case when cnt > alert_threshold then 1 else 0 end td_alert from
 ( SELECT COUNT(1) as cnt FROM link_ctrl where link_id like 'PR_' ) t1
 ,  ( select alert_threshold from monitor_ctrl ) t2
),
(
 select case when cnt > alert_threshold then 1 else 0 end ts_alert from
 ( SELECT COUNT(1) as cnt FROM link_ctrl where link_id like 'PR_' ) t1
 ,  ( select alert_threshold from monitor_ctrl ) t2
);

--Sample3
select
      case
           when HOUR = '09' then 'A1'
           when HOUR='11' then 'A2'
           when HOUR='13' then 'A3'
           when HOUR='15' then 'A4'
           when HOUR='17' then 'A5'
           when HOUR is null then msg_stas
           else 'Unknown'
      end DAY,
      msg_stas,
      cnt
from
(
select msg_stas, to_char(msg_tsm_dt, 'HH24') as HOUR, count(1) as cnt
from xxxua2.in_msg
where msg_cap_dt > to_date('04SEP 17:40','DDMON HH24:MI')
group by msg_stas,to_char(msg_tsm_dt, 'HH24')
order by msg_stas,to_char(msg_tsm_dt, 'HH24')
);

-- Alternative solution
select ctry_cde "Ctry code", msg_type "Message Type",
  decode(msg_stas,
    'AT', 'Accepted',
    'CL', 'Cancelled',
    'ER', 'Error',
    'HD', 'Hold',
    'PC', 'Pending approval - Cancel',
    'PR', 'Pending approval - Repair',
    'PT', 'Pending Transmit',
    'RD', 'Repaired',
    'TD', 'Transmitted',
    'WT', 'Waiting') "Status",
  count(*) "Count", to_char(sysdate,'yyyy-mm-dd HH24:MI:ss') "Fetched Datetime"
from xxx01pa1.out_msg
where 1 = 1
and last_udt_dt > to_date('2012-06-10 10:00:00','yyyy-mm-dd hh24:mi:ss')
and ctry_cde in ('SG','HK','TH','MY','BD','NZ')
group by ctry_cde,msg_type,msg_stas
order by ctry_cde,msg_type,msg_stas asc;

http://www.cnblogs.com/eshizhan/archive/2012/04/06/2435493.html 

? cannot distinct or unique on CLOB column type!!!
select unique (abc) from (
select substr(full_msg_txt , instr(full_msg_txt, '<d:FinInstrmId>') + length('<d:FinInstrmId>'), (instr(full_msg_txt,'</d:FinInstrmId>') - instr(full_msg_txt, '<d:FinInstrmId>')) - length('</d:FinInstrmId>') ) as abc from gdijpua2.in_msg_LISA
where  msg_cap_dt > (trunc(sysdate) -3) and msg_stas <> 'TD' and full_msg_txt not like '%JP3203500008%' /*and msg_id >= 5975112*/ order by msg_id desc
);

alternatively, you can use TO_CHAR() to resolve!

 

==