SQL only else part of case statement is printing

Question

cd /home/XXXXX
USER_NAME=1234
USER_PWD=1234
DEBUG=0


clear

echo -e "\n Enter The From Date in specified format like DD-MON-YY \n "
read From_Date

echo -e "\n Enter The To Date in specified format like DD-MON-YY \n "
read To_Date


res=`sqlplus -S -L $USER_NAME/$USER_PWD<<EOF | tail -n +2
set pages 0
set linesize 800
set heading off
set feedback off

select distinct CASE WHEN s.s_name in('A','B','AB','C') THEN 'PASSED' ELSE 'FAILED' END as      RESULT,count(*) from student s,Result r where r.REF_NO=s.REF_NO and trunc(r.TIME)>='$From_Date' and trunc(r.TIME)<='$To_Date' and s.response='S' group by rollup((CASE WHEN s.s_name in('A','B','AB','C') THEN 'PASSED' ELSE 'FAILED' END)) order by count(*);

EOF`

echo $res;

OUTPUT for this script is

FAILED 64

BUT OUTPUT want like

PASSED 34
FAILED 64

only one case part is working else part only working

Your shell script does not contain a `case` statement. Maybe something is wrong with your SQL. — tripleee, Jan 07 '15 at 09:16
You should `echo "$res"` with double quotes around the value, though. See also http://stackoverflow.com/questions/10067266/when-to-wrap-quotes-around-a-variable/27701642#27701642 — tripleee, Jan 07 '15 at 09:17
@savi: Does the SQL produce the correct result when you execute it manually? — Aaron Digulla, Jan 07 '15 at 11:22
@tripleee: That's not relevant here. Quoting is only important if you have to preserve whitespace, say in file names. `echo` doesn't care. — Aaron Digulla, Jan 07 '15 at 11:23
We don't know what exactly the value is supposed to contain. The shell will also perform wildcard expansion and other expansions on unquoted values. The norm should be to quote unless you specifically require whitespace splitting and wildcard expansion. See the linked question for details. — tripleee, Jan 07 '15 at 13:05

score 0 · Answer 1 · answered Jan 07 '15 at 11:29

The code above should produce the desired results if the SELECT statement returns a row for PASSED.

Try without the tail -n +2 to see all of the output; maybe sqlplus adds another empty line at the end.

Or try to use egrep which allows you to say "print only lines which start with ...":

res=$(sqlplus -S -L $USER_NAME/$USER_PWD<<EOF | egrep '^(FAILED|PASSED)'
... your SQL here ...
EOF

SQL only else part of case statement is printing

1 Answers1