ij> AUTOCOMMIT OFF;
ij> -- MODULE DML023

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

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

-- date_time print

-- TEST:0103 Subquery with comparison predicate!
     SELECT PNUM                                                        
          FROM PROJ                                                            
          WHERE PROJ.CITY =                                                  
             (SELECT STAFF.CITY                                              
              FROM STAFF                                                     
              WHERE EMPNUM = 'E1');
PN&
---
P1 
P4 
P6 
ij> -- PASS:0103 If 3 rows are selected with PNUMs:'P1','P4','P6?

-- END TEST >>> 0103 <<< END TEST
-- **************************************************************

-- TEST:0104 SQLCODE < 0, subquery with more than 1 value!
     SELECT PNUM                                                       
          FROM PROJ                                                          
          WHERE PROJ.CITY =                                                  
             (SELECT STAFF.CITY                                              
              FROM STAFF                                                    
              WHERE EMPNUM > 'E1' );
ERROR 21000: Scalar subquery is only allowed to return a single row.
ij> -- PASS:0104 If ERROR, SELECT returns more than 1 row in subquery?
-- PASS:0104 If 0 rows are selected?
 
-- END TEST >>> 0104 <<< END TEST
-- ************************************************************

-- TEST:0105 Subquery in comparison predicate is empty!
--O     SELECT COUNT(*)
     SELECT *
          FROM STAFF
          WHERE STAFF.CITY =
              (SELECT PROJ.CITY
                    FROM PROJ
                    WHERE PNUM > 'P7');
EM&|EMPNAME             |GRADE|CITY           
----------------------------------------------
ij> -- PASS:0105 If count = 0?

--O     SELECT COUNT(*)
     SELECT *
          FROM STAFF
          WHERE NOT (STAFF.CITY =
                  (SELECT PROJ.CITY
                        FROM PROJ
                        WHERE PNUM > 'P7' ));
EM&|EMPNAME             |GRADE|CITY           
----------------------------------------------
ij> -- PASS:0105 If count = 0?

-- END TEST >>> 0105 <<< END TEST
-- *************************************************************

-- TEST:0106 Comparison predicate <> !
     SELECT PNUM                                                         
          FROM PROJ                                                          
          WHERE CITY <> 'Deale';
PN&
---
P2 
P3 
P5 
ij> -- PASS:0106 If 3 rows are selected with PNUMs:'P2','P3','P5'?

-- END TEST >>> 0106 <<< END TEST
-- *************************************************************

-- TEST:0107 Comp predicate with short string logically blank padded!
--O     SELECT COUNT(*)
     SELECT empnum
          FROM WORKS
          WHERE EMPNUM = 'E1';
EM&
---
E1 
E1 
E1 
E1 
E1 
E1 
ij> -- PASS:0107 If count = 6 ?

--O     SELECT COUNT(*)
     SELECT empnum
          FROM WORKS
          WHERE EMPNUM = 'E1' AND EMPNUM = 'E1 ';
EM&
---
E1 
E1 
E1 
E1 
E1 
E1 
ij> -- PASS:0107 If count = 6?

-- END TEST >>> 0107 <<< END TEST
-- ****************************************************************

-- TEST:0180 NULLs sort together in ORDER BY!

-- setup
     UPDATE STAFF
          SET GRADE = NULL
          WHERE EMPNUM = 'E1' OR EMPNUM = 'E3' OR EMPNUM = 'E5';
3 rows inserted/updated/deleted
ij> -- PASS:0180 If 3 rows are updated?
                                                                     
     SELECT EMPNUM,GRADE                                                 
          FROM   STAFF                                                        
          ORDER  BY GRADE,EMPNUM;
EM&|GRADE
---------
E2 |10   
E4 |12   
E1 |NULL 
E3 |NULL 
E5 |NULL 
ij> -- PASS:0180 If 5 rows are selected with NULLs together ?
-- PASS:0180 If first EMPNUM is either 'E1' or 'E2'?
-- PASS:0180 If last EMPNUM is either 'E4' or 'E5?

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

-- TEST:0181 NULLs are equal for DISTINCT!

-- setup
     UPDATE STAFF
          SET GRADE = NULL
          WHERE EMPNUM = 'E1' OR EMPNUM = 'E3' OR EMPNUM = 'E5';
3 rows inserted/updated/deleted
ij> -- PASS:0181 If 3 rows are updated?
                                                                     
     SELECT DISTINCT USER, GRADE                                               
          FROM   STAFF                                                        
          ORDER  BY GRADE;
1                                                                                                                               |GRADE
--------------------------------------------------------------------------------------------------------------------------------------
HU                                                                                                                              |10   
HU                                                                                                                              |12   
HU                                                                                                                              |NULL 
ij> -- PASS:0181 If 3 rows are selected with GRADEs:10, 12, NULL ?
-- PASS:0181 GRADE 10 precedes GRADE 12?

-- restore
     ROLLBACK WORK;
ij> -- END TEST >>> 0181 <<< END TEST
                                                                       
-- *************************************************////END-OF-MODULE
;
ij>