>>---------------------------------------------------------------------
>>-- 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 where clauses (SELECT - FROM - WHERE).
>>--    This test unit uses the select global data base (<subvol>).
>>-- Each test case in this test unit tests a particular feature of
>>-- select statements with where clauses.  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: SELECT WHERE columns are compared against a
>>--                    constant.
>>--       testcase A1: SELECT WHERE columns are compared with other
>>--                    columns or with expressions.
>>--       testcase A2: SELECT WHERE column or expression is BETWEEN
>>--                    other columns or values or expressions.
>>--       testcase A3: SELECT WHERE column or expression is IN an
>>--                    expression list
>>--       testcase A4: SELECT WHERE column is LIKE a constant
>>--       testcase A5: SELECT WHERE column is LIKE a constant,
>>--                    using an ESCAPE character
>>--       testcase A6: use of for browse access, for stable access
>>--         and for repeatable access
>>--       testcase A7: SELECT aggregate functions with where predicate
>>
>>-- All testcases are documented further below.
>>
>>-- ***********************  end test unit comments     ***********************
>>
>>-- <testunit-summary>
>>-- select #2 pos/loc ufi norm where clause
>>
>>
>>-- <testunit-specs>
>>
>>-- mode-type ufi
>>-- test-type functional
>>-- form-type pos/loc
>>-- select-test
>>
>>-- <testcase A0>
>>
>>--    <detail>
>>--       normal where clause select statement test case - this tests
>>--       the use of a where predicate that compares a column with
>>--       a constant.
>>
>>--    <switches>
>>--       pat-file uoutpat <mypat>
>>
>>--    <templates>
>>--       US00
>>
>>--    <ufi-input>
>>      select * from btsel01
+>      where char_1 = 'A';

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

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel01
+>      where 'A' <> var_char;

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
------  ----------  -------  -------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------  -----------  ---------------------  --------------------  ----------  ------------  ---------  ----------  --------------------  ---------  ----------------  ------------------  -------------  -------------  -------------

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
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
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
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
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       steven      B        9        bat                                                                                                                                                                                                       thomas                                                                                                                                                                                                                                                                  8000        70.00               2000.000                   500         .10     100.99900         90       10000                  1000          7               .70                 110            3.1           .300              3

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel01
+>      where not ('A' = var_char);

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
------  ----------  -------  -------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------  -----------  ---------------------  --------------------  ----------  ------------  ---------  ----------  --------------------  ---------  ----------------  ------------------  -------------  -------------  -------------

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
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
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
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
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       steven      B        9        bat                                                                                                                                                                                                       thomas                                                                                                                                                                                                                                                                  8000        70.00               2000.000                   500         .10     100.99900         90       10000                  1000          7               .70                 110            3.1           .300              3

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel01
+>      where binary_32_u < 100;

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
------  ----------  -------  -------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------  -----------  ---------------------  --------------------  ----------  ------------  ---------  ----------  --------------------  ---------  ----------------  ------------------  -------------  -------------  -------------

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
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
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
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
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       steven      B        9        bat                                                                                                                                                                                                       thomas                                                                                                                                                                                                                                                                  8000        70.00               2000.000                   500         .10     100.99900         90       10000                  1000          7               .70                 110            3.1           .300              3

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel01
+>      where decimal_3_unsigned <= 100;

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
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

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel02
+>      where pic_x_1 > 'A';

PIC_X_1
-------

B      
C      
B      
Q      
B      
C      
Q      
Q      

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel03
+>      where binary_32_u >= 5.0;

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

A               5.00  make           90                 200.00
7               6.00  joe            80                1200.00
8               6.00  joe            80                1200.00
5            1000.00  5             100                1000.00
michael        50.00  dave           50                1500.00
7               6.00  john           80                2000.00
B               6.00  mark           80                3000.00
michael        70.00  joan           50                4000.00

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel03
+>      where pic_9_7 = 80;

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

7               6.00  joe            80                1200.00
8               6.00  joe            80                1200.00
7               6.00  john           80                2000.00
B               6.00  mark           80                3000.00

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel03
+>      where pic_x_7 <> '8';

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

A               5.00  make           90                 200.00
7               6.00  joe            80                1200.00
5            1000.00  5             100                1000.00
michael        50.00  dave           50                1500.00
7               6.00  john           80                2000.00
B               6.00  mark           80                3000.00
michael        70.00  joan           50                4000.00

--- 7 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel04
+>      where var_char < 'pam';

VAR_CHAR                                                                                                                                                                                                                                                     MEDIUM_INT   PIC_X_7  PIC_COMP_1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  -------  --------------------

bob                                                                                                                                                                                                                                                                  999  A                         300
bobby                                                                                                                                                                                                                                                                200  sue                       100
bill                                                                                                                                                                                                                                                                2000  B                         500
christopher                                                                                                                                                                                                                                                         1000  white                    1000
marilyn                                                                                                                                                                                                                                                             2000  green                    3000

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel04
+>      where medium_int <= 1000;

VAR_CHAR                                                                                                                                                                                                                                                     MEDIUM_INT   PIC_X_7  PIC_COMP_1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  -------  --------------------

tom                                                                                                                                                                                                                                                                 1000  7                         200
bob                                                                                                                                                                                                                                                                  999  A                         300
sue                                                                                                                                                                                                                                                                  200  sue                       100
bobby                                                                                                                                                                                                                                                                200  sue                       100
christopher                                                                                                                                                                                                                                                         1000  white                    1000
william                                                                                                                                                                                                                                                             1000  black                    2000

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel04
+>      where pic_x_7 <= '8';

VAR_CHAR                                                                                                                                                                                                                                                     MEDIUM_INT   PIC_X_7  PIC_COMP_1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  -------  --------------------

tom                                                                                                                                                                                                                                                                 1000  7                         200

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel05
+>      where pic_x_a > 'abe';

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 
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  
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  
why      B        so       lowry  amy    squaw  tammy  junk   junk   junk   junk   junk   junk  
not      B        on       lowry  mary   mogul  pammy  junk   junk   junk   junk   junk   junk  

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel05
+>      where col_9 > 'abe';

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 
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  
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  
why      B        so       lowry  amy    squaw  tammy  junk   junk   junk   junk   junk   junk  
not      B        on       lowry  mary   mogul  pammy  junk   junk   junk   junk   junk   junk  

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel06
+>      where col_1 >= 100;

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      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
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      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
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      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
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      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

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel06
+>      where 100 = col_7;

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
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

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel06
+>      where 'joe' <> pic_x_a;

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
-------  -------  -------  -----  -----  -----  -----  -----  -----  -----  -----  -----  ------  -----------  -----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  -------------------  -------------------  -------------------  -------------------

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      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
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      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

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel07
+>      where pic_x_b < 'Q';

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

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

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel08
+>      where large_int <= 1000;

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

                1000  harveys                                                                                                                                                                                                                                                       B    
                 400  joseph                                                                                                                                                                                                                                                        X    
                 300  Q                                                                                                                                                                                                                                                             X    
                 200  squaw                                                                                                                                                                                                                                                         X    
                 100  carltons                                                                                                                                                                                                                                                      E    

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel08
+>      where pic_1 > 'C';

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

                4000  valley                                                                                                                                                                                                                                                        D    
                3000  george                                                                                                                                                                                                                                                        D    
                 400  joseph                                                                                                                                                                                                                                                        X    
                 300  Q                                                                                                                                                                                                                                                             X    
                 200  squaw                                                                                                                                                                                                                                                         X    
                 100  carltons                                                                                                                                                                                                                                                      E    

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel09
+>      where pic_x_4 >= 'JOE';

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

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

--- 10 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel09
+>      where pic_x_6 = 10;

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

sue      mary     Z        MARY           3       10       20
deb      bill     Y        BILL           4       10       30

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel10
+>      where pic_x_a <> 'tom';

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
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

--- 7 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel10
+>      where binary_32_signed < 0;

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

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel10
+>      where decimal_10 <= 100;

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
rod      for          400  inside               25               60                 0

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel11
+>      where pic_x_a > 'bob';

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

boe      tommy        200  ebedafiih           200               50                50
car      who          400  done                160              500               500
jar      how          600  inside              160              500               500
dan      sue          100  ebediih             100              100                60
tar      what         500  none                100             8888                 0
mar      why           50  house                50             7777                 0

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel11
+>      where binary_unsigned >= 100;

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

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

--- 7 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel11
+>      where pic_9_7 = 100;

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

bob      bob          100  abadefih            160              200               200
dan      sue          100  ebediih             100              100                60

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from pvsel01
+>      where binary_signed > 50;

BINARY_SIGNED  LARGE_INT             MEDIUM_INT  PIC_DECIMAL_3  PIC_X_1  PIC_X_7  SMALL_INT
-------------  --------------------  ----------  -------------  -------  -------  ---------

           60                 -1000        8000              2  A        bobby         1000
         1000                   999        9000              4  C        7               80
         2000              -1000000        8000              5  Q        sue           2000
         3000                  2000        1000              7  E        jimmy         9000
         4000                     0        5000              8  Z        johnny        8000
         8000                  1000       10000              3  B        9               90

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select * from svsel11
+>      where col_1 > 50;

COL_1   COL_2                 COL_3       COL_4  COL_5  COL_6    COL_7   COL_8
------  --------------------  ----------  -----  -----  -------  ------  -------

    60                 -1000        8000      2  A      bobby      1000  7      
  2000              -1000000        8000      5  Q      sue        2000  7      
    60                 -1000        8000      2  A      bobby      1000  8      
  2000              -1000000        8000      5  Q      sue        2000  8      
  1000                   999        9000      4  C      7            80  michael
  3000                  2000        1000      7  E      jimmy      9000  7      
  8000                  1000       10000      3  B      9            90  7      
  4000                     0        5000      8  Z      johnny     8000  7      

--- 8 row(s) selected.
>>
>>
>>--    <comment> *** test use of SYSKEY in the where clause ***
>>--    <ufi-input>
>>      select * from btsel02
+>      where SYSKEY <> 0;

PIC_X_1
-------

B      
C      
B      
Q      
B      
C      
Q      
Q      

--- 8 row(s) selected.
>>
>>--    <comment> *** new_name_4 is SYSKEY in these views ***
>>--    <ufi-input>
>>      select * from pvsel03
+>      where new_name_4 <> 0;

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

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

--- 4 row(s) selected.
>>
>>--    <ufi-input>
>>      select * from svsel13
+>      where new_name_4 <> 0;

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

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

--- 4 row(s) selected.
>>--    <end-input>
>>
>>-- <testcase A1>
>>
>>--    <detail>
>>--    normal where clause select statement - this tests the use
>>--    of a where predicate where columns are compared.
>>
>>--    <switches>
>>--    pat-file uoutpat <mypat>
>>
>>--    <templates>
>>--    US00
>>
>>--    <ufi-input>
>>      select * from btsel01
+>      where binary_32_u = pic_comp_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

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel01
+>      where decimal_1 <> decimal_3_unsigned;

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
------  ----------  -------  -------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------  -----------  ---------------------  --------------------  ----------  ------------  ---------  ----------  --------------------  ---------  ----------------  ------------------  -------------  -------------  -------------

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
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
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
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
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       steven      B        9        bat                                                                                                                                                                                                       thomas                                                                                                                                                                                                                                                                  8000        70.00               2000.000                   500         .10     100.99900         90       10000                  1000          7               .70                 110            3.1           .300              3

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel01
+>      where not (decimal_1 = decimal_3_unsigned);

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
------  ----------  -------  -------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------  -----------  ---------------------  --------------------  ----------  ------------  ---------  ----------  --------------------  ---------  ----------------  ------------------  -------------  -------------  -------------

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
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
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
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
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       steven      B        9        bat                                                                                                                                                                                                       thomas                                                                                                                                                                                                                                                                  8000        70.00               2000.000                   500         .10     100.99900         90       10000                  1000          7               .70                 110            3.1           .300              3

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel01
+>      where decimal_3_unsigned < binary_64_s;

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
------  ----------  -------  -------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------  -----------  ---------------------  --------------------  ----------  ------------  ---------  ----------  --------------------  ---------  ----------------  ------------------  -------------  -------------  -------------

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
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
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
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
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       steven      B        9        bat                                                                                                                                                                                                       thomas                                                                                                                                                                                                                                                                  8000        70.00               2000.000                   500         .10     100.99900         90       10000                  1000          7               .70                 110            3.1           .300              3

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel01
+>      where char_10 <= pic_x_7;

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
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

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel01
+>      where var_char > char_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
------  ----------  -------  -------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------  -----------  ---------------------  --------------------  ----------  ------------  ---------  ----------  --------------------  ---------  ----------------  ------------------  -------------  -------------  -------------

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
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
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
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
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       steven      B        9        bat                                                                                                                                                                                                       thomas                                                                                                                                                                                                                                                                  8000        70.00               2000.000                   500         .10     100.99900         90       10000                  1000          7               .70                 110            3.1           .300              3

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel03
+>      where binary_64_s >= binary_32_u;

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

A               5.00  make           90                 200.00
7               6.00  joe            80                1200.00
8               6.00  joe            80                1200.00
5            1000.00  5             100                1000.00
michael        50.00  dave           50                1500.00
7               6.00  john           80                2000.00
B               6.00  mark           80                3000.00
michael        70.00  joan           50                4000.00

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel03
+>      where pic_9_7 = (binary_64_s - 1120);

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

7               6.00  joe            80                1200.00
8               6.00  joe            80                1200.00

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel03
+>      where not (pic_x_7 <> pic_x4_a);

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

5            1000.00  5             100                1000.00

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel04
+>      where (pic_comp_1 < medium_int) and
+>      (var_char <= pic_x_7);

VAR_CHAR                                                                                                                                                                                                                                                     MEDIUM_INT   PIC_X_7  PIC_COMP_1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  -------  --------------------

sue                                                                                                                                                                                                                                                                  200  sue                       100
bobby                                                                                                                                                                                                                                                                200  sue                       100

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel05
+>      where (col_1 > col_2) and
+>            (col_3 >= col_4) and
+>            (col_5 = col_6) and
+>            (col_9 <> col_10);

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 

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel05
+>      where (col_3 < col_2) and
+>            (col_4 <= col_5) and
+>            (col_7 > col_6);

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 

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel05
+>      where ((col_8 >= col_9) or
+>            (col_9 = pic_x_a)) and
+>            (col_7 = col_7);

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 
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  
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  
why      B        so       lowry  amy    squaw  tammy  junk   junk   junk   junk   junk   junk  
not      B        on       lowry  mary   mogul  pammy  junk   junk   junk   junk   junk   junk  

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel06
+>      where (col_1 < col_2) and
+>            (col_3 <= col_4) and
+>            (col_5 > col_6) and
+>            (col_7 >= col_8) and
+>            ((col_9 * 2) = col_10);

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
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

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel06
+>      where (col_2 < col_22) and
+>            (col_23 <= col_24) and
+>            (col_25 > col_26) and
+>            (col_27 >= col_28) and
+>            ((col_29 * 2) = col_30);

--- 0 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel06
+>      where (col_22 <> col_23) and
+>            (col_24 <= col_25) and
+>            ((col_26 * 2) = (col_27 + 100)) and
+>            ((col_28>col_29) or (col_29 > col_28));

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
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

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel06
+>      where (col_41 < col_42) and
+>            (col_43 <= col_44) and
+>            (col_45 > col_46) and
+>            (col_47 >= col_48) ;

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
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

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel06
+>      where (col_42 <> col_43) and
+>            (col_44 <= col_45) and
+>            ((col_46 * 2) = (col_47 + 100));

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
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

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel06
+>      where (col_61 < col_62) and
+>            (col_63 <= col_64) and
+>            (col_65 > col_66) and
+>            (col_67 >= col_68) and
+>            ((col_69 * 2) = col_70);

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
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

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel06
+>      where (col_62 <> col_63) and
+>            (col_64 <= col_65) and
+>            ((col_66 * 2) = (col_67 + 100)) and
+>            ((col_68 > col_69) or (col_69 > col_68));

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
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

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel07
+>      where pic_x_a = pic_x_c;

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

P        P        P      
al       F        al     
al       Q        al     
jo       Z        jo     

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel08
+>      where (pic_252 > pic_1) or
+>            (not (pic_252 > pic_1));

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

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

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel09
+>      where (pic_x_2 = pic_x_4) or
+>            (pic_x_6 = pic_x_7);

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

can      come     X        TED            7       77       77
_t%      junk     j        _%             0        0        0
\ts      t_go     j        to%go          0        0        0
\t_      junk     j        \%             0        0        0
%t_      junk     j        %_             0        0        0

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel10
+>      where (pic_x_a = pic_x_7) and
+>            (decimal_10 > binary_32_signed) and
+>            (binary_unsigned > binary_32_signed);

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

tom      tom            3  abadefih            100              100              -100

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel11
+>      where (pic_x_a = pic_x_7) or
+>            (decimal_10 = binary_unsigned) or
+>            (binary_unsigned = binary_32_signed);

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

boe      tommy        200  ebedafiih           200               50                50
bob      bob          100  abadefih            160              200               200
car      who          400  done                160              500               500
jar      how          600  inside              160              500               500
dan      sue          100  ebediih             100              100                60

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select * from pvsel02
+>      where pic_x_1 = 'B';

PIC_X_1
-------

B      
B      
B      

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select * from svsel13
+>      where medium_int = 1000;

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  7             216172782159534342  tom                                                                                                                                                                                                                                                                 1000  7                         200

--- 2 row(s) selected.
>>--    <end-input>
>>
>>
>>-- <testcase A2>
>>
>>--    <detail>
>>--    select normal where clause test case - this tests the use of
>>--    a BETWEEN predicate in a where clause.
>>
>>--    <switches>
>>--    pat-file uoutpat <mypat>
>>
>>--    <templates>
>>--    US00
>>
>>
>>--    <ufi-input>
>>      SELECT * from btsel01
+>      where binary_32_u between pic_comp_1 and binary_64_s;

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

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel02
+>      where pic_x_1 between 'A' and 'D';

PIC_X_1
-------

B      
C      
B      
B      
C      

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel03
+>      where binary_64_s between pic_9_7 and 1201;

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

A               5.00  make           90                 200.00
7               6.00  joe            80                1200.00
8               6.00  joe            80                1200.00
5            1000.00  5             100                1000.00

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel04
+>      where 150 between 100 and pic_comp_1;

VAR_CHAR                                                                                                                                                                                                                                                     MEDIUM_INT   PIC_X_7  PIC_COMP_1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  -------  --------------------

tom                                                                                                                                                                                                                                                                 1000  7                         200
bob                                                                                                                                                                                                                                                                  999  A                         300
bill                                                                                                                                                                                                                                                                2000  B                         500
christopher                                                                                                                                                                                                                                                         1000  white                    1000
william                                                                                                                                                                                                                                                             1000  black                    2000
marilyn                                                                                                                                                                                                                                                             2000  green                    3000

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel05
+>      where 'mike' between col_9 and col_10;

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 

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel06
+>      where (col_1 * 2) between col_3 and col_2;

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      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
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      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
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
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      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

--- 7 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel07
+>      where pic_x_b NOT between pic_x_a and pic_x_c;

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

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

--- 7 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel08
+>      where pic_252 between 'A' and pic_1;

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

                 300  Q                                                                                                                                                                                                                                                             X    

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel09
+>      where pic_x_6 between (pic_x_5 + 5) and pic_x_7;

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

why      gone     R        billy          7       15       30
sue      mary     Z        MARY           3       10       20
red      long     S        tommy          5       15       70
not      time     P        diane          7       20       40
joe      over     W        maria          7       90      100
deb      bill     Y        BILL           4       10       30
can      come     X        TED            7       77       77
box      here     R        debby          5       11       50

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel10
+>      where decimal_10 between binary_unsigned and
+>            (binary_32_signed + 100);

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

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel10
+>      where (decimal_10 * 2) not between binary_32_signed
+>            and binary_unsigned;

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
jon      does        1000  offsides          50000              500               100
ron      they        2000  onsides           10000              100              -100

--- 7 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel10
+>      where not ((decimal_10 * 2) between binary_32_signed
+>            and binary_unsigned);

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
jon      does        1000  offsides          50000              500               100
ron      they        2000  onsides           10000              100              -100

--- 7 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel11
+>      where decimal_10 between binary_unsigned - 50
+>            and binary_32_signed + 50;

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

bob      bob          100  abadefih            160              200               200
dan      sue          100  ebediih             100              100                60

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from pvsel03
+>      where new_name_2 between 5 and 7;

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

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

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select * from pvsel03
+>      where new_name_2 between 7 and 5;

--- 0 row(s) selected.
>>--    <ufi-input>
>>      select * from svsel13
+>      where medium_int between pic_comp_1 and 1000;

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

        90         5.00  A             216172782159534337  bob                                                                                                                                                                                                                                                                  999  A                         300
        80         6.00  7             216172782159534338  tom                                                                                                                                                                                                                                                                 1000  7                         200
        80         6.00  7             216172782159534342  tom                                                                                                                                                                                                                                                                 1000  7                         200

--- 3 row(s) selected.
>>--    <end-input>
>>
>>
>>-- <testcase A3>
>>
>>--    <detail>
>>--    select normal where clause test case - this tests the use of
>>--    an IN predicate in the where clause.
>>
>>--    <switches>
>>--    pat-file uoutpat <mypat>
>>
>>--    <templates>
>>--    US00
>>
>>
>>--    <ufi-input>
>>      select * from btsel01
+>      where binary_signed in (binary_32_u,pic_comp_1,
+>                              decimal_2_signed);

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

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel02
+>      where pic_x_1 in ('B','C');

PIC_X_1
-------

B      
C      
B      
B      
C      

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel03 where
+>      ((pic_9_7 + 1120) in (binary_64_s,120)) or
+>      (((pic_9_7 * 2) + 20) in (binary_64_s, 120));

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

A               5.00  make           90                 200.00
7               6.00  joe            80                1200.00
8               6.00  joe            80                1200.00
michael        50.00  dave           50                1500.00
michael        70.00  joan           50                4000.00

--- 5 row(s) selected.
>>-- <ufi-input>
>>       select * from btsel04
+>       where (medium_int / 2) not in (pic_comp_1,500);

VAR_CHAR                                                                                                                                                                                                                                                     MEDIUM_INT   PIC_X_7  PIC_COMP_1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  -------  --------------------

bob                                                                                                                                                                                                                                                                  999  A                         300
bill                                                                                                                                                                                                                                                                2000  B                         500
marilyn                                                                                                                                                                                                                                                             2000  green                    3000

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel05
+>      where 'mary' in (col_2,col_5,col_6);

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
-------  -------  -------  -----  -----  -----  -----  -----  -----  -----  -----  -----  ------

sue      A        in       peggy  diane  zora   cathy  mary   mary   rhoda  debra  sue    madge 
deb      D        jo       lowry  mary   sunny  debra  junk   junk   junk   junk   junk   junk  
not      B        on       lowry  mary   mogul  pammy  junk   junk   junk   junk   junk   junk  

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel06
+>      where col_1 in (col_2,col_3,col_21,col_9 * 2);

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
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

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel07
+>      where pic_x_a not in ('jo','al');

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

P        P        P      
 al      F        al     
 al      F        di     
B        A        ed     
JO       D        em     

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel07
+>      where not (pic_x_a in ('jo','al'));

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

P        P        P      
 al      F        al     
 al      F        di     
B        A        ed     
JO       D        em     

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel08
+>      where 1000 in (large_int,500);

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

                1000  harveys                                                                                                                                                                                                                                                       B    

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel09
+>      where pic_x_7 in ((pic_x_6 * 2),30);

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

why      gone     R        billy          7       15       30
sue      mary     Z        MARY           3       10       20
not      time     P        diane          7       20       40
deb      bill     Y        BILL           4       10       30
_t%      junk     j        _%             0        0        0
\ts      t_go     j        to%go          0        0        0
\t_      junk     j        \%             0        0        0
%t_      junk     j        %_             0        0        0

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel10
+>      where decimal_10 in (binary_unsigned,110,25);

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
tom      tom            3  abadefih            100              100              -100
rod      for          400  inside               25               60                 0

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel11
+>      where pic_9_7 not in (decimal_10,binary_unsigned,
+>                            binary_32_signed);

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

bar      where        300  gone                200             9999              -100
bob      bob          100  abadefih            160              200               200
car      who          400  done                160              500               500
jar      how          600  inside              160              500               500
tar      what         500  none                100             8888                 0

--- 5 row(s) selected.
>>--    <ufi-input>
>>      select * from pvsel04
+>      where pic_comp_1 in (200,300);

VAR_CHAR                                                                                                                                                                                                                                                     MEDIUM_INT   PIC_X_7  PIC_COMP_1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  -------  --------------------

tom                                                                                                                                                                                                                                                                 1000  7                         200
bob                                                                                                                                                                                                                                                                  999  A                         300

--- 2 row(s) selected.
>>--    <end-input>
>>
>>
>>-- <testcase A4>
>>
>>--    <detail>
>>--    select normal test case with where clause - this tests
>>--    the use of a LIKE predicate in the where clause.
>>
>>--    <switches>
>>--    pat-file uoutpat <mypat>
>>
>>--    <templates>
>>--    US00
>>
>>--    <ufi-input>
>>      select * from btsel01
+>      where char_10 like 'steven' or
+>            pic_x_7 like 'bob%' or
+>            var_char like 'jim_y';

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       bobby       A        bobby    bop                                                                                                                                                                                                       B                                                                                                                                                                                                                                                                         60        60.00               1200.000                    60         .79     100.99000       1000        8000                 -1000          5               .60                 100            2.1           .200              2
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

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel01
+>      where char_10 like 'steven    ';

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
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

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel02
+>      where pic_x_1 like '_';

PIC_X_1
-------

B      
C      
B      
Q      
B      
C      
Q      
Q      

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel03
+>      where pic_x4_a not like 'joe ';

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

A               5.00  make           90                 200.00
5            1000.00  5             100                1000.00
michael        50.00  dave           50                1500.00
7               6.00  john           80                2000.00
B               6.00  mark           80                3000.00
michael        70.00  joan           50                4000.00

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel03
+>      where not (pic_x4_a like 'joe ');

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

A               5.00  make           90                 200.00
5            1000.00  5             100                1000.00
michael        50.00  dave           50                1500.00
7               6.00  john           80                2000.00
B               6.00  mark           80                3000.00
michael        70.00  joan           50                4000.00

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel04
+>      where var_char like 'b%';

VAR_CHAR                                                                                                                                                                                                                                                     MEDIUM_INT   PIC_X_7  PIC_COMP_1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  -------  --------------------

bob                                                                                                                                                                                                                                                                  999  A                         300
bobby                                                                                                                                                                                                                                                                200  sue                       100
bill                                                                                                                                                                                                                                                                2000  B                         500

--- 3 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel05
+>      where col_9 not like '__e';

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
-------  -------  -------  -----  -----  -----  -----  -----  -----  -----  -----  -----  ------

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  
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  
why      B        so       lowry  amy    squaw  tammy  junk   junk   junk   junk   junk   junk  
not      B        on       lowry  mary   mogul  pammy  junk   junk   junk   junk   junk   junk  

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel06
+>      where pic_x_a like '%';

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      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
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      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
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      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
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      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

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel06
+>      where pic_x_a like '%%';

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      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
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      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
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      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
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      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

--- 8 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel07
+>      where pic_x_a like '%_';

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

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

--- 10 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel07
+>      where pic_x_a like '_%_';

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

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

--- 10 row(s) selected.
>>
>>--     test matching when some column values contain blanks
>>
>>--    <ufi-input>
>>      select * from btsel07
+>      where pic_x_a like 'al';

--- 0 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel07
+>      where pic_x_a like '_al';

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

 al      F        al     
 al      F        di     

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel07
+>      where pic_x_a like 'al_';

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

al       F        al     
al       Q        al     
al       E        bo     

--- 3 row(s) selected.
>>
>>--     test matching when LIKE string contains a blank
>>
>>--    <ufi-input>
>>      select * from btsel07
+>      where pic_x_a like 'al ';

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

al       F        al     
al       Q        al     
al       E        bo     

--- 3 row(s) selected.
>>
>>--    <ufi-input>
>>      select * from btsel08
+>      where pic_252 like '__r%s%';

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

                1000  harveys                                                                                                                                                                                                                                                       B    
                 100  carltons                                                                                                                                                                                                                                                      E    

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel09
+>      where pic_x_4 like '___  ';

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

can      come     X        TED            7       77       77
_t%      junk     j        _%             0        0        0
\t_      junk     j        \%             0        0        0
%t_      junk     j        %_             0        0        0

--- 4 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel10
+>      where char_10 like '_b_d_f%h%';

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
tom      tom            3  abadefih            100              100              -100

--- 2 row(s) selected.
>>--    <ufi-input>
>>      select * from btsel11
+>      where char_10 not like '_b_d_f%h%';

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

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

--- 6 row(s) selected.
>>--    <ufi-input>
>>      select * from pvsel01
+>      where pic_x_7 like 'walter ';

BINARY_SIGNED  LARGE_INT             MEDIUM_INT  PIC_DECIMAL_3  PIC_X_1  PIC_X_7  SMALL_INT
-------------  --------------------  ----------  -------------  -------  -------  ---------

           50            1000000000       10000              1  C        walter          10

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select * from svsel11
+>      where col_6 like 'walter ';

COL_1   COL_2                 COL_3       COL_4  COL_5  COL_6    COL_7   COL_8
------  --------------------  ----------  -----  -----  -------  ------  -------

    50            1000000000       10000      1  C      walter       10  A      

--- 1 row(s) selected.
>>--    <end-input>
>>
>>-- <testcase A5>
>>
>>--    <detail>
>>--    select normal test case with where clause - this tests
>>--    the use of an ESCAPE character in a LIKE predicate of
>>--    a where clause.
>>
>>--    <switches>
>>--    pat-file uoutpat <mypat>
>>
>>--    <templates>
>>--    US00
>>
>>--     using '\' as the escape character
>>
>>--     turn of '_' as a special character
>>--    <ufi-input>
>>      select *
+>      from btsel09
+>      where pic_x_2 like 't\_go' escape '\';

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

\ts      t_go     j        to%go          0        0        0

--- 1 row(s) selected.
>>
>>--     turn of '%' as a special character
>>--    <ufi-input>
>>      select *
+>      from btsel09
+>      where pic_x_4 like 'to\%go' escape '\';

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

\ts      t_go     j        to%go          0        0        0

--- 1 row(s) selected.
>>
>>--     turn of '\' as a special character
>>--    <ufi-input>
>>      select *
+>      from btsel09
+>      where pic_x_a like '\\ts' escape '\';

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

\ts      t_go     j        to%go          0        0        0

--- 1 row(s) selected.
>>
>>--     turn of '_' as a special character, then try using
>>--     it again
>>--    <ufi-input>
>>      select *
+>      from btsel09
+>      where pic_x_2 like 't\__o' escape '\' ;

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

\ts      t_go     j        to%go          0        0        0

--- 1 row(s) selected.
>>
>>--     turn of '%' as a special character, then try using
>>--     it again
>>--    <ufi-input>
>>      select *
+>      from btsel09
+>      where pic_x_4 like 'to\%%' escape '\';

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

\ts      t_go     j        to%go          0        0        0

--- 1 row(s) selected.
>>
>>--     turn of '\' as a special character, then try using
>>--     it again, with an intervening character
>>--    <ufi-input>
>>      select *
+>      from btsel09
+>      where pic_x_a like '\\t\_' escape '\';

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

--- 1 row(s) selected.
>>
>>--     turn of '\' as a special character, then try using
>>--     it again, with no intervening character
>>--    <ufi-input>
>>      select *
+>      from btsel09
+>      where pic_x_4 like '\\\%   ' escape '\' ;

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

--- 1 row(s) selected.
>>
>>--     specifying a space (' ') as the escape character
>>
>>--    <ufi-input>
>>      select *
+>      from btsel07
+>      where pic_x_a like 'al' escape ' ';

--- 0 row(s) selected.
>>
>>--     specifying the blank ('') as the escape character
>>
>>--    <ufi-input>
>>      select *
+>      from btsel07
+>      where pic_x_a like '  al' escape ' ';

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

 al      F        al     
 al      F        di     

--- 2 row(s) selected.
>>
>>--     using '_' as the escape character
>>
>>--     turn of '_' as a special character
>>--    <ufi-input>
>>      select *
+>      from btsel09
+>      where pic_x_2 like 't__go' escape '_' ;

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

\ts      t_go     j        to%go          0        0        0

--- 1 row(s) selected.
>>
>>--     turn of '%' as a special character
>>--    <ufi-input>
>>      select *
+>      from btsel09
+>      where pic_x_4 like 'to_%go' escape '_' ;

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

\ts      t_go     j        to%go          0        0        0

--- 1 row(s) selected.
>>
>>--     turn of '%' as a special character, then try
>>--     using it again
>>--    <ufi-input>
>>      select *
+>      from btsel09
+>      where pic_x_4 like 'to_%%' escape '_' ;

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

\ts      t_go     j        to%go          0        0        0

--- 1 row(s) selected.
>>
>>--     turn of '_' as a special character, then try
>>--     using it again, with an intervening character
>>--    <ufi-input>
>>      select *
+>      from btsel09
+>      where pic_x_a like '__t_%' escape '_' ;

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

--- 1 row(s) selected.
>>
>>--     turn of '_' as a special character, then try
>>--     using it again, no intervening character
>>--    <ufi-input>
>>      select *
+>      from btsel09
+>      where pic_x_4 like '___%   ' escape '_' ;

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

--- 1 row(s) selected.
>>
>>--     using '%' as escape character
>>
>>--     turn of '_' as a special character
>>--    <ufi-input>
>>      select *
+>      from btsel09
+>      where pic_x_2 like 't%_go' escape '%' ;

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

\ts      t_go     j        to%go          0        0        0

--- 1 row(s) selected.
>>
>>--     turn of '%' as a special character
>>--    <ufi-input>
>>      select *
+>      from btsel09
+>      where pic_x_4 like 'to%%go' escape '%' ;

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

\ts      t_go     j        to%go          0        0        0

--- 1 row(s) selected.
>>
>>--     turn of '_' as a special character, then try
>>--     using it again
>>--    <ufi-input>
>>      select *
+>      from btsel09
+>      where pic_x_2 like 't%__o' escape '%' ;

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

\ts      t_go     j        to%go          0        0        0

--- 1 row(s) selected.
>>
>>--     turn of '%' as a special character, then try
>>--     using it again, with an intervening character
>>--    <ufi-input>
>>      select *
+>      from btsel09
+>      where pic_x_a like '%%t%_' escape '%' ;

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

--- 1 row(s) selected.
>>
>>--     turn of '%' as a special character, then try
>>--     using it again, no intervening character
>>--    <ufi-input>
>>      select *
+>      from btsel09
+>      where pic_x_4 like '%%%_   ' escape '%' ;

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

--- 1 row(s) selected.
>>
>>--    <end-input>
>>
>>
>>
>>-- <testcase A6>
>>
>>--    <detail>
>>--     select normal test case with where clause - this tests the use
>>--     of the for browse access, for stable access, and for repeatable access
>>--     clauses. Only syntax is checked here.
>>
>>--    <switches>
>>--    pat-file uoutpat <mypat>
>>
>>--    <templates>
>>--    US00
>>
>>
>>--    <comment>  **** check for locking on audited tables ****
>>--    <ufi-input>
>>       select * from btsel06
+>       where col_7 = 100
+>       for stable 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  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
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

--- 2 row(s) selected.
>>--    <ufi-input>
>>       select * from btsel04
+>       where medium_int <= 1000
+>       for browse access;

VAR_CHAR                                                                                                                                                                                                                                                     MEDIUM_INT   PIC_X_7  PIC_COMP_1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  -------  --------------------

tom                                                                                                                                                                                                                                                                 1000  7                         200
bob                                                                                                                                                                                                                                                                  999  A                         300
sue                                                                                                                                                                                                                                                                  200  sue                       100
bobby                                                                                                                                                                                                                                                                200  sue                       100
christopher                                                                                                                                                                                                                                                         1000  white                    1000
william                                                                                                                                                                                                                                                             1000  black                    2000

--- 6 row(s) selected.
>>--    <ufi-input>
>>       select * from btsel07
+>       where pic_x_b < 'Q'
+>       for repeatable access;

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

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

--- 8 row(s) selected.
>>
>>--    <comment> **** check for locking on non-audited tables ****
>>--    <ufi-input>
>>       select * from btsel05
+>       where pic_x_a > 'abe'
+>       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 
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  
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  
why      B        so       lowry  amy    squaw  tammy  junk   junk   junk   junk   junk   junk  
not      B        on       lowry  mary   mogul  pammy  junk   junk   junk   junk   junk   junk  

--- 8 row(s) selected.
>>--    <ufi-input>
>>       select * from btsel05
+>       where pic_x_a > 'abe'
+>       for stable 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 
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  
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  
why      B        so       lowry  amy    squaw  tammy  junk   junk   junk   junk   junk   junk  
not      B        on       lowry  mary   mogul  pammy  junk   junk   junk   junk   junk   junk  

--- 8 row(s) selected.
>>--    <ufi-input>
>>       select * from btsel05
+>       where pic_x_a > 'abe'
+>       for repeatable 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 
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  
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  
why      B        so       lowry  amy    squaw  tammy  junk   junk   junk   junk   junk   junk  
not      B        on       lowry  mary   mogul  pammy  junk   junk   junk   junk   junk   junk  

--- 8 row(s) selected.
>>
>>--    <comment> **** check for locking on views ****
>>--    <ufi-input>
>>       select * from pvsel04
+>       where pic_comp_1 in (200,300)
+>       for browse access;

VAR_CHAR                                                                                                                                                                                                                                                     MEDIUM_INT   PIC_X_7  PIC_COMP_1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  -------  --------------------

tom                                                                                                                                                                                                                                                                 1000  7                         200
bob                                                                                                                                                                                                                                                                  999  A                         300

--- 2 row(s) selected.
>>
>>--    <ufi-input>
>>       select * from pvsel01
+>       where binary_signed > 50
+>       for stable access;

BINARY_SIGNED  LARGE_INT             MEDIUM_INT  PIC_DECIMAL_3  PIC_X_1  PIC_X_7  SMALL_INT
-------------  --------------------  ----------  -------------  -------  -------  ---------

           60                 -1000        8000              2  A        bobby         1000
         1000                   999        9000              4  C        7               80
         2000              -1000000        8000              5  Q        sue           2000
         3000                  2000        1000              7  E        jimmy         9000
         4000                     0        5000              8  Z        johnny        8000
         8000                  1000       10000              3  B        9               90

--- 6 row(s) selected.
>>--    <ufi-input>
>>       select * from svsel11
+>       where col_1 > 50
+>       for stable access;

COL_1   COL_2                 COL_3       COL_4  COL_5  COL_6    COL_7   COL_8
------  --------------------  ----------  -----  -----  -------  ------  -------

    60                 -1000        8000      2  A      bobby      1000  7      
  2000              -1000000        8000      5  Q      sue        2000  7      
    60                 -1000        8000      2  A      bobby      1000  8      
  2000              -1000000        8000      5  Q      sue        2000  8      
  1000                   999        9000      4  C      7            80  michael
  3000                  2000        1000      7  E      jimmy      9000  7      
  8000                  1000       10000      3  B      9            90  7      
  4000                     0        5000      8  Z      johnny     8000  7      

--- 8 row(s) selected.
>>--    <ufi-input>
>>       select * from pvsel02
+>       where pic_x_1 = 'B'
+>       for repeatable access;

PIC_X_1
-------

B      
B      
B      

--- 3 row(s) selected.
>>--    <ufi-input>
>>       select * from svsel13
+>       where medium_int = 1000
+>       for repeatable 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  7             216172782159534342  tom                                                                                                                                                                                                                                                                 1000  7                         200

--- 2 row(s) selected.
>>--    <end-input>
>>
>>
>>-- <testcase A7>
>>
>>--    <detail>
>>--       select normal where clause test case - this tests the selection
>>--       of aggregate functions (COUNT, AVG, MAX, MIN, SUM). The
>>--       following attributes of aggregates are tested:
>>--           SELECT all set function (function argument a value expression,
>>--                                    optionally preceded by ALL, value
>>--                                    expression a simple column spec)
>>--           SELECT distinct set function (function argument a column spec,
>>--                                         preceded by DISTINCT)
>>--           SELECT the average, max, min, sum of expressions
>>--           SELECT an aggregate expression involving SYSKEY
>>--           SELECT an expression with aggregates in it
>>--           SELECT more than one aggregate
>>
>>--    <switches>
>>--    pat-file uoutpat <mypat>
>>
>>--    <templates>
>>--    US00
>>
>>--    <comment> select ALL
>>--    <ufi-input>
>>      select count(*)
+>      from btsel01
+>      where var_char = 'thomas';

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

                   3

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select avg(medium_int)
+>      from pvsel01
+>      where medium_int <> 8000;

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

                7000

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select max(ALL new_name_3)
+>      from pvsel03
+>      where new_name_1 = 80;

(EXPR) 
-------

B      

--- 1 row(s) selected.
>>
>>--    <comment> select aggregates on 0 records
>>--    <ufi-input>
>>      select count(*)
+>      from btsel03
+>      where binary_32_u < 0;

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

                   0

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select avg(binary_64_s)
+>      from btsel03
+>      where binary_32_u < 0;

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

                    ?

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select count(*), avg(binary_64_s)
+>      from btsel03
+>      where binary_32_u < 0;

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

                   0                      ?

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select count(*), avg(binary_64_s), avg(binary_32_u)
+>      from btsel03
+>      where binary_32_u < 0;

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

                   0                      ?                      ?

--- 1 row(s) selected.
>>
>>--    <comment> select DISTINCT
>>--    <ufi-input>
>>      select SUM(distinct binary_64_s)
+>      from btsel01
+>      where binary_32_u > 50;

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

             4700.000

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select count(distinct medium_int)
+>      from pvsel01
+>      where medium_int not in (1000,10000);

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

                   3

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

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

                   3

--- 1 row(s) selected.
>>
>>--    <comment> select an expression inside an aggregate
>>--    <ufi-input>
>>      select avg(ALL binary_32_u + pic_comp_1)
+>      from btsel01
+>      where (pic_comp_1 <> 60);

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

               800.00

--- 1 row(s) selected.
>>-- <ufi-input>
>>       select max(ALL new_name_1 / new_name_2)
+>       from svsel13
+>       where ((new_name_1 = 80) or (new_name_1 = 90)) and
+>             ((new_name_2 = 5) or (new_name_2 = 6));

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

           18.0000000

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select sum ((col_1 + col_4) + 1000)
+>      from svsel11
+>      where col_6 like '*';

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

                   ?

--- 1 row(s) selected.
>>
>>--    <comment> select expression of aggregates
>>-- <ufi-input>
>>       select (sum(pic_decimal_1) / count(*))
+>       from btsel01
+>       where pic_comp_3 > 100.6;

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

                  4.1

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select avg(pic_comp_1 ) + sum(pic_comp_1)
+>      from svsel13
+>      where pic_comp_1 > 100;

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

                1500

--- 1 row(s) selected.
>>--    <ufi-input>
>>      select sum(decimal_1) + sum(decimal_1)
+>      from btsel01
+>      where decimal_3_unsigned <> 110;

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

                  60

--- 1 row(s) selected.
>>
>>--    <comment> select multiple aggregates
>>--    <ufi-input>
>>      select sum(pic_decimal_3),avg(pic_decimal_3),max(pic_decimal_3),
+>             min(pic_decimal_3),count(distinct pic_decimal_3),count(*)
+>      from pvsel01
+>      where pic_decimal_3 < 8;

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

                  28                     4       7       1                     7                     7

--- 1 row(s) selected.
>>
>>--    <ufi-input>
>>      select sum(new_name_1), avg(new_name_2), max(new_name_3)
+>      from svsel13
+>      where pic_comp_1 > 100;

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

                 330                   5.75  B      

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