ij> AUTOCOMMIT OFF;
ij> -- MODULE  YTS811  

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

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

-- TEST:7568 WHERE <search condition> referencing column!

   CREATE VIEW V_DATA_TYPE AS
     SELECT SUM(NUM) AS VT1, ING AS VT2, SMA AS VT3
     FROM DATA_TYPE
     GROUP BY ING, SMA;
0 rows inserted/updated/deleted
ij> -- PASS:7568 If view created successfully?

   COMMIT WORK;
ij> --O   INSERT INTO CTS1.DATA_TYPE (ING, SMA) VALUES
   INSERT INTO DATA_TYPE (ING, SMA) VALUES
     (1,1);
1 row inserted/updated/deleted
ij> -- PASS:7568 If 1 row inserted successfully?

--O   INSERT INTO CTS1.DATA_TYPE (NUM, ING, SMA)
   INSERT INTO DATA_TYPE (NUM, ING, SMA)
     VALUES (2,2,3);
1 row inserted/updated/deleted
ij> -- PASS:7568 If 1 row inserted successfully?

--O   INSERT INTO CTS1.DATA_TYPE (NUM, ING, SMA)
   INSERT INTO DATA_TYPE (NUM, ING, SMA)
     VALUES (3,4,5);
1 row inserted/updated/deleted
ij> -- PASS:7568 If 1 row inserted successfully?

--O   INSERT INTO CTS1.DATA_TYPE (NUM, ING, SMA)
   INSERT INTO DATA_TYPE (NUM, ING, SMA)
     VALUES (2,2,3);
1 row inserted/updated/deleted
ij> -- PASS:7568 If 1 row inserted successfully?

--O   INSERT INTO CTS1.DATA_TYPE (NUM, ING, SMA)
   INSERT INTO DATA_TYPE (NUM, ING, SMA)
     VALUES (5,4,3);
1 row inserted/updated/deleted
ij> -- PASS:7568 If 1 row inserted successfully?

   SELECT VT1, VT2, VT3
     FROM V_DATA_TYPE
     WHERE NOT VT1 = 0
     ORDER BY VT2, VT3;
VT1        |VT2        |VT3   
------------------------------
4          |2          |3     
WARNING 01003: Null values were eliminated from the argument of a column function.
5          |4          |3     
3          |4          |5     
ij> -- PASS:7568 If WARNING - null value eliminated in set function?
-- PASS:7568 If 3 rows are returned in the following order?
--                VT1     VT2     VT3
--                ===     ===     ===
-- PASS:7568 If   4       2       3  ?
-- PASS:7568 If   5       4       3  ?
-- PASS:7568 If   3       4       5  ?

   SELECT VT1, VT2, VT3
     FROM V_DATA_TYPE WHERE VT2 = 1;
VT1        |VT2        |VT3   
------------------------------
NULL       |1          |1     
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:7568 If WARNING - null value eliminated in set function?
-- PASS:7568 If  NULL, 1, 1?

   SELECT SUM(NUM) 
     FROM DATA_TYPE
     WHERE NUM IS NOT NULL;
1          
-----------
12         
ij> -- PASS:7568 If SUM = 12?

   SELECT SUM(VT1) AS SUNTA1 
     FROM V_DATA_TYPE AS TB3;
SUNTA1               
---------------------
12                   
WARNING 01003: Null values were eliminated from the argument of a column function.
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:7568 If WARNING - null value eliminated in set function?
-- PASS:7568 If SUM = 12?

   ROLLBACK WORK;
ij> DROP VIEW V_DATA_TYPE;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 7568 <<< END TEST
-- *********************************************
-- *************************************************////END-OF-MODULE
;
ij>