SQLPlus Case statement on a substring

Applications Programming
1

Hi,

I have an SQL query that returns a substring of a field and I want to work a case statement on it, but either I get a syntax error or in it's current manifestation the case is ignored all together.

 select distinct dbms_lob.substr(msg_body,1,4) as networkId,
 A_STATUS, count(*) from A_MESSAGE_LOG where CREATION_TIME like '16-OCT-12%'
 group by dbms_lob.substr(msg_body,1,4), A_STATUS order by dbms_lob.substr(msg_body,1,4),
 case networkId when 'A' || 'B' || 'C' || 'E' then 'BILL'
 when 'F' then 'BOB'
 when 'G' then 'JOHN'
 when 'H' then 'TOM'
 when 'I' then 'NIGEL'
 end

Any ideas?

---------- Post updated at 02:55 PM ---------- Previous update was at 01:42 PM ----------

I've also tried:

 select distinct dbms_lob.substr(msg_body,1,4) as networkId,
 A_STATUS, count(*) from A_MESSAGE_LOG where CREATION_TIME like '16-OCT-12%'
 group by dbms_lob.substr(msg_body,1,4), A_STATUS order by dbms_lob.substr(msg_body,1,4),
 case when networkId in ('A','B','C','E') then 'BILL'
 when networkId ='F' then 'BOB'
 when networkId ='G' then 'JOHN'
 when networkId ='H' then 'TOM'
 when networkId ='I' then 'NIGEL'
 end

and get

NETWORKID
------------------------------------------------------------------------------------------------------------------------------------
W   COUNT(*)
- ----------
B
U         90
B
C       7130
B
T          5
C
C       1210
C
U         17
F
C       1518
F
O        252
G
C       3878
H
C          2
 
9 rows selected.

---------- Post updated at 03:29 PM ---------- Previous update was at 02:55 PM ----------

select distinct (case when dbms_lob.substr(msg_body,1,4) in ('A','B','C','E') then 'BILL'
when dbms_lob.substr(msg_body,1,4) ='F' then 'BOB'
when dbms_lob.substr(msg_body,1,4) ='G' then 'JOHN'
when dbms_lob.substr(msg_body,1,4) ='H' then 'TOM'
when dbms_lob.substr(msg_body,1,4) ='I' then 'NIGEL'
end) as NetworkId,
A_STATUS, count(*) from A_MESSAGE_LOG
where CREATION_TIME like '16-OCT-12%'
group by dbms_lob.substr(msg_body,1,4), A_STATUS
order by NetworkId

2

Try:-

select 
case 
when dbms_lob.substr(msg_body,1,1) in ('A', 'B', 'C', 'E') then 'BILL'
when dbms_lob.substr(msg_body,1,1) =  'F' then 'BOB'
when dbms_lob.substr(msg_body,1,1) =  'G' then 'JOHN'
when dbms_lob.substr(msg_body,1,1) =  'H' then 'TOM'
when dbms_lob.substr(msg_body,1,1) =  'I' then 'NIGEL'
end as networkId, count(1)