test/sql/Aggregate/testnw038.ksh

   1 #!/bin/ksh
   2 # Group by and aggregate function of projection list cannot applied on same field
   3 #
   4
   5 QUITFILE=${PWD}/sql/Aggregate/quit.sql
   6 REL_PATH=.
   7 if [ -s "$QUITFILE" ]
   8 then
   9     REL_PATH=`pwd`/sql/Aggregate
  10 fi
  11
  12 echo "create table t1 (f1 int,f2 int,f3 int);" >${REL_PATH}/grp_agg.sql
  13 echo "insert into t1 values(1,10,100);" >>${REL_PATH}/grp_agg.sql
  14 echo "insert into t1 values(2,20,100);" >>${REL_PATH}/grp_agg.sql
  15 echo "insert into t1 values(1,30,200);" >>${REL_PATH}/grp_agg.sql
  16 echo "insert into t1 values(3,40,200);" >>${REL_PATH}/grp_agg.sql
  17 echo "insert into t1 values(2,50,300);" >>${REL_PATH}/grp_agg.sql
  18 echo "insert into t1 values(1,60,300);" >>${REL_PATH}/grp_agg.sql
  19 echo "select * from t1;" >>${REL_PATH}/grp_agg.sql
  20 echo "select f1,MAX(f1) from t1;" >>${REL_PATH}/grp_agg.sql
  21 echo "select f1,COUNT(f1) from t1 group by f1;" >>${REL_PATH}/grp_agg.sql
  22 echo "select f1,MIN(f1) from t1 group by f1;" >>${REL_PATH}/grp_agg.sql
  23 echo "select f1,MAX(f1) from t1 group by f1;" >>${REL_PATH}/grp_agg.sql
  24 echo "select f1,SUM(f1) from t1 group by f1;" >>${REL_PATH}/grp_agg.sql
  25 echo "select f1,AVG(f1) from t1 group by f1;" >>${REL_PATH}/grp_agg.sql
  26 echo "select f1,MAX(f2) from t1 group by f1;" >>${REL_PATH}/grp_agg.sql
  27
  28
  29 $CSQL_INSTALL_ROOT/bin/csql -s ${REL_PATH}/grp_agg.sql
  30 if [ $? -ne 0 ]
  31 then
  32    $CSQL_INSTALL_ROOT/bin/csql -s ${REL_PATH}/dropt1.sql
  33    rm -f ${REL_PATH}/grp_agg.sql
  34    exit 1;
  35 fi
  36
  37 $CSQL_INSTALL_ROOT/bin/csql -s ${REL_PATH}/dropt1.sql
  38 if [ $? -ne 0 ]
  39 then
  40    exit 2;
  41 fi
  42
  43 rm -f ${REL_PATH}/grp_agg.sql
  44 exit 0;