ij> AUTOCOMMIT OFF;
ij> -- MODULE DML083

-- SQL Test Suite, V6.0, Interactive SQL, dml083.sql
-- 59-byte ID
-- TEd Version #

-- AUTHORIZATION SCHANZLE
   set schema SCHANZLE;
0 rows inserted/updated/deleted
ij> --O   SELECT USER FROM HU.ECCO;
  VALUES USER;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
SCHANZLE                                                                                                                        
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment

-- date_time print

-- NOTE Direct support for SQLCODE or SQLSTATE is not required
-- NOTE    in Interactive Direct SQL, as defined in FIPS 127-2.
-- NOTE   ********************* instead ***************************
-- NOTE If a statement raises an exception condition,
-- NOTE    then the system shall display a message indicating that
-- NOTE    the statement failed, giving a textual description
-- NOTE    of the failure.
-- NOTE If a statement raises a completion condition that is a
-- NOTE    "warning" or "no data", then the system shall display
-- NOTE    a message indicating that the statement completed,
-- NOTE    giving a textual description of the "warning" or "no data."

-- NO_TEST:0496 SQLSTATE 22002: data exception/null, value, no indic.!

-- Testing indicators

-- *********************************************

-- TEST:0498 SQLSTATE 22001: data exception/string right trunc.!

   INSERT INTO HU.STAFF 
         VALUES ('E6','Earl Brown',11,'Claggetsville Maryland');
ERROR 22001: A truncation error was encountered trying to shrink CHAR 'Claggetsville Maryland' to length 15.
ij> -- PASS:0498 If ERROR, data exception/string right trunc.?
-- PASS:0498 If 0 rows inserted OR SQLSTATE = 22001 OR SQLCODE < 0?

   INSERT INTO HU.STAFF 
         VALUES ('E7','Ella Brown',12,'Claggetsville Maryland');
ERROR 22001: A truncation error was encountered trying to shrink CHAR 'Claggetsville Maryland' to length 15.
ij> -- PASS:0498 If ERROR, data exception/string right trunc.?
-- PASS:0498 If 0 rows inserted OR SQLSTATE = 22001 OR SQLCODE < 0?

--O   SELECT COUNT(*)
   SELECT *
         FROM HU.STAFF;
EM&|EMPNAME             |GRADE|CITY           
----------------------------------------------
E1 |Alice               |12   |Deale          
E2 |Betty               |10   |Vienna         
E3 |Carmen              |13   |Vienna         
E4 |Don                 |12   |Deale          
E5 |Ed                  |13   |Akron          
ij> -- PASS:0498 If count = 5?

-- restore
   ROLLBACK WORK;
ij> -- END TEST >>> 0498 <<< END TEST
-- *********************************************

-- TEST:0500 SQLSTATE 01003: warning/null value elim. in set function!

-- setup
   DELETE FROM HU.HH;
0 rows inserted/updated/deleted
WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.
ij> INSERT INTO HU.HH 
         VALUES (3);
1 row inserted/updated/deleted
ij> INSERT INTO HU.HH 
         VALUES (NULL);
1 row inserted/updated/deleted
ij> SELECT AVG(SMALLTEST)
         FROM HU.HH;
1     
------
3     
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0500 If WARNING, null value eliminated in set function?
-- PASS:0500 OR SQLSTATE = 01003?

-- setup
   UPDATE HU.STAFF 
         SET GRADE = NULL
         WHERE GRADE = 13;
2 rows inserted/updated/deleted
ij> SELECT AVG(GRADE)
         FROM HU.STAFF
         WHERE CITY = 'Vienna';
1           
------------
10.0000     
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0500 If WARNING, null value eliminated in set function?
-- PASS:0500 OR SQLSTATE = 01003?

   SELECT SUM(DISTINCT GRADE)
         FROM HU.STAFF;
1        
---------
22       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0500 If WARNING, null value eliminated in set function?
-- PASS:0500 OR SQLSTATE = 01003?

   INSERT INTO HU.HH 
         SELECT MAX(GRADE) 
               FROM HU.STAFF;
1 row inserted/updated/deleted
ij> -- PASS:0500 If WARNING, null value eliminated in set function?
-- PASS:0500 OR SQLSTATE = 01003?

   DELETE FROM HU.HH 
         WHERE SMALLTEST < (SELECT MIN(GRADE)
                                  FROM HU.STAFF
                                  WHERE CITY = 'Vienna');
1 row inserted/updated/deleted
ij> -- PASS:0500 If WARNING, null value eliminated in set function?
-- PASS:0500 OR SQLSTATE = 01003?

   SELECT CITY, COUNT(DISTINCT GRADE) 
         FROM HU.STAFF
         GROUP BY CITY
         ORDER BY CITY DESC;
CITY           |2          
---------------------------
Vienna         |1          
WARNING 01003: Null values were eliminated from the argument of a column function.
Deale          |1          
Akron          |0          
ij> -- PASS:0500 If 3 rows are selected with the following order?
-- PASS:0500     CITY    COUNT(DISTINCT GRADE)?
-- PASS:0500   'Vienna'          1?
-- PASS:0500   'Deale'           1?
-- PASS:0500   'Akron'           0?
-- PASS:0500 OR SQLSTATE = 01003?

-- restore
   ROLLBACK WORK;
ij> -- END TEST >>> 0500 <<< END TEST
-- *********************************************

-- NO_TEST:0501 SQLSTATE 01004: warning/string right truncation!

-- Testing host variables

-- *************************************************////END-OF-MODULE
;
ij>