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 FREEBSD

mailq

MAILQ(1)						      General Commands Manual							  MAILQ(1)

NAME

       mailq - print the mail queue

SYNOPSIS

       mailq [-Ac] [-q...]  [-v]

DESCRIPTION

       Mailq prints a summary of the mail messages queued for future delivery.

       The  first  line printed for each message shows the internal identifier used on this host for the message with a possible status character,
       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  shows  the error message that caused this message to be retained in the queue; it will not be present if the message is being
       processed for the first time.  The status characters are either * to indicate the job is being processed; X to indicate that  the  load	is
       too  high to process the job; and - to indicate that the job is too young to process.  The following lines show message recipients, one per
       line.

       Mailq is identical to ``sendmail -bp''.

       The relevant options are as follows:

       -Ac    Show the mail submission queue specified in /etc/mail/submit.cf instead of the MTA queue specified in /etc/mail/sendmail.cf.

       -qL    Show the "lost" items in the mail queue instead of the normal queue items.

       -qQ    Show the quarantined items in the mail queue instead of the normal queue items.

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

       -q[!]Q substr
	      Limit processed jobs to quarantined jobs containing substr as a substring of the quarantine reason or not when !	is specified.

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

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

       -v     Print 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 indicating 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 expanded from, if any.  Moreover, status messages for each recipient are printed if
	      available.

       Several sendmail.cf options influence the behavior of the mailq utility: The number of items printed  per  queue  group	is  restricted	by
       MaxQueueRunSize	if  that  value  is set.  The status character * is not printed for some values of QueueSortOrder, e.g., filename, random,
       modification, and none, unless a -q option is used to limit the processed jobs.

       The mailq utility exits 0 on success, and >0 if an error occurs.

SEE ALSO

       sendmail(8)

HISTORY

       The mailq command appeared in 4.0BSD.

							   $Date: 2013-11-22 20:51:55 $ 						  MAILQ(1)
All times are GMT -4. The time now is 03:33 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy