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;
此脚本的输出为
FAILED 64
但是输出想要像
PASSED 34
FAILED 64
只有一个案例部分在工作,其他部分仅工作
如果 SELECT
语句返回 PASSED
行,上面的代码应该会产生所需的结果。
尝试不使用tail -n +2
来查看所有输出;也许sqlplus
末尾添加了另一个空行。
或者尝试使用允许您说"仅打印以...开头的行"的egrep
:
res=$(sqlplus -S -L $USER_NAME/$USER_PWD<<EOF | egrep '^(FAILED|PASSED)'
... your SQL here ...
EOF