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