>>---------------------------------------------------------------------
>>-- File: SQLQAT13                Formerly $cats.testest.qat010t
>>-- Component: NonStop SQL Regression Test Suite
>>-- Description:
>>--    This test unit is a positive local test for the select statement.
>>-- It tests only normal (non-join and non-subquery) select statements,
>>-- with GROUP BY, HAVING, and ORDER BY clauses, and also tests some
>>-- complex queries that use all the clauses together.
>>--    This test unit uses the select global data base (sqldd01d).
>>-- Each test case in this test unit tests a particular feature of complex
>>-- normal select statements. For each test case, each base table
>>-- (tables 01 - 11) and at least one protection view and one shorthand
>>-- view have at least one select statement executed against them. This
>>-- is to assure that the particular feature that the test case is testing
>>-- works on all types of objects.
>>--    The test cases in this test unit are as follows:
>>--       testcase A0: GROUP BY
>>--       testcase A1: HAVING
>>--       testcase A2: ORDER BY
>>--       testcase A3: complex queries - combinations of all features
>>--       testcase A4: use of locking clauses (for stable access, for browse access, keep
>>--                    lock) with above clauses
>>--       testcase A5: variation of ordering of WHERE,GROUP BY,HAVING,
>>--                    ORDER ,locking clauses
>>--       testcase A6: variation of amount of white space (including
>>--                    blank lines) and upper/lower case
>>--       testcase A7: select aggregate functions with GROUP BY
>>--       testcase A8: select aggregate functions with HAVING and complex
>>--                    queries
>>--       testcase A9: select .. GROUP BY with ordinal numbers
>>
>>-- All testcases are documented further below
>>
>>-- *************    end test unit comments   **************
>>
>>
>>-- <testunit-summary>
>>-- select #3 pos/loc ufi norm complex
>>
>>
>>-- <testunit-specs>
>>
>>-- mode-type ufi
>>-- test-type functional
>>-- form-type pos/loc
>>-- select-test
>>
>>-- <testcase A0>
>>
>>--    <detail>
>>--       normal select test - this tests the use of a GROUP BY clause
>>--       in a select stmt.
>>
>>--    <templates>
>>--       US00
>>
>>--    <ufi-input>
>>      select char_1,decimal_1 from btsel01
+>      group by char_1,decimal_1;

CHAR_1  DECIMAL_1
------  ---------

D               7
A               4
A               5
D               5
E               1
C               8
C               9

--- 7 row(s) selected.
>>--    <ufi-input>
>>      select pic_x_1 from btsel02
+>      group by pic_x_1;

PIC_X_1
-------

B      
C      
Q      

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select pic_x_7,pic_9_7 from btsel03
+>      group by pic_9_7,pic_x_7 ;

PIC_X_7  PIC_9_7
-------  -------

A             90
7             80
8             80
5            100
michael       50
B             80

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select medium_int from btsel04
+>      group by medium_int;

MEDIUM_INT 
-----------

       1000
        999
        200
       2000

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select pic_x_b from btsel05
+>      group by pic_x_b;

PIC_X_B
-------

A      
D      
B      
C      

--- 4 row(s) selected.
>>
>>--    <ufi-input>
>>      select pic_x_a,col_1,col_3 from btsel06
+>      group by pic_x_a,col_1,col_3;

PIC_X_A  COL_1  COL_3
-------  -----  -----

joe        100    200
joe        300    400
joe        100    100
pam        900    900
sue        300    500
sue        200    300
sue        200    100

--- 7 row(s) selected.
>>
>>--     try grouping by same column twice, column name adjacent to itself
>>--    <ufi-input>
>>      select pic_x_a,col_1,col_3 from btsel06
+>      group by pic_x_a, col_1,col_1, col_3;

PIC_X_A  COL_1  COL_3
-------  -----  -----

joe        100    200
joe        300    400
joe        100    100
pam        900    900
sue        300    500
sue        200    300
sue        200    100

--- 7 row(s) selected.
>>
>>--     try grouping by same column twice, column not adjacent to itself
>>--    <ufi-input>
>>      select pic_x_a,col_1,col_3 from btsel06
+>      group by pic_x_a,col_1,col_3, col_1;

PIC_X_A  COL_1  COL_3
-------  -----  -----

joe        100    200
joe        300    400
joe        100    100
pam        900    900
sue        300    500
sue        200    300
sue        200    100

--- 7 row(s) selected.
>>
>>--    <ufi-input>
>>      select pic_x_a from btsel07
+>      group by pic_x_a;

PIC_X_A
-------

 al    
B      
JO     
P      
al     
jo     

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select pic_1,large_int from btsel08
+>      group by pic_1,large_int;

PIC_1  LARGE_INT           
-----  --------------------

D                      4000
D                      3000
B                      2000
B                      1000
X                       400
X                       300
X                       200
E                       100

--- 8 row(s) selected.
>>
>>
>>--     only select one of the group by fields
>>--    <ufi-input>
>>      select large_int from btsel08
+>      group by pic_1,large_int;

LARGE_INT           
--------------------

                4000
                3000
                2000
                1000
                 400
                 300
                 200
                 100

--- 8 row(s) selected.
>>
>>--    <ufi-input>
>>      select pic_x_5 from btsel09
+>      group by pic_x_5;

PIC_X_5
-------

      7
      3
      5
      4
      0

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select binary_32_signed from btsel10
+>      group by binary_32_signed;

BINARY_32_SIGNED
----------------

             100
           -1000
            -100
               0

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select decimal_10 from btsel11
+>      group by decimal_10;

DECIMAL_10 
-----------

        200
        160
        100
         50

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select new_name_1 from pvsel03
+>      group by new_name_1;

NEW_NAME_1
----------

        90
        80

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select new_name_1 from svsel13
+>      group by new_name_1;

NEW_NAME_1
----------

        80
        90

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select SYSKEY
+>      from btsel04
+>      group by SYSKEY;

SYSKEY              
--------------------

  216172782152933121
  216172782152933122
  216172782152933125
  216172782152933127
  216172782152933128
  216172782152933123
  216172782152933124
  216172782152933126

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select medium_int * binary_64_s
+>      from btsel01
+>      group by medium_int,binary_64_s;

(EXPR)              
--------------------

            16000000
             2000000
             9600000
            13500000
             2000000
            10000000
            20000000

--- 7 row(s) selected.
>>--    <end-input>
>>
>>
>>-- <testcase A1>
>>
>>--    <detail>
>>--      select normal test case - this tests the use of a HAVING clause
>>
>>--    <templates>
>>--    US00
>>
>>--    <ufi-input>
>>      select char_1,decimal_1 from btsel01
+>      group by char_1,decimal_1
+>         having (decimal_1 > 1) and (decimal_1 < 9);

CHAR_1  DECIMAL_1
------  ---------

D               7
A               4
A               5
D               5
C               8

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select pic_x_1 from btsel02
+>      group by pic_x_1
+>      having pic_x_1 > 'B';

PIC_X_1
-------

C      
Q      

--- 2 row(s) selected.
>>
>>-- <comment> test select from 0 groups
>>--    <ufi-input>
>>      select pic_x_1 from btsel02
+>      group by pic_x_1
+>      having pic_x_1 > 'Q';

--- 0 row(s) selected.
>>
>>--    <ufi-input>
>>      select pic_9_7,pic_x_7 from btsel03
+>      group by pic_9_7, pic_x_7
+>      having (pic_x_7 not like 'A') or (pic_x_7 not like 'B');

PIC_9_7  PIC_X_7
-------  -------

     90  A      
     80  7      
     80  8      
    100  5      
     50  michael
     80  B      

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select medium_int from btsel04
+>      group by medium_int
+>      having medium_int in (999,1000,2000);

MEDIUM_INT 
-----------

       1000
        999
       2000

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select pic_x_b from btsel05
+>      group by pic_x_b
+>      having pic_x_b not like 'C';

PIC_X_B
-------

A      
D      
B      

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select pic_x_a,col_1,col_3 from btsel06
+>      group by pic_x_a,col_1,col_3
+>      having col_1 between 100 and col_3;

PIC_X_A  COL_1  COL_3
-------  -----  -----

joe        100    200
joe        300    400
joe        100    100
pam        900    900
sue        300    500
sue        200    300

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select pic_x_a from btsel07
+>      group by pic_x_a
+>      having pic_x_a in ('jo','al','P','JO',' al');

PIC_X_A
-------

 al    
JO     
P      
al     
jo     

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select pic_1,large_int from btsel08
+>      group by pic_1,large_int
+>      having large_int in (100,200,1000,2000);

PIC_1  LARGE_INT           
-----  --------------------

B                      2000
B                      1000
X                       200
E                       100

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select pic_x_5 from btsel09
+>      group by pic_x_5
+>      having pic_x_5 between 4 and 7;

PIC_X_5
-------

      7
      5
      4

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select binary_32_signed from btsel10
+>      group by binary_32_signed
+>      having binary_32_signed in (-100, -1000,100);

BINARY_32_SIGNED
----------------

             100
           -1000
            -100

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select decimal_10 from btsel11
+>      group by decimal_10
+>         having decimal_10 <> 50;

DECIMAL_10 
-----------

        200
        160
        100

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select new_name_1 from pvsel03
+>      group by new_name_1
+>      having new_name_1 = 80;

NEW_NAME_1
----------

        80

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select new_name_1 from svsel13
+>      group by new_name_1
+>      having new_name_1 = 80;

NEW_NAME_1
----------

        80

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select SYSKEY
+>      from btsel04
+>      group by SYSKEY
+>      having SYSKEY > 0;

SYSKEY              
--------------------

  216172782152933121
  216172782152933122
  216172782152933125
  216172782152933127
  216172782152933128
  216172782152933123
  216172782152933124
  216172782152933126

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select medium_int * binary_64_s
+>      from btsel01
+>      group by medium_int, binary_64_s
+>         having medium_int + binary_64_s < 10000;

(EXPR)              
--------------------

             9600000
             2000000
            10000000

--- 3 row(s) selected.
>>--    <end-input>
>>
>>
>>-- <testcase A2>
>>
>>--    <detail>
>>--    select normal test case - this tests the use of an ORDER BY
>>--    clause in a select stmt.
>>
>>
>>--    <templates>
>>--    US00
>>
>>--    <ufi-input>
>>      select * from btsel01
+>      order by char_1,decimal_1;

CHAR_1  CHAR_10     PIC_X_1  PIC_X_7  PIC_X_LONG                                                                                                                                                                                                VAR_CHAR                                                                                                                                                                                                                                                       BINARY_SIGNED  BINARY_32_U  BINARY_64_S            PIC_COMP_1            PIC_COMP_2  PIC_COMP_3    SMALL_INT  MEDIUM_INT  LARGE_INT             DECIMAL_1  DECIMAL_2_SIGNED  DECIMAL_3_UNSIGNED  PIC_DECIMAL_1  PIC_DECIMAL_2  PIC_DECIMAL_3
------  ----------  -------  -------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------  -----------  ---------------------  --------------------  ----------  ------------  ---------  ----------  --------------------  ---------  ----------------  ------------------  -------------  -------------  -------------

A       steven      C        walter   bob                                                                                                                                                                                                       B                                                                                                                                                                                                                                                                         50        50.00                200.000                    50         .12     100.90000         10       10000            1000000000          4               .50                  90            1.1           .100              1
A       bobby       A        bobby    bop                                                                                                                                                                                                       B                                                                                                                                                                                                                                                                         60        60.00               1200.000                    60         .79     100.99000       1000        8000                 -1000          5               .60                 100            2.1           .200              2
C       maureen     E        jimmy    rum                                                                                                                                                                                                       marilyn                                                                                                                                                                                                                                                                 3000        80.00               2000.000                   500         .50     100.70000       9000        1000                  2000          8               .97                 150            7.1           .700              7
C       marcia      Z        johnny   dum                                                                                                                                                                                                       thomas                                                                                                                                                                                                                                                                  4000        40.00               2000.000                    50         .60     100.60000       8000        5000                     0          9               .99                 110            8.1           .800              8
D       melissa     C        7        pop                                                                                                                                                                                                       jimmy                                                                                                                                                                                                                                                                   1000        80.00               1500.000                   500         .20     100.99990         80        9000                   999          5               .80                 120            4.1           .400              4
D       michelle    D        michael  rat                                                                                                                                                                                                       thomas                                                                                                                                                                                                                                                                 -5000        90.00               2000.000                   500         .40     100.80000         90        8000                   200          7               .93                 140            6.1           .600              6
D       steven      B        9        bat                                                                                                                                                                                                       thomas                                                                                                                                                                                                                                                                  8000        70.00               2000.000                   500         .10     100.99900         90       10000                  1000          7               .70                 110            3.1           .300              3
E       monica      Q        sue      pat                                                                                                                                                                                                       christopher                                                                                                                                                                                                                                                             2000        90.00               1200.000                  3000         .30     100.99999       2000        8000              -1000000          1               .90                  80            5.1           .500              5

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel02
+>      order by pic_x_1 ASC;

PIC_X_1
-------

B      
B      
B      
C      
C      
Q      
Q      
Q      

--- 8 row(s) selected.
>>--    <ufi-input>
>>--      select * from btsel03
>>--      order by 4,1 DESC;
>>--    <ufi-input>
>>--      select * from btsel04
>>--      order by 2;
>>--    <ufi-input>
>>      select * from btsel05
+>      order by pic_x_b DESC;

PIC_X_A  PIC_X_B  PIC_X_C  COL_1  COL_2  COL_3  COL_4  COL_5  COL_6  COL_7  COL_8  COL_9  COL_10
-------  -------  -------  -----  -----  -----  -----  -----  -----  -----  -----  -----  ------

can      D        by       lowry  steve  slope  debby  junk   junk   junk   junk   junk   junk  
deb      D        jo       lowry  mary   sunny  debra  junk   junk   junk   junk   junk   junk  
box      C        to       howie  debra  snow   debbi  junk   junk   junk   junk   junk   junk  
red      B        go       lowry  sue    ski    cammy  junk   junk   junk   junk   junk   junk  
not      B        on       lowry  mary   mogul  pammy  junk   junk   junk   junk   junk   junk  
why      B        so       lowry  amy    squaw  tammy  junk   junk   junk   junk   junk   junk  
sue      A        in       peggy  diane  zora   cathy  mary   mary   rhoda  debra  sue    madge 
joe      A        al       pablo  david  amy    amy    steve  steve  walt   mojo   joe    percy 

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel06
+>      order by pic_x_a DESC,col_1,col_3 ASC;

PIC_X_A  PIC_X_B  PIC_X_C  COL_1  COL_2  COL_3  COL_4  COL_5  COL_6  COL_7  COL_8  COL_9  COL_10  COL_21       COL_22       COL_23       COL_24       COL_25       COL_26       COL_27      COL_28      COL_29      COL_30      COL_41       COL_42       COL_43       COL_44       COL_45      COL_46      COL_47      COL_48      COL_61                COL_62                COL_63                COL_64                COL_65                COL_66                COL_67               COL_68               COL_69               COL_70
-------  -------  -------  -----  -----  -----  -----  -----  -----  -----  -----  -----  ------  -----------  -----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  -------------------  -------------------  -------------------  -------------------

sue      Q        on         200    400    100    100    400    100    100     50    100     200          200          400          100          100          400          100         100          50         100         200       200.00       400.00       100.00       100.00      400.00      100.00      100.00       50.00                   200                   400                   100                   100                   400                   100                  100                   50                  100                  200
sue      D        so         200   1000    300   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      C        by         300   1000    500   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
pam      D        al         900   1000    900   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      Z        in         100    200    100    200    200    100    100    100     50     100          100          200          100          200          200          100         100         100          50         100       100.00       200.00       100.00       200.00      200.00      100.00      100.00      100.00                   100                   200                   100                   200                   200                   100                  100                  100                   50                  100
joe      C        go         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      A        jo         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      B        to         300   1000    400   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000

--- 8 row(s) selected.
>>
>>--     order by same column name, column name adjacent to itself
>>--    <ufi-input>
>>      select * from btsel06
+>      order by pic_x_a , col_1,col_1, col_3 ;

PIC_X_A  PIC_X_B  PIC_X_C  COL_1  COL_2  COL_3  COL_4  COL_5  COL_6  COL_7  COL_8  COL_9  COL_10  COL_21       COL_22       COL_23       COL_24       COL_25       COL_26       COL_27      COL_28      COL_29      COL_30      COL_41       COL_42       COL_43       COL_44       COL_45      COL_46      COL_47      COL_48      COL_61                COL_62                COL_63                COL_64                COL_65                COL_66                COL_67               COL_68               COL_69               COL_70
-------  -------  -------  -----  -----  -----  -----  -----  -----  -----  -----  -----  ------  -----------  -----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  -------------------  -------------------  -------------------  -------------------

joe      Z        in         100    200    100    200    200    100    100    100     50     100          100          200          100          200          200          100         100         100          50         100       100.00       200.00       100.00       200.00      200.00      100.00      100.00      100.00                   100                   200                   100                   200                   200                   100                  100                  100                   50                  100
joe      C        go         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      A        jo         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      B        to         300   1000    400   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
pam      D        al         900   1000    900   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      Q        on         200    400    100    100    400    100    100     50    100     200          200          400          100          100          400          100         100          50         100         200       200.00       400.00       100.00       100.00      400.00      100.00      100.00       50.00                   200                   400                   100                   100                   400                   100                  100                   50                  100                  200
sue      D        so         200   1000    300   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      C        by         300   1000    500   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000

--- 8 row(s) selected.
>>
>>--     order by same column name, column name adjacent to itself
>>--    <ufi-input>
>>      select * from btsel06
+>      order by pic_x_a , col_1, col_3, col_1 ;

PIC_X_A  PIC_X_B  PIC_X_C  COL_1  COL_2  COL_3  COL_4  COL_5  COL_6  COL_7  COL_8  COL_9  COL_10  COL_21       COL_22       COL_23       COL_24       COL_25       COL_26       COL_27      COL_28      COL_29      COL_30      COL_41       COL_42       COL_43       COL_44       COL_45      COL_46      COL_47      COL_48      COL_61                COL_62                COL_63                COL_64                COL_65                COL_66                COL_67               COL_68               COL_69               COL_70
-------  -------  -------  -----  -----  -----  -----  -----  -----  -----  -----  -----  ------  -----------  -----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  -------------------  -------------------  -------------------  -------------------

joe      Z        in         100    200    100    200    200    100    100    100     50     100          100          200          100          200          200          100         100         100          50         100       100.00       200.00       100.00       200.00      200.00      100.00      100.00      100.00                   100                   200                   100                   200                   200                   100                  100                  100                   50                  100
joe      C        go         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      A        jo         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      B        to         300   1000    400   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
pam      D        al         900   1000    900   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      Q        on         200    400    100    100    400    100    100     50    100     200          200          400          100          100          400          100         100          50         100         200       200.00       400.00       100.00       100.00      400.00      100.00      100.00       50.00                   200                   400                   100                   100                   400                   100                  100                   50                  100                  200
sue      D        so         200   1000    300   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      C        by         300   1000    500   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000

--- 8 row(s) selected.
>>
>>--     order by same column name, one ASC, one DESC
>>--    <ufi-input>
>>      select * from btsel06
+>      order by pic_x_a , col_1 ASC, col_1 DESC, col_3 ;

PIC_X_A  PIC_X_B  PIC_X_C  COL_1  COL_2  COL_3  COL_4  COL_5  COL_6  COL_7  COL_8  COL_9  COL_10  COL_21       COL_22       COL_23       COL_24       COL_25       COL_26       COL_27      COL_28      COL_29      COL_30      COL_41       COL_42       COL_43       COL_44       COL_45      COL_46      COL_47      COL_48      COL_61                COL_62                COL_63                COL_64                COL_65                COL_66                COL_67               COL_68               COL_69               COL_70
-------  -------  -------  -----  -----  -----  -----  -----  -----  -----  -----  -----  ------  -----------  -----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  -------------------  -------------------  -------------------  -------------------

joe      Z        in         100    200    100    200    200    100    100    100     50     100          100          200          100          200          200          100         100         100          50         100       100.00       200.00       100.00       200.00      200.00      100.00      100.00      100.00                   100                   200                   100                   200                   200                   100                  100                  100                   50                  100
joe      C        go         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      A        jo         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      B        to         300   1000    400   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
pam      D        al         900   1000    900   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      Q        on         200    400    100    100    400    100    100     50    100     200          200          400          100          100          400          100         100          50         100         200       200.00       400.00       100.00       100.00      400.00      100.00      100.00       50.00                   200                   400                   100                   100                   400                   100                  100                   50                  100                  200
sue      D        so         200   1000    300   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      C        by         300   1000    500   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000

--- 8 row(s) selected.
>>
>>--    <ufi-input>
>>      select * from btsel07
+>      order by pic_x_a;

PIC_X_A  PIC_X_B  PIC_X_C
-------  -------  -------

 al      F        al     
 al      F        di     
B        A        ed     
JO       D        em     
P        P        P      
al       F        al     
al       Q        al     
al       E        bo     
jo       C        ek     
jo       Z        jo     

--- 10 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel08
+>      order by 3  DESC, large_int ASC;

LARGE_INT             PIC_252                                                                                                                                                                                                                                                       PIC_1
--------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----

                 200  squaw                                                                                                                                                                                                                                                         X    
                 300  Q                                                                                                                                                                                                                                                             X    
                 400  joseph                                                                                                                                                                                                                                                        X    
                 100  carltons                                                                                                                                                                                                                                                      E    
                3000  george                                                                                                                                                                                                                                                        D    
                4000  valley                                                                                                                                                                                                                                                        D    
                1000  harveys                                                                                                                                                                                                                                                       B    
                2000  alexander                                                                                                                                                                                                                                                     B    

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel09
+>      order by pic_x_5;

PIC_X_A  PIC_X_2  PIC_X_3  PIC_X_4  PIC_X_5  PIC_X_6  PIC_X_7
-------  -------  -------  -------  -------  -------  -------

%t_      junk     j        %_             0        0        0
_t%      junk     j        _%             0        0        0
\ts      t_go     j        to%go          0        0        0
\t_      junk     j        \%             0        0        0
sue      mary     Z        MARY           3       10       20
deb      bill     Y        BILL           4       10       30
box      here     R        debby          5       11       50
red      long     S        tommy          5       15       70
can      come     X        TED            7       77       77
not      time     P        diane          7       20       40
joe      over     W        maria          7       90      100
why      gone     R        billy          7       15       30

--- 12 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel10
+>      order by binary_32_signed;

PIC_X_A  PIC_X_7  PIC_9_7  CHAR_10     DECIMAL_10   BINARY_UNSIGNED  BINARY_32_SIGNED
-------  -------  -------  ----------  -----------  ---------------  ----------------

bob      bobby          2  ebediih              90              100             -1000
rob      bobby          2  ebediih              90              100             -1000
tom      tom            3  abadefih            100              100              -100
don      who           50  outside           10000              100              -100
ron      they        2000  onsides           10000              100              -100
rod      for          400  inside               25               60                 0
jon      does        1000  offsides          50000              500               100
sue      sue            1  ebedafiih           110               80               100

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel11
+>      order by decimal_10;

PIC_X_A  PIC_X_7  PIC_9_7  CHAR_10     DECIMAL_10   BINARY_UNSIGNED  BINARY_32_SIGNED
-------  -------  -------  ----------  -----------  ---------------  ----------------

mar      why           50  house                50             7777                 0
tar      what         500  none                100             8888                 0
dan      sue          100  ebediih             100              100                60
jar      how          600  inside              160              500               500
car      who          400  done                160              500               500
bob      bob          100  abadefih            160              200               200
boe      tommy        200  ebedafiih           200               50                50
bar      where        300  gone                200             9999              -100

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from pvsel03
+>      order by new_name_1;

NEW_NAME_1  NEW_NAME_2   NEW_NAME_3  NEW_NAME_4          
----------  -----------  ----------  --------------------

        80         6.00  7             216172782159534338
        80         6.00  B             216172782159534343
        80         6.00  7             216172782159534342
        90         5.00  A             216172782159534337

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select * from svsel13
+>      order by new_name_1;

NEW_NAME_1  NEW_NAME_2   NEW_NAME_3  NEW_NAME_4            VAR_CHAR                                                                                                                                                                                                                                                     MEDIUM_INT   PIC_X_7  PIC_COMP_1
----------  -----------  ----------  --------------------  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  -------  --------------------

        80         6.00  7             216172782159534338  tom                                                                                                                                                                                                                                                                 1000  7                         200
        80         6.00  B             216172782159534343  bill                                                                                                                                                                                                                                                                2000  B                         500
        80         6.00  7             216172782159534342  tom                                                                                                                                                                                                                                                                 1000  7                         200
        90         5.00  A             216172782159534337  bob                                                                                                                                                                                                                                                                  999  A                         300

--- 4 row(s) selected.
>>
>>--    <comment> order by SYSKEY
>>--    <ufi-input>
>>      select SYSKEY,*
+>      from btsel03
+>      order by 1;

SYSKEY                PIC_X_7  BINARY_32_U  PIC_X4_A  PIC_9_7  BINARY_64_S
--------------------  -------  -----------  --------  -------  ---------------------

  216172782159534337  A               5.00  make           90                 200.00
  216172782159534338  7               6.00  joe            80                1200.00
  216172782159534339  8               6.00  joe            80                1200.00
  216172782159534340  5            1000.00  5             100                1000.00
  216172782159534341  michael        50.00  dave           50                1500.00
  216172782159534342  7               6.00  john           80                2000.00
  216172782159534343  B               6.00  mark           80                3000.00
  216172782159534344  michael        70.00  joan           50                4000.00

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select btsel03.*,SYSKEY
+>      from btsel03
+>      order by 6;

PIC_X_7  BINARY_32_U  PIC_X4_A  PIC_9_7  BINARY_64_S            SYSKEY
-------  -----------  --------  -------  ---------------------  --------------------

A               5.00  make           90                 200.00    216172782159534337
7               6.00  joe            80                1200.00    216172782159534338
8               6.00  joe            80                1200.00    216172782159534339
5            1000.00  5             100                1000.00    216172782159534340
michael        50.00  dave           50                1500.00    216172782159534341
7               6.00  john           80                2000.00    216172782159534342
B               6.00  mark           80                3000.00    216172782159534343
michael        70.00  joan           50                4000.00    216172782159534344

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select btsel03.*,SYSKEY
+>      from btsel03
+>      order by 4,6;

PIC_X_7  BINARY_32_U  PIC_X4_A  PIC_9_7  BINARY_64_S            SYSKEY
-------  -----------  --------  -------  ---------------------  --------------------

michael        50.00  dave           50                1500.00    216172782159534341
michael        70.00  joan           50                4000.00    216172782159534344
7               6.00  joe            80                1200.00    216172782159534338
8               6.00  joe            80                1200.00    216172782159534339
7               6.00  john           80                2000.00    216172782159534342
B               6.00  mark           80                3000.00    216172782159534343
A               5.00  make           90                 200.00    216172782159534337
5            1000.00  5             100                1000.00    216172782159534340

--- 8 row(s) selected.
>>
>>--     order by column not in select list
>>--    <ufi-input>
>>       select small_int
+>       from btsel01
+>       order by medium_int;

SMALL_INT
---------

     9000
     8000
       90
     1000
     2000
       80
       10
       90

--- 8 row(s) selected.
>>
>>--    <end-input>
>>
>>
>>
>>-- <testcase A3>
>>
>>--    <detail>
>>--    select normal test unit - this tests some complex queries that
>>--    use all the SELECT clauses together.
>>
>>--    <templates>
>>--    US00
>>
>>--    <ufi-input>
>>      select char_1, decimal_1
+>      from btsel01
+>      where (char_1 like '%') and
+>            (var_char in ('B','jimmy','thomas','marilyn','christopher'))
+>        and (binary_32_u between 50 and 100) and
+>               (decimal_1 < (decimal_2_signed*100))
+>      group by char_1,decimal_1
+>      having decimal_1 between 2 and 8
+>      order by decimal_1   DESC
+>      for repeatable access;

CHAR_1  DECIMAL_1
------  ---------

C               8
D               7
A               5
D               5
A               4

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select distinct var_char,binary_64_s, pic_comp_1
+>      from btsel01
+>      where (char_1 like '%') and
+>            (var_char in ('B','jimmy','thomas','marilyn','christopher'))
+>        and (binary_32_u between 50 and 100) and
+>               (decimal_1 < (decimal_2_signed*100))
+>      group by binary_64_s,pic_comp_1, var_char
+>      having binary_64_s > pic_comp_1
+>      order by binary_64_s, binary_64_s DESC
+>      for repeatable access;

VAR_CHAR                                                                                                                                                                                                                                                       BINARY_64_S            PIC_COMP_1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ---------------------  --------------------

B                                                                                                                                                                                                                                                                            200.000                    50
B                                                                                                                                                                                                                                                                           1200.000                    60
jimmy                                                                                                                                                                                                                                                                       1500.000                   500
marilyn                                                                                                                                                                                                                                                                     2000.000                   500
thomas                                                                                                                                                                                                                                                                      2000.000                   500

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select (new_name_1 * new_name_1),new_name_3
+>      from pvsel03
+>      where (new_name_1 between new_name_2 and 100) and
+>            (new_name_3 in ('A','B','7')) and
+>            (new_name_3 like '_') and
+>            (new_name_1 > (new_name_2 + 20))
+>      group by new_name_1,new_name_3
+>      having new_name_1 = 80
+>      order by new_name_3 ASC
+>      for stable access;

--- 0 row(s) selected.
>>--    <ufi-input>
>>      select new_name_1,new_name_4
+>      from svsel13
+>      where ((var_char like 'b%') or (var_char like 'tom')) and
+>            (pic_comp_1 in (200,300,400,500)) and
+>            (new_name_1 between 80 and 90)
+>      group by new_name_1,new_name_4
+>      having new_name_1 = 80
+>      order by new_name_1
+>      for browse access;

NEW_NAME_1  NEW_NAME_4          
----------  --------------------

        80    216172782159534338
        80    216172782159534342
        80    216172782159534343

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select medium_int,SYSKEY
+>      from btsel04
+>      where (medium_int > 200)
+>      group by medium_int,SYSKEY
+>      having medium_int in (999,1000,2000)
+>      order by medium_int;

MEDIUM_INT   SYSKEY              
-----------  --------------------

        999    216172782152933122
       1000    216172782152933121
       1000    216172782152933127
       1000    216172782152933126
       2000    216172782152933125
       2000    216172782152933128

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select pic_x_b from btsel05
+>      where col_1 = 'lowry'
+>      group by pic_x_b;

PIC_X_B
-------

D      
B      

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel06
+>         where col_6 < 90
+>      order by pic_x_a,col_1;

PIC_X_A  PIC_X_B  PIC_X_C  COL_1  COL_2  COL_3  COL_4  COL_5  COL_6  COL_7  COL_8  COL_9  COL_10  COL_21       COL_22       COL_23       COL_24       COL_25       COL_26       COL_27      COL_28      COL_29      COL_30      COL_41       COL_42       COL_43       COL_44       COL_45      COL_46      COL_47      COL_48      COL_61                COL_62                COL_63                COL_64                COL_65                COL_66                COL_67               COL_68               COL_69               COL_70
-------  -------  -------  -----  -----  -----  -----  -----  -----  -----  -----  -----  ------  -----------  -----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  -------------------  -------------------  -------------------  -------------------

joe      A        jo         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      C        go         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      B        to         300   1000    400   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
pam      D        al         900   1000    900   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      D        so         200   1000    300   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      C        by         300   1000    500   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select pic_1,large_int from btsel08
+>      where large_int >= 100
+>      group by pic_1,large_int
+>      having large_int in (100,200,1000,2000);

PIC_1  LARGE_INT           
-----  --------------------

B                      2000
B                      1000
X                       200
E                       100

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select pic_x_5 from btsel09
+>--       where pic_x_6 <> 11
+>      group by pic_x_5
+>      order by pic_x_5;

PIC_X_5
-------

      0
      3
      4
      5
      7

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select decimal_10 from btsel10
+>      group by decimal_10
+>      having decimal_10 in (90,100,110,10000,50000)
+>      order by decimal_10;

DECIMAL_10 
-----------

         90
        100
        110
      10000
      50000

--- 5 row(s) selected.
>>--    <end-input>
>>
>>
>>
>>-- <testcase A4>
>>
>>--    <detail>
>>--     select normal test case - this tests the use of the for browse access,
>>--     for stable access, and for repeatable access clauses with GROUP BY
>>--     HAVING, and ORDER BY clauses. This is a test for syntax only - no actual
>>--     checking of the locks obtained is done.
>>
>>--    <templates>
>>--    US00
>>
>>--    <comment>  *****    audited tables  *****
>>--    <ufi-input>
>>      select large_int from btsel08
+>      group by large_int
+>      for browse access;

LARGE_INT           
--------------------

                4000
                3000
                2000
                1000
                 400
                 300
                 200
                 100

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select pic_x_5 from btsel09
+>      group by pic_x_5
+>      having pic_x_5 > 4
+>      for stable access;

PIC_X_5
-------

      7
      5

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel10
+>      order by pic_9_7
+>      for repeatable access;

PIC_X_A  PIC_X_7  PIC_9_7  CHAR_10     DECIMAL_10   BINARY_UNSIGNED  BINARY_32_SIGNED
-------  -------  -------  ----------  -----------  ---------------  ----------------

sue      sue            1  ebedafiih           110               80               100
bob      bobby          2  ebediih              90              100             -1000
rob      bobby          2  ebediih              90              100             -1000
tom      tom            3  abadefih            100              100              -100
don      who           50  outside           10000              100              -100
rod      for          400  inside               25               60                 0
jon      does        1000  offsides          50000              500               100
ron      they        2000  onsides           10000              100              -100

--- 8 row(s) selected.
>>
>>--    <comment>  *****  non-audited tables *****
>>--    <ufi-input>
>>      select pic_x_b from btsel05
+>      group by pic_x_b
+>      for stable access;

PIC_X_B
-------

A      
D      
B      
C      

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select pic_x_b from btsel05
+>      group by pic_x_b
+>      having (pic_x_b = 'D') or (pic_x_b = 'B')
+>      for repeatable access;

PIC_X_B
-------

D      
B      

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel05
+>      order by pic_x_b
+>      for browse access;

PIC_X_A  PIC_X_B  PIC_X_C  COL_1  COL_2  COL_3  COL_4  COL_5  COL_6  COL_7  COL_8  COL_9  COL_10
-------  -------  -------  -----  -----  -----  -----  -----  -----  -----  -----  -----  ------

joe      A        al       pablo  david  amy    amy    steve  steve  walt   mojo   joe    percy 
sue      A        in       peggy  diane  zora   cathy  mary   mary   rhoda  debra  sue    madge 
red      B        go       lowry  sue    ski    cammy  junk   junk   junk   junk   junk   junk  
not      B        on       lowry  mary   mogul  pammy  junk   junk   junk   junk   junk   junk  
why      B        so       lowry  amy    squaw  tammy  junk   junk   junk   junk   junk   junk  
box      C        to       howie  debra  snow   debbi  junk   junk   junk   junk   junk   junk  
deb      D        jo       lowry  mary   sunny  debra  junk   junk   junk   junk   junk   junk  
can      D        by       lowry  steve  slope  debby  junk   junk   junk   junk   junk   junk  

--- 8 row(s) selected.
>>
>>--    <comment>    **** views *****
>>--    <ufi-input>
>>      select new_name_1 from pvsel03
+>      group by new_name_1
+>      for repeatable access;

NEW_NAME_1
----------

        90
        80

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select new_name_1 from svsel13
+>      group by new_name_1
+>      for repeatable access;

NEW_NAME_1
----------

        80
        90

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select new_name_1 from pvsel03
+>      group by new_name_1
+>      having new_name_1 = 80
+>      for browse access;

NEW_NAME_1
----------

        80

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select new_name_1 from svsel13
+>      group by new_name_1
+>      having new_name_1 = 80
+>      for browse access;

NEW_NAME_1
----------

        80

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select * from pvsel03
+>      order by new_name_1
+>      for stable access;

NEW_NAME_1  NEW_NAME_2   NEW_NAME_3  NEW_NAME_4          
----------  -----------  ----------  --------------------

        80         6.00  7             216172782159534338
        80         6.00  B             216172782159534343
        80         6.00  7             216172782159534342
        90         5.00  A             216172782159534337

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select * from svsel13
+>      order by new_name_1
+>      for stable access;

NEW_NAME_1  NEW_NAME_2   NEW_NAME_3  NEW_NAME_4            VAR_CHAR                                                                                                                                                                                                                                                     MEDIUM_INT   PIC_X_7  PIC_COMP_1
----------  -----------  ----------  --------------------  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  -------  --------------------

        80         6.00  7             216172782159534338  tom                                                                                                                                                                                                                                                                 1000  7                         200
        80         6.00  B             216172782159534343  bill                                                                                                                                                                                                                                                                2000  B                         500
        80         6.00  7             216172782159534342  tom                                                                                                                                                                                                                                                                 1000  7                         200
        90         5.00  A             216172782159534337  bob                                                                                                                                                                                                                                                                  999  A                         300

--- 4 row(s) selected.
>>--    <end-input>
>>
>>
>>
>>-- <testcase A5>
>>
>>--    <detail>
>>--    select normal test unit - this test case checks that the ordering
>>--    of the different SQL clauses that can appear in any order have
>>--    no effect on the functionality of the statement. The select statement
>>--    must have the SELECT clause first, followed by the FROM clause.
>>--    All other clauses may appear in any order after that. The following
>>--    tests will have the order of these clauses randomly varied from
>>--    the standard order of SELECT-FROM-WHERE-GROUP BY-HAVING-ORDER BY-
>>--    for browse access or for stable access or for repeatable access.
>>
>>
>>--    <templates>
>>--    US00
>>
>>--    <ufi-input>
>>      select char_1, decimal_1
+>      from btsel01
+>      where (char_1 like '%') and
+>            (var_char in ('B','jimmy','thomas','marilyn','christopher'))
+>        and (binary_32_u between 50 and 100) and
+>               (decimal_1 < decimal_2_signed)
+>      group by char_1,decimal_1
+>      having decimal_1 between 2 and 8
+>      order by decimal_1   DESC
+>      for repeatable access;

--- 0 row(s) selected.
>>
>>
>>
>>--    <ufi-input>
>>      select distinct var_char,binary_64_s, pic_comp_1
+>      from btsel01
+>      where (char_1 like '%') and
+>            (var_char in ('B','jimmy','thomas','marilyn','christopher'))
+>        and (binary_32_u between 50 and 100) and
+>               (decimal_1 < decimal_2_signed)
+>      group by binary_64_s,pic_comp_1, var_char
+>      having binary_64_s > pic_comp_1
+>      order by binary_64_s, binary_64_s DESC
+>      for stable access;

--- 0 row(s) selected.
>>
>>
>>
>>--    <ufi-input>
>>      select new_name_1,new_name_4
+>      from svsel13
+>      where ((var_char like 'b%') or (var_char like 'tom')) and
+>            (pic_comp_1 in (200,300,400,500)) and
+>            (new_name_1 between 80 and 90)
+>      group by new_name_1,new_name_4
+>      having new_name_1 = 80
+>      order by new_name_1;

NEW_NAME_1  NEW_NAME_4          
----------  --------------------

        80    216172782159534338
        80    216172782159534342
        80    216172782159534343

--- 3 row(s) selected.
>>
>>
>>--    <ufi-input>
>>      select medium_int,SYSKEY
+>      from btsel04
+>      where (medium_int > 200)
+>      group by medium_int,SYSKEY
+>      having medium_int in (999,1000,2000)
+>      order by medium_int;

MEDIUM_INT   SYSKEY              
-----------  --------------------

        999    216172782152933122
       1000    216172782152933121
       1000    216172782152933127
       1000    216172782152933126
       2000    216172782152933125
       2000    216172782152933128

--- 6 row(s) selected.
>>
>>--    <ufi-input>
>>      select pic_x_b from btsel05
+>      where col_1 = 'lowry'
+>      group by pic_x_b;

PIC_X_B
-------

D      
B      

--- 2 row(s) selected.
>>
>>--    <ufi-input>
>>      select * from btsel06
+>      where col_6 < 90
+>      order by pic_x_a,col_1;

PIC_X_A  PIC_X_B  PIC_X_C  COL_1  COL_2  COL_3  COL_4  COL_5  COL_6  COL_7  COL_8  COL_9  COL_10  COL_21       COL_22       COL_23       COL_24       COL_25       COL_26       COL_27      COL_28      COL_29      COL_30      COL_41       COL_42       COL_43       COL_44       COL_45      COL_46      COL_47      COL_48      COL_61                COL_62                COL_63                COL_64                COL_65                COL_66                COL_67               COL_68               COL_69               COL_70
-------  -------  -------  -----  -----  -----  -----  -----  -----  -----  -----  -----  ------  -----------  -----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  -------------------  -------------------  -------------------  -------------------

joe      A        jo         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      C        go         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      B        to         300   1000    400   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
pam      D        al         900   1000    900   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      D        so         200   1000    300   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      C        by         300   1000    500   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select pic_1,large_int from btsel08
+>      where large_int >= 100
+>      group by pic_1,large_int
+>      having large_int in (100,200,1000,2000);

PIC_1  LARGE_INT           
-----  --------------------

B                      2000
B                      1000
X                       200
E                       100

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select pic_x_5 from btsel09
+>         where pic_x_6 <> 11
+>      group by pic_x_5
+>      order by pic_x_5;

PIC_X_5
-------

      0
      3
      4
      5
      7

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select decimal_10 from btsel10
+>      group by decimal_10
+>      having decimal_10 in (90,100,110,10000,50000)
+>      order by decimal_10;

DECIMAL_10 
-----------

         90
        100
        110
      10000
      50000

--- 5 row(s) selected.
>>--    <end-input>
>>
>>
>>
>>
>>-- <testcase A6>
>>
>>--    <detail>
>>--    select normal test case - this tests having the SQL statement
>>--    spread over multiple lines and white space. Different variations
>>--    on the number of blank lines, postioning of tokens on lines, and
>>--    white space between tokens are tried. Variation of upper and lower
>>--    case letters in SQL keywords and table names are tryed to assure
>>--    both work equally.  These variations should not affect the
>>--    functionality of the SQL statement.
>>
>>
>>--    <templates>
>>--    US00
>>
>>--    <ufi-input>
>>      select
+>       char_1
+>       ,
+>        decimal_1
+>      from
+>       btsel01
+>      where
+>       (
+>       char_1
+>        like
+>        '%'
+>        )
+>        and
+>            (
+>            var_char
+>             in
+>              (
+>             'B'
+>             ,
+>             'jimmy'
+>             ,
+>             'thomas'
+>             ,
+>             'marilyn'
+>             ,
+>             'christopher'
+>             )
+>             )
+>        and
+>         (
+>         binary_32_u
+>          between
+>           50
+>            and
+>             100
+>             )
+>              and
+>            (
+>            decimal_1
+>                <
+>              decimal_2_signed
+>              )
+>      group
+>       by
+>        char_1
+>        ,
+>        decimal_1
+>      having
+>       decimal_1
+>        between
+>        2
+>         and
+>          8
+>      order
+>       by
+>        decimal_1
+>          DESC
+>      repeatable
+>       access
+>       ;

--- 0 row(s) selected.
>>--    <ufi-input>
>>
>>
>>      select distinct var_char,binary_64_s, pic_comp_1
+>
+>      from btsel01
+>
+>      where (char_1 like '%') and
+>
+>
+>            (var_char in ('B','jimmy','thomas','marilyn','christopher'))
+>
+>        and (binary_32_u between 50 and 100) and
+>
+>               (decimal_1 < decimal_2_signed)
+>
+>      group by binary_64_s,pic_comp_1, var_char
+>
+>      having binary_64_s > pic_comp_1
+>
+>      order by binary_64_s, 2 DESC
+>
+>      for repeatable access
+>
+>
+>      ;

--- 0 row(s) selected.
>>--    <ufi-input>
>>      select                          (new_name_1*new_name_1),new_name_3
+>      from                                                  pvsel03
+>      where                    (new_name_1 between new_name_2 and 100) and
+>(new_name_3 in ('A','B','7')) and
+>                                               (new_name_3 like '_') and
+>            (new_name_1 > (new_name_2 + 20))
+>group                                                                     by
+>            new_name_1,new_name_3
+>      having new_name_1=80
+>      order by new_name_3 ASC
+>      stable                                                        access;

--- 0 row(s) selected.
>>--    <ufi-input>
>>      SELECT new_name_1,new_name_4
+>      FROM svsel13
+>      WHERE ((var_char like 'b%') OR (var_char LIKE 'tom')) and
+>            (pic_comp_1 IN (200,300,400,500)) AND
+>            (new_name_1 BETWEEN 80 and 90)
+>      group BY new_name_1,new_name_4
+>      having new_name_1 = 80
+>      ORDER by new_name_1
+>
+>
+>      FOR
+>
+>
+>                              broWSE
+>
+>
+>
+>                                                                   access;

NEW_NAME_1  NEW_NAME_4          
----------  --------------------

        80    216172782159534338
        80    216172782159534342
        80    216172782159534343

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select medium_int,SYSKEY
+>      from btsel04
+>      where (medium_int > 200)
+>      GROUP BY medium_int,SYSKEY
+>      HAVING medium_int in (999,1000,2000)
+>      order by medium_int;

MEDIUM_INT   SYSKEY              
-----------  --------------------

        999    216172782152933122
       1000    216172782152933121
       1000    216172782152933127
       1000    216172782152933126
       2000    216172782152933125
       2000    216172782152933128

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select PIC_X_B from BTSEL05
+>      where COL_1 = 'lowry'
+>      group by PIC_X_B;

PIC_X_B
-------

D      
B      

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btSeL06
+>         where Col_6 < 90
+>      order by pIc_X_a,cOL_1;

PIC_X_A  PIC_X_B  PIC_X_C  COL_1  COL_2  COL_3  COL_4  COL_5  COL_6  COL_7  COL_8  COL_9  COL_10  COL_21       COL_22       COL_23       COL_24       COL_25       COL_26       COL_27      COL_28      COL_29      COL_30      COL_41       COL_42       COL_43       COL_44       COL_45      COL_46      COL_47      COL_48      COL_61                COL_62                COL_63                COL_64                COL_65                COL_66                COL_67               COL_68               COL_69               COL_70
-------  -------  -------  -----  -----  -----  -----  -----  -----  -----  -----  -----  ------  -----------  -----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  -------------------  -------------------  -------------------  -------------------

joe      A        jo         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      C        go         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      B        to         300   1000    400   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
pam      D        al         900   1000    900   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      D        so         200   1000    300   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      C        by         300   1000    500   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000

--- 6 row(s) selected.
>>
>>--    <comment> test use of space between > and = in the >= sign
>>--    <ufi-input>
>>      select pic_1,large_int from btsel08
+>where large_int >= 100 group by pic_1,large_int having large_int in
+>      (100,200,1000,2000);

PIC_1  LARGE_INT           
-----  --------------------

B                      2000
B                      1000
X                       200
E                       100

--- 4 row(s) selected.
>>--    <ufi-input>
>>   select pic_x_5 from btsel09 where pic_x_6 <> 11
+>      group by pic_x_5 order by pic_x_5;

PIC_X_5
-------

      0
      3
      4
      5
      7

--- 5 row(s) selected.
>>--    <ufi-input>
>>select decimal_10 from btsel10
+>group by decimal_10
+>having decimal_10 in
+>(
+>90,100,110,10000,50000
+>)
+>order by decimal_10;

DECIMAL_10 
-----------

         90
        100
        110
      10000
      50000

--- 5 row(s) selected.
>>--    <end-input>
>>
>>
>>
>>-- <testcase A7>
>>
>>--    <detail>
>>--    select normal test case - this tests the selection
>>--    of aggregate functions (COUNT, AVG, MAX, MIN, SUM). Aggregates
>>--    are tested in the SELECT clause in conjunction with GROUP BY
>>--    clauses.
>>
>>--    <templates>
>>--    US00
>>
>>--    <comment> select ALL
>>--    <ufi-input>
>>      select binary_64_s,count(*)
+>      from btsel01
+>      group by binary_64_s;

BINARY_64_S            (EXPR)              
---------------------  --------------------

             2000.000                     4
              200.000                     1
             1200.000                     2
             1500.000                     1

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select avg(large_int)
+>      from pvsel01
+>      group by medium_int;

(EXPR)              
--------------------

             -333600
           500000500
                 999
                2000
                   0

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select binary_64_s, pic_comp_1, min(ALL decimal_2_signed)
+>      from btsel01
+>      group by binary_64_s,pic_comp_1;

BINARY_64_S            PIC_COMP_1            (EXPR)
---------------------  --------------------  ------

             2000.000                   500     .70
              200.000                    50     .50
             1200.000                    60     .60
             1500.000                   500     .80
             1200.000                  3000     .90
             2000.000                    50     .99

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select new_name_2, sum(new_name_2)
+>      from pvsel03
+>      group by new_name_2;

NEW_NAME_2   (EXPR)               
-----------  ---------------------

       5.00                   5.00
       6.00                  18.00

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select pic_comp_1,min(var_char)
+>      from svsel13
+>      group by pic_comp_1;

PIC_COMP_1            (EXPR)
--------------------  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                 200  tom                                                                                                                                                                                                                                                        
                 300  bob                                                                                                                                                                                                                                                        
                 500  bill                                                                                                                                                                                                                                                       

--- 3 row(s) selected.
>>
>>--    <comment> select DISTINCT
>>--    <ufi-input>
>>      select pic_comp_1,AVG(distinct binary_32_u)
+>      from btsel01
+>      group by pic_comp_1;

PIC_COMP_1            (EXPR)               
--------------------  ---------------------

                 500                  80.00
                  50                  45.00
                  60                  60.00
                3000                  90.00

--- 4 row(s) selected.
>>
>>--    <ufi-input>
>>      select new_name_1,count(distinct var_char)
+>      from svsel13
+>      group by new_name_1;

NEW_NAME_1  (EXPR)              
----------  --------------------

        90                     1
        80                     2

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select binary_64_s, min(distinct var_char)
+>      from btsel01
+>      group by binary_64_s;

BINARY_64_S            (EXPR)
---------------------  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

             2000.000  marilyn                                                                                                                                                                                                                                                      
              200.000  B                                                                                                                                                                                                                                                            
             1200.000  B                                                                                                                                                                                                                                                            
             1500.000  jimmy                                                                                                                                                                                                                                                        

--- 4 row(s) selected.
>>
>>--    <comment> select an expression inside an aggregate
>>--    <ufi-input>
>>      select binary_64_s, avg(ALL binary_32_u + pic_comp_1)
+>      from btsel01
+>      group by binary_64_s;

BINARY_64_S            (EXPR)               
---------------------  ---------------------

             2000.000                 457.50
              200.000                 100.00
             1200.000                1605.00
             1500.000                 580.00

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select medium_int, min(ALL large_int * small_int)
+>      from pvsel01
+>      group by medium_int;

MEDIUM_INT  (EXPR)              
----------  --------------------

      8000           -2000000000
     10000                 90000
      9000                 79920
      1000              18000000
      5000                     0

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select medium_int, min(ALL large_int * medium_int)
+>      from pvsel01
+>      group by medium_int;

MEDIUM_INT  (EXPR)              
----------  --------------------

      8000           -8000000000
     10000              10000000
      9000               8991000
      1000               2000000
      5000                     0

--- 5 row(s) selected.
>>
>>--    <comment> select expression of aggregates
>>--    <ufi-input>
>>      select medium_int,(sum(pic_decimal_3) + sum(small_int)) / 100
+>      from pvsel01
+>      group by medium_int;

MEDIUM_INT  (EXPR)              
----------  --------------------

      8000                    31
     10000                     1
      9000                     0
      1000                    90
      5000                    80

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select medium_int,(sum(pic_decimal_3) + sum(medium_int)) / 100
+>      from pvsel01
+>      group by medium_int;

MEDIUM_INT  (EXPR)              
----------  --------------------

      8000                   240
     10000                   200
      9000                    90
      1000                    10
      5000                    50

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select pic_comp_1,avg(pic_comp_1 ) + sum(pic_comp_1)
+>      from svsel13
+>      group by pic_comp_1;

PIC_COMP_1            (EXPR)              
--------------------  --------------------

                 200                   600
                 300                   600
                 500                  1000

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select medium_int,sum(decimal_1) + sum(decimal_1)
+>      from btsel01
+>      group by medium_int;

MEDIUM_INT  (EXPR)              
----------  --------------------

      8000                    26
     10000                    22
      9000                    10
      1000                    16
      5000                    18

--- 5 row(s) selected.
>>
>>--    <ufi-input>
>>      select medium_int,
+>             sum(pic_decimal_3),avg(pic_decimal_3),max(pic_decimal_3),
+>             min(pic_decimal_3),count(distinct pic_decimal_3),count(*)
+>      from pvsel01
+>      group by medium_int;

MEDIUM_INT  (EXPR)                (EXPR)                (EXPR)  (EXPR)  (EXPR)                (EXPR)
----------  --------------------  --------------------  ------  ------  --------------------  --------------------

      8000                    13                     4       6       2                     3                     3
     10000                     4                     2       3       1                     2                     2
      9000                     4                     4       4       4                     1                     1
      1000                     7                     7       7       7                     1                     1
      5000                     8                     8       8       8                     1                     1

--- 5 row(s) selected.
>>
>>--    <comment> queries with where predicates
>>--    <ufi-input>
>>      select binary_64_s, count(*)
+>      from btsel01
+>      where var_char = 'thomas'
+>      group by binary_64_s;

BINARY_64_S            (EXPR)              
---------------------  --------------------

             2000.000                     3

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select pic_comp_1, SUM(distinct binary_64_s)
+>      from btsel01
+>      where binary_32_u > 50
+>      group by pic_comp_1;

PIC_COMP_1            (EXPR)               
--------------------  ---------------------

                 500               3500.000
                  60               1200.000
                3000               1200.000

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select new_name_1, count(distinct var_char)
+>      from svsel13
+>      where (new_name_1 = 80) or (new_name_2 = 5)
+>      group by new_name_1;

NEW_NAME_1  (EXPR)              
----------  --------------------

        90                     1
        80                     2

--- 2 row(s) selected.
>>
>>--    <ufi-input>
>>      select pic_comp_1, avg(ALL binary_32_u + pic_comp_1)
+>      from btsel01
+>         where (pic_comp_1 <> 60)
+>      group by pic_comp_1;

PIC_COMP_1            (EXPR)               
--------------------  ---------------------

                 500                 580.00
                  50                  95.00
                3000                3090.00

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select binary_64_s, sum(decimal_1) + sum(decimal_1)
+>      from btsel01
+>         where decimal_3_unsigned <> 110
+>      group by binary_64_s;

BINARY_64_S            (EXPR)              
---------------------  --------------------

             2000.000                    30
              200.000                     8
             1200.000                    12
             1500.000                    10

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select medium_int,avg(large_int)
+>      from pvsel01
+>         where small_int <> 8000
+>      group by medium_int;

MEDIUM_INT  (EXPR)              
----------  --------------------

      8000               -333600
     10000             500000500
      9000                   999
      1000                  2000

--- 4 row(s) selected.
>>
>>--    <comment> select aggregate, group by SYSKEY
>>--    <ufi-input>
>>      select avg(pic_comp_1)
+>      from btsel04
+>      group by SYSKEY;

(EXPR)              
--------------------

                 200
                 300
                 100
                 100
                 500
                1000
                2000
                3000

--- 8 row(s) selected.
>>--    <end-input>
>>
>>
>>
>>-- <testcase A8>
>>
>>--    <detail>
>>--    select normal test case - this tests the selection of aggregate
>>--    functions in HAVING clauses in conjunction with GROUP BY. Aggregates
>>--    are also tested in some select statements with all clauses and locking
>>--    clauses.
>>
>>
>>
>>--    <templates>
>>--    US00
>>
>>--    <comment> test use of a HAVING clause with no GROUP BY clause
>>--    <ufi-input>
>>      select max(binary_32_u)
+>      from btsel01
+>      having sum(pic_decimal_3) > 30;

(EXPR)     
-----------

      90.00

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select avg(pic_decimal_3)
+>      from btsel01
+>      having avg(pic_decimal_3) > 4;

(EXPR)              
--------------------

                   4

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select count(*)
+>      from pvsel01
+>         having min(small_int) <= 10;

(EXPR)              
--------------------

                   8

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select sum(col_4)
+>      from svsel11
+>      having count(*) = 8;

--- 0 row(s) selected.
>>
>>--    <comment> test select from 0 groups
>>--    <ufi-input>
>>      select max(binary_64_s), min(binary_64_s)
+>      from btsel03
+>      group by pic_9_7
+>      having pic_9_7 > 200;

--- 0 row(s) selected.
>>
>>--    <ufi-input>
>>      select binary_64_s,count(*)
+>      from btsel01
+>      group by binary_64_s
+>         having sum(distinct pic_comp_1) < 1000;

BINARY_64_S            (EXPR)              
---------------------  --------------------

             2000.000                     4
              200.000                     1
             1500.000                     1

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select binary_64_s,count(*)
+>      from btsel01
+>      group by binary_64_s
+>         having binary_64_s <> 200;

BINARY_64_S            (EXPR)              
---------------------  --------------------

             2000.000                     4
             1200.000                     2
             1500.000                     1

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select binary_64_s,count(*)
+>      from btsel01
+>      group by binary_64_s
+>      having sum(binary_64_s) > 2000;

BINARY_64_S            (EXPR)              
---------------------  --------------------

             2000.000                     4
             1200.000                     2

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select binary_64_s
+>      from btsel01
+>      group by binary_64_s
+>      having sum(binary_64_s) > 2000;

BINARY_64_S          
---------------------

             2000.000
             1200.000

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select binary_64_s,count(*)
+>      from btsel01
+>      group by binary_64_s
+>      having count(*) >= 2;

BINARY_64_S            (EXPR)              
---------------------  --------------------

             2000.000                     4
             1200.000                     2

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select avg(large_int)
+>      from pvsel01
+>      group by medium_int
+>      having sum(large_int) > 0;

(EXPR)              
--------------------

           500000500
                 999
                2000

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select binary_64_s, pic_comp_1, min(ALL decimal_2_signed)
+>      from btsel01
+>      group by binary_64_s, pic_comp_1
+>      having max(binary_32_u) >= 50;

BINARY_64_S            PIC_COMP_1            (EXPR)
---------------------  --------------------  ------

             2000.000                   500     .70
              200.000                    50     .50
             1200.000                    60     .60
             1500.000                   500     .80
             1200.000                  3000     .90

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select new_name_2, sum(new_name_2)
+>      from pvsel03
+>      group by new_name_2
+>      having (sum(new_name_1) + min(new_name_1)) * 2 > 400;

NEW_NAME_2   (EXPR)               
-----------  ---------------------

       6.00                  18.00

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select pic_comp_1, min(var_char)
+>      from svsel13
+>      group by pic_comp_1
+>         having min(new_name_1) + min(new_name_2) <> 86;

PIC_COMP_1            (EXPR)
--------------------  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                 300  bob                                                                                                                                                                                                                                                        

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select pic_comp_1, AVG(distinct binary_32_u)
+>      from btsel01
+>      group by pic_comp_1
+>         having avg(pic_comp_2) < 0.5;

PIC_COMP_1            (EXPR)               
--------------------  ---------------------

                 500                  80.00
                  50                  45.00
                3000                  90.00

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select new_name_1
+>      from svsel13
+>      group by new_name_1
+>      having sum(new_name_2) in (5,18);

NEW_NAME_1
----------

        80
        90

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select binary_64_s, avg(ALL binary_32_u + pic_comp_1)
+>      from btsel01
+>      group by binary_64_s
+>      having avg(ALL binary_32_u + pic_comp_1) > 100;

BINARY_64_S            (EXPR)               
---------------------  ---------------------

             2000.000                 457.50
             1200.000                1605.00
             1500.000                 580.00

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select binary_64_s
+>      from btsel01
+>      group by binary_64_s
+>      having avg(ALL binary_32_u + pic_comp_1) > 100;

BINARY_64_S          
---------------------

             2000.000
             1200.000
             1500.000

--- 3 row(s) selected.
>>
>>--    <comment> queries with where predicates
>>--    <ufi-input>
>>      select pic_comp_1, SUM(distinct binary_64_s)
+>      from btsel01
+>      where binary_32_u > 50
+>      group by pic_comp_1
+>      having sum(binary_signed) > 5000;

PIC_COMP_1            (EXPR)               
--------------------  ---------------------

                 500               3500.000

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select new_name_1, count(distinct var_char)
+>      from svsel13
+>      where (new_name_1 = 80) or (new_name_2 = 5)
+>      group by new_name_1
+>      having avg(new_name_2) in (5,6);

NEW_NAME_1  (EXPR)              
----------  --------------------

        90                     1
        80                     2

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select pic_comp_1, avg(ALL binary_32_u + pic_comp_1)
+>      from btsel01
+>         where (pic_comp_1 <> 60)
+>      group by pic_comp_1
+>         having min(binary_signed) < 1000;

PIC_COMP_1            (EXPR)               
--------------------  ---------------------

                 500                 580.00
                  50                  95.00

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select binary_64_s, sum(decimal_1) + sum(decimal_1)
+>      from btsel01
+>         where decimal_3_unsigned <> 110
+>      group by binary_64_s
+>      having (max(pic_comp_2) between 0.2 and 0.99)
+>            and (binary_64_s <> 1200);

BINARY_64_S            (EXPR)              
---------------------  --------------------

             2000.000                    30
             1500.000                    10

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select medium_int, avg(large_int)
+>      from pvsel01
+>         where small_int <> 8000
+>      group by medium_int
+>      having max(pic_x_1) <> 'E';

MEDIUM_INT  (EXPR)              
----------  --------------------

      8000               -333600
     10000             500000500
      9000                   999

--- 3 row(s) selected.
>>
>>--    <comment> complex queries
>>--    <ufi-input>
>>      select pic_comp_1, SUM(distinct binary_64_s)
+>      from btsel01
+>      where binary_32_u > 50
+>      group by pic_comp_1
+>      having sum(binary_signed) > 5000
+>      order by pic_comp_1, 2
+>      for repeatable access;

PIC_COMP_1            (EXPR)               
--------------------  ---------------------

                 500               3500.000

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select new_name_1, count(distinct var_char)
+>      from svsel13
+>      where (new_name_1 = 80) or (new_name_2 = 5)
+>      group by new_name_1
+>      having avg(new_name_2) in (5,6)
+>      order by 1, 2 ASC
+>      for stable access;

NEW_NAME_1  (EXPR)              
----------  --------------------

        80                     2
        90                     1

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select pic_comp_1, avg(ALL binary_32_u + pic_comp_1)
+>      from btsel01
+>         where (pic_comp_1 <> 60)
+>      group by pic_comp_1
+>         having min(binary_signed) < 1000
+>      order by pic_comp_1;

PIC_COMP_1            (EXPR)               
--------------------  ---------------------

                  50                  95.00
                 500                 580.00

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select binary_64_s, sum(decimal_1) + sum(decimal_1)
+>      from btsel01
+>         where decimal_3_unsigned <> 110
+>      and pic_comp_2 between 0.2 and 0.99
+>      group by binary_64_s
+>      order by 2;

BINARY_64_S            (EXPR)              
---------------------  --------------------

             1500.000                    10
             1200.000                    12
             2000.000                    30

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select medium_int, avg(large_int)
+>      from pvsel01
+>         where small_int <> 8000
+>      group by medium_int
+>      having max(pic_x_1) <> 'E'
+>      order by medium_int
+>      for browse access;

MEDIUM_INT  (EXPR)              
----------  --------------------

      8000               -333600
      9000                   999
     10000             500000500

--- 3 row(s) selected.
>>
>>--    <comment> select having aggregate(SYSKEY)
>>--    <ufi-input>
>>      select pic_comp_1
+>      from btsel04
+>      group by pic_comp_1
+>      having avg(SYSKEY) > 0;

PIC_COMP_1          
--------------------

                 200
                 300
                 100
                 500
                1000
                2000
                3000

--- 7 row(s) selected.
>>
>>-- <testcase A9>
>>
>>--    <detail>
>>--    normal select test - this tests the use of ordinal numbers in
>>--    a GROUP BY clause of a select statement.
>>
>>--    <templates>
>>--    US00
>>
>>--    <ufi-input>
>>      select char_1,decimal_1 from btsel01
+>	group by char_1, decimal_1;

CHAR_1  DECIMAL_1
------  ---------

D               7
A               4
A               5
D               5
E               1
C               8
C               9

--- 7 row(s) selected.
>>
>>--    <ufi-input>
>>      select pic_x_1 from btsel02
+>	group by pic_x_1;

PIC_X_1
-------

B      
C      
Q      

--- 3 row(s) selected.
>>
>>--    <ufi-input>
>>      select pic_x_7,pic_9_7 from btsel03
+>	group by pic_9_7, pic_x_7;

PIC_X_7  PIC_9_7
-------  -------

A             90
7             80
8             80
5            100
michael       50
B             80

--- 6 row(s) selected.
>>
>>--     try grouping by same column twice, column name adjacent to itself
>>--    <ufi-input>
>>      select pic_x_a,col_1,col_3 from btsel06
+>	group by pic_x_a, col_1, col_1, col_3;

PIC_X_A  COL_1  COL_3
-------  -----  -----

joe        100    200
joe        300    400
joe        100    100
pam        900    900
sue        300    500
sue        200    300
sue        200    100

--- 7 row(s) selected.
>>
>>--     try grouping by same column twice, column not adjacent to itself
>>--    <ufi-input>
>>      select pic_x_a,col_1,col_3 from btsel06
+>	group by pic_x_a, col_1, col_3, col_1;

PIC_X_A  COL_1  COL_3
-------  -----  -----

joe        100    200
joe        300    400
joe        100    100
pam        900    900
sue        300    500
sue        200    300
sue        200    100

--- 7 row(s) selected.
>>
>>--    <ufi-input>
>>      select * from (select medium_int * 2, binary_64_s + 100
+>      from btsel01)t(a,b)
+>	group by a,b;

A                     B                    
--------------------  ---------------------

               16000                2100.00
               20000                 300.00
               16000                1300.00
               18000                1600.00
                2000                2100.00
               10000                2100.00
               20000                2100.00

--- 7 row(s) selected.
>>
>>--    <ufi-input>
>>      select * from 
+>      (select medium_int * 2, binary_64_s
+>      from btsel01) t(a,b)
+>      group by a,b;

A                     B                    
--------------------  ---------------------

               16000               2000.000
               20000                200.000
               16000               1200.000
               18000               1500.000
                2000               2000.000
               10000               2000.000
               20000               2000.000

--- 7 row(s) selected.
>>
>>--    <ufi-input>
>>      select medium_int, sum(pic_comp_1)
+>      from btsel01
+>      group by medium_int;

MEDIUM_INT  (EXPR)              
----------  --------------------

      8000                  3560
     10000                   550
      9000                   500
      1000                   500
      5000                    50

--- 5 row(s) selected.
>>
>>--    <ufi-input>
>>      select * from
+>      (select medium_int * binary_64_s
+>      from btsel01)t(a)
+>      group by a;

A                   
--------------------

            16000000
             2000000
             9600000
            13500000
            10000000
            20000000

--- 6 row(s) selected.
>>
>>--    <ufi-input>
>>      select a,sum(a) from
+>      (select medium_int * binary_64_s
+>      from btsel01) t(a)
+>      group by a;

A                     (EXPR)               
--------------------  ---------------------

             9600000           19200000.000
            10000000           10000000.000
            20000000           20000000.000
            16000000           16000000.000
             2000000            4000000.000
            13500000           13500000.000

--- 6 row(s) selected.
>>
>>--    <ufi-input>
>>      select a,sum(b) from
+>      (select medium_int * binary_64_s, medium_int
+>      from btsel01) t(a,b)
+>      group by a;

A                     (EXPR)              
--------------------  --------------------

             9600000                 16000
            10000000                  5000
            20000000                 10000
            16000000                  8000
             2000000                 11000
            13500000                  9000

--- 6 row(s) selected.
>>
>>
>>--    <end-input>
>>
>>-- <end-test>
>>LOG;
