Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: SQLPlus Case statement on a substring
Top Forums Programming SQLPlus Case statement on a substring Post 302716355 by chris01010 on Tuesday 16th of October 2012 10:29:00 AM
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
 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

LEARN ABOUT SUNOS

mailq

mailq(1)							   User Commands							  mailq(1)

NAME

       mailq - print the mail queue

SYNOPSIS

       /usr/bin/mailq [-Ac] [-q subarg] [-v]

DESCRIPTION

       The mailq utility displays a summary of the mail messages queued for future delivery.

       The  first line displayed for each mail message shows the internal identifier used on this host for the message, the size of the message in
       bytes, the date and time the message was accepted into the queue, and the envelope sender of the message. The second line  of  the  display
       shows  the error message that caused this message to be retained in the queue. This line will not be displayed if the message is being pro-
       cessed for the first time.

       The mailq utility used to be identical to sendmail -bp. Now it checks for the authorization attribute, solaris.mail.mailq. If the check for
       the  invoking  user  succeeds,  sendmail  -bp  is executed with the remaining argument vector. Otherwise, an error message is printed. This
       authorization attribute is by default enabled for all users. It can be disabled by modifying the Basic Solaris User entry in prof_attr(4).

OPTIONS

       The following options are supported:

       -Ac	       Like sendmail(1M), this flag tells mailq to use submit.cf rather than sendmail.cf even  if  the	operation  mode  does  not
		       indicate  an  initial  mail submission. This will result in the client queue /var/spool/clientmqueue being displayed rather
		       than the default server queue /var/spool/mqueue.

       -qp[time]       Similar to -qtime, except that instead of periodically forking a child to process the queue, sendmail forks a  single  per-
		       sistent	child  for  each  queue  that alternates between processing the queue and sleeping. The sleep time is given as the
		       argument. The sleep time default is 1 second. The process will always sleep at least 5 seconds if the queue  was  empty	in
		       the previous queue run.

       -qf	       Processes saved	messages in the queue once and does not fork(), but runs in the foreground.

       -qG name        Processes jobs in the queue group called name only.

       -q[!]I substr   Limits processed jobs to those containing substr as a substring of the queue id, or not when ! is specified.

       -q[!]R substr   Limits processed jobs to those containing substr as a substring of one of the recipients, or not when ! is specified.

       -q[!]S substr   Limits processed jobs to those containing substr as a substring of the sender, or not when ! is specified.

       -v	       Prints  verbose information. This adds the priority of the message and a single character indicator (+ or blank) indicating
		       whether a warning message has been sent on the first line of the message. Additionally, extra lines may be intermixed  with
		       the  recipients that indicate the "controlling user" information. This shows who will own any programs that are executed on
		       behalf of this message and the name of the alias this command is expanded from, if any.

EXIT STATUS

       0	Successful completion.

       >0	An error occurred.

FILES

       /etc/security/prof_attr	       local source for execution profile attributes

       /var/spool/mqueue	       default server queue

       /var/spool/clientmqueue	       client queue

ATTRIBUTES

       See attributes(5) for descriptions of the following attributes:

       +-----------------------------+-----------------------------+
       |      ATTRIBUTE TYPE	     |	    ATTRIBUTE VALUE	   |
       +-----------------------------+-----------------------------+
       |Availability		     |SUNWsndmu 		   |
       +-----------------------------+-----------------------------+

SEE ALSO

       sendmail(1M), prof_attr(4), attributes(5)

SunOS 5.10							    10 Jul 2002 							  mailq(1)
All times are GMT -4. The time now is 08:07 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy