SQLPlus Case statement on a substring


 
Thread Tools Search this Thread
Top Forums Programming SQLPlus Case statement on a substring
# 1  
Old 10-16-2012
SQLPlus Case statement on a substring

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.

Code:
 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:

Code:
 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
Code:
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  
Old 10-16-2012
Try:-

Code:
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)


Last edited by Yoda; 10-16-2012 at 05:32 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

SQLPLUS command with more than 1 select statement

Hi all, I'm using below code processId=`sqlplus -s ${sysuser}/${syspwd} <<CHK_PROCESS whenever sqlerror exit sql.sqlcode; set head off feedback off echo off pages 0 SELECT PROCESS_ID FROM LSHADMIN.DATA_DOMAIN WHERE DOMAIN_NAME = '${tabname}' ... (8 Replies)
Discussion started by: Pratiksha Mehra
8 Replies

2. Shell Programming and Scripting

Use sqlplus statement inside case sentence

Hello, I have a problem. I want to launch a different sql queries for different shell parameter values, something like this. #/bin/bash case $1 in "A") sqlplus -s user/pass << SQL query A; SQL "B") sqlplus -s user/pass << SQL2 ... (3 Replies)
Discussion started by: Vares
3 Replies

3. Shell Programming and Scripting

Connecting sqlplus from UNIX with multiple select statement

hi, i have a requirement where i need to connect sqlplus from unix and i am able to do so by following command: cust_count=`sqlplus -s $ORACLE_USER/$ORACLE_PASS@$ORACLE_SID << EOF set pagesize 0 set feedback off set verify off ... (1 Reply)
Discussion started by: lovelysethii
1 Replies

4. Shell Programming and Scripting

Case Statement

Hey, guys I really need some help with a project. "Write a shell program that examines the command line arguments, counts and collects the number of options. Basically it has to collect and count the arguments that start with a "-" and the one's that don't start with a - I know I have to use... (2 Replies)
Discussion started by: sk192010`
2 Replies

5. UNIX for Dummies Questions & Answers

If statement for substring within string (korn)

Hi all, Just looking for a simple if statement that searches for a substring within a varaible, and then performs some function. Problem is that I need it to work in Korn shell $var = *string* does not work in Korn i="xxxxxx00.00yyyyy.zzzzz" want to find 00.00 (2 Replies)
Discussion started by: jgrosecl
2 Replies

6. Shell Programming and Scripting

case statement

Hi, I am writing case statement to execute some finction, my requirement is once one of the case statement is executed again it has to prompt for the option. for script in `echo "$Script_Selected"` do case $script in 1) getNoOFActUsers ;; 2) moveServerrOORotation ;; ... (2 Replies)
Discussion started by: Satyak
2 Replies

7. Shell Programming and Scripting

case-insensitive if on substring

I'd like to print a line if a substring is matched in a case insensitive manner something like do a case insensitive search for ABCD as a substring: awk '{ if (substr($1,1,4) == "") print $1 }' infile > outfile I'm not certain how to make the syntax work??? Thanks (4 Replies)
Discussion started by: dcfargo
4 Replies

8. UNIX for Dummies Questions & Answers

If or Case Statement

I want to write a program with the following variables: a=7000 b=24000 c=613.8 The user can enter two words: Vivid or Blue for example. The challenge is that the user might not want to write the words the way they appear. The user can write V or v or vivid or Vivid or write Blue or blue, or B,... (1 Reply)
Discussion started by: Ernst
1 Replies

9. Shell Programming and Scripting

Case Statement

Can anyone please tell me why this wont work! Thanks so much! #!/bin/sh for file do case $file in *.*.*) echo Cannot have more than 1 dot exit ;; *'**'*) echo Cannot have more than 1 asterisk exit ;; *'*'*|?.) echo this is a target (19 Replies)
Discussion started by: Zeta_Acosta
19 Replies

10. Shell Programming and Scripting

case statement

Hi all, is it possible to create a 'dynamic' case statement. ie select option in `ls` do case satement depending on results of the above `ls` done I hope I have explained this ok! Thanks Helen (1 Reply)
Discussion started by: Bab00shka
1 Replies
Login or Register to Ask a Question