ij> AUTOCOMMIT OFF; ij> -- MODULE DML148 -- SQL Test Suite, V6.0, Interactive SQL, dml148.sql -- 59-byte ID -- TEd Version # -- AUTHORIZATION FLATER set schema FLATER; 0 rows inserted/updated/deleted ij> --O SELECT USER FROM HU.ECCO; VALUES USER; 1 -------------------------------------------------------------------------------------------------------------------------------- FLATER ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment ROLLBACK WORK; ij> -- date_time print -- TEST:0843 Ordering of column names in joins ! -- REFERENCE: 7.5 sr 6 f -- NOTE:0843 ordering of column names in NATURAL JOIN SELECT * --O FROM HU.WORKS NATURAL LEFT JOIN HU.PROJ FROM HU.WORKS JOIN HU.PROJ on (HU.WORKS.pnum=HU.PROJ.pnum) --O FROM WORKS , PROJ where works.pnum = proj.pnum ORDER BY EMPNUM DESC, HU.PROJ.PNUM; EM&|PN&|HOURS |PN&|PNAME |PTYPE |BUDGET |CITY ------------------------------------------------------------------------- E4 |P2 |20 |P2 |CALM |Code |30000 |Vienna E4 |P4 |40 |P4 |SDP |Design|20000 |Deale E4 |P5 |80 |P5 |IRM |Test |10000 |Vienna E3 |P2 |20 |P2 |CALM |Code |30000 |Vienna E2 |P1 |40 |P1 |MXSS |Design|10000 |Deale E2 |P2 |80 |P2 |CALM |Code |30000 |Vienna E1 |P1 |40 |P1 |MXSS |Design|10000 |Deale E1 |P2 |20 |P2 |CALM |Code |30000 |Vienna E1 |P3 |80 |P3 |SDP |Test |30000 |Tampa E1 |P4 |20 |P4 |SDP |Design|20000 |Deale E1 |P5 |12 |P5 |IRM |Test |10000 |Vienna E1 |P6 |12 |P6 |PAYR |Design|50000 |Deale ij> -- PASS:0843 If 12 rows selected? -- PASS:0843 If ordered row and column values for first two rows are: ? -- PASS:0843 P2 E4 20 CALM Code 30000 Vienna ? -- PASS:0843 P4 E4 40 SDP Design 20000 Deale ? -- NOTE:0843 ordering of column names in JOIN ... ON SELECT * FROM HU.WORKS JOIN HU.PROJ ON (HU.WORKS.PNUM=HU.PROJ.PNUM) ORDER BY EMPNUM DESC, HU.PROJ.PNUM; EM&|PN&|HOURS |PN&|PNAME |PTYPE |BUDGET |CITY ------------------------------------------------------------------------- E4 |P2 |20 |P2 |CALM |Code |30000 |Vienna E4 |P4 |40 |P4 |SDP |Design|20000 |Deale E4 |P5 |80 |P5 |IRM |Test |10000 |Vienna E3 |P2 |20 |P2 |CALM |Code |30000 |Vienna E2 |P1 |40 |P1 |MXSS |Design|10000 |Deale E2 |P2 |80 |P2 |CALM |Code |30000 |Vienna E1 |P1 |40 |P1 |MXSS |Design|10000 |Deale E1 |P2 |20 |P2 |CALM |Code |30000 |Vienna E1 |P3 |80 |P3 |SDP |Test |30000 |Tampa E1 |P4 |20 |P4 |SDP |Design|20000 |Deale E1 |P5 |12 |P5 |IRM |Test |10000 |Vienna E1 |P6 |12 |P6 |PAYR |Design|50000 |Deale ij> -- PASS:0843 If 12 rows selected? -- PASS:0843 If ordered row and column values for first two rows are: ? -- PASS:0843 P2 E4 20 CALM Code 30000 Vienna ? -- PASS:0843 P4 E4 40 SDP Design 20000 Deale ? -- NOTE:0843 Same answer as above -- NOTE:0843 ordering of column names in NATURAL JOIN -- REFERENCE: 7.5 sr 5 SELECT * --O FROM HU.WORKS RIGHT JOIN HU.PROJ --O ON HU.WORKS.PNUM = HU.PROJ.PNUM FROM HU.WORKS JOIN HU.PROJ ON HU.PROJ.PNUM = HU.WORKS.PNUM ORDER BY 1 DESC, 2; EM&|PN&|HOURS |PN&|PNAME |PTYPE |BUDGET |CITY ------------------------------------------------------------------------- E4 |P2 |20 |P2 |CALM |Code |30000 |Vienna E4 |P4 |40 |P4 |SDP |Design|20000 |Deale E4 |P5 |80 |P5 |IRM |Test |10000 |Vienna E3 |P2 |20 |P2 |CALM |Code |30000 |Vienna E2 |P1 |40 |P1 |MXSS |Design|10000 |Deale E2 |P2 |80 |P2 |CALM |Code |30000 |Vienna E1 |P1 |40 |P1 |MXSS |Design|10000 |Deale E1 |P2 |20 |P2 |CALM |Code |30000 |Vienna E1 |P3 |80 |P3 |SDP |Test |30000 |Tampa E1 |P4 |20 |P4 |SDP |Design|20000 |Deale E1 |P5 |12 |P5 |IRM |Test |10000 |Vienna E1 |P6 |12 |P6 |PAYR |Design|50000 |Deale ij> -- PASS:0843 If 12 rows selected? -- PASS:0843 If ordered row and column values for first two rows are: ? -- PASS:0843 E4 P2 20 P2 CALM Code 30000 Vienna ? -- PASS:0843 E4 P4 40 P4 SDP Design 20000 Deale ? ROLLBACK WORK; ij> -- END TEST >>> 0843 <<< END TEST -- ********************************************* -- TEST:0844 Outer join predicates ! CREATE TABLE SEVEN_TYPES ( T_INT INTEGER, T_CHAR CHAR(10), T_SMALL SMALLINT, T_DECIMAL DECIMAL(10,2), T_REAL REAL, T_FLOAT FLOAT, T_DOUBLE DOUBLE PRECISION); 0 rows inserted/updated/deleted ij> COMMIT WORK; ij> -- setup DELETE FROM SEVEN_TYPES; 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 SEVEN_TYPES VALUES (1, 'E1',-11, 2, 3, 4, 5); 1 row inserted/updated/deleted ij> INSERT INTO SEVEN_TYPES VALUES (2, 'E2', -5, 13, 33,-444, -55); 1 row inserted/updated/deleted ij> INSERT INTO SEVEN_TYPES VALUES (3, 'E6', -3,-222,333, 44, 555); 1 row inserted/updated/deleted ij> INSERT INTO SEVEN_TYPES VALUES (12,'DUP', 0, 0, -1, 1,1E+1); 1 row inserted/updated/deleted ij> INSERT INTO SEVEN_TYPES VALUES (12,'DUP', 0, 0, -1, 1,1E+1); 1 row inserted/updated/deleted ij> -- -- NOTE:0844 BETWEEN predicate SELECT EMPNAME, CITY, T_DECIMAL FROM HU.STAFF LEFT OUTER JOIN SEVEN_TYPES ON -GRADE / 11 BETWEEN T_REAL AND T_DECIMAL ORDER BY EMPNAME; EMPNAME |CITY |T_DECIMAL ------------------------------------------------- Alice |Deale |NULL Betty |Vienna |0.00 Betty |Vienna |0.00 Carmen |Vienna |NULL Don |Deale |NULL Ed |Akron |NULL ij> -- PASS:0844 If 6 rows selected with ordered rows and column values ? -- PASS:0844 Alice Deale NULL ? -- PASS:0844 Betty Vienna 0 ? -- PASS:0844 Betty Vienna 0 ? -- PASS:0844 Carmen Vienna NULL ? -- PASS:0844 Don Deale NULL ? -- PASS:0844 Ed Akron NULL ? -- NOTE:0844 comparable CHAR types -- NOTE:0844 IN predicate, with literals and variable value SELECT T_INT, T_CHAR, EMPNAME, EMPNUM, GRADE --O FROM SEVEN_TYPES RIGHT JOIN HU.STAFF FROM SEVEN_TYPES right outer JOIN HU.STAFF ON GRADE IN (10, 11, 13) AND EMPNUM = T_CHAR ORDER BY EMPNAME, T_INT; T_INT |T_CHAR |EMPNAME |EMP&|GRADE ------------------------------------------------------ NULL |NULL |Alice |E1 |12 2 |E2 |Betty |E2 |10 NULL |NULL |Carmen |E3 |13 NULL |NULL |Don |E4 |12 NULL |NULL |Ed |E5 |13 ij> -- PASS:0844 If 5 rows selected with ordered rows and column values ? -- PASS:0844 NULL NULL Alice E1 12 ? -- PASS:0844 2 E2 Betty E2 10 ? -- PASS:0844 NULL NULL Carmen E3 13 ? -- PASS:0844 NULL NULL Don E4 12 ? -- PASS:0844 NULL NULL Ed E5 13 ? SELECT HU.STAFF.CITY,EMPNAME,PNAME,BUDGET --O FROM HU.STAFF LEFT JOIN HU.PROJ FROM HU.STAFF left outer JOIN HU.PROJ ON HU.STAFF.CITY = HU.PROJ.CITY AND HU.STAFF.CITY <> 'Vienna' AND EMPNAME <> 'Don' WHERE BUDGET > 15000 OR BUDGET IS NULL --O ORDER BY HU.STAFF.CITY, EMPNAME, BUDGET; ORDER BY 1,2,4; CITY |EMPNAME |PNAME |BUDGET -------------------------------------------------------------------- Akron |Ed |NULL |NULL Deale |Alice |SDP |20000 Deale |Alice |PAYR |50000 Deale |Don |NULL |NULL Vienna |Betty |NULL |NULL Vienna |Carmen |NULL |NULL ij> -- PASS:0844 If 6 rows selected with ordered rows and column values ? -- PASS:0844 Akron Ed NULL NULL ? -- PASS:0844 Deale Alice SDP 20000 ? -- PASS:0844 Deale Alice PAYR 50000 ? -- PASS:0844 Deale Don NULL NULL ? -- PASS:0844 Vienna Betty NULL NULL ? -- PASS:0844 Vienna Carmen NULL NULL ? -- NOTE:0844 difference between WHERE and ON SELECT HU.STAFF.CITY,EMPNAME,PNAME,BUDGET --O FROM HU.STAFF LEFT JOIN HU.PROJ FROM HU.STAFF left outer JOIN HU.PROJ ON HU.STAFF.CITY = HU.PROJ.CITY AND HU.STAFF.CITY <> 'Vienna' WHERE (BUDGET > 15000 OR BUDGET IS NULL) AND EMPNAME <> 'Don' --O ORDER BY HU.STAFF.CITY, EMPNAME, BUDGET; ORDER BY 1,2,4; CITY |EMPNAME |PNAME |BUDGET -------------------------------------------------------------------- Akron |Ed |NULL |NULL Deale |Alice |SDP |20000 Deale |Alice |PAYR |50000 Vienna |Betty |NULL |NULL Vienna |Carmen |NULL |NULL ij> -- PASS:0844 If 5 rows selected with ordered rows and column values ? -- PASS:0844 Akron Ed NULL NULL ? -- PASS:0844 Deale Alice SDP 20000 ? -- PASS:0844 Deale Alice PAYR 50000 ? -- PASS:0844 Vienna Betty NULL NULL ? -- PASS:0844 Vienna Carmen NULL NULL ? -- NOTE:0844 correlation name with self-JOIN SELECT XX.T_INT, YY.T_INT FROM SEVEN_TYPES XX RIGHT OUTER JOIN SEVEN_TYPES YY ON XX.T_INT = YY.T_INT +1 --O ORDER BY YY.T_INT; ORDER BY 2; T_INT |T_INT ----------------------- 2 |1 3 |2 NULL |3 NULL |12 NULL |12 ij> -- PASS:0844 If 5 rows selected with ordered rows and column values ? -- PASS:0844 2 1 ? -- PASS:0844 3 2 ? -- PASS:0844 NULL 3 ? -- PASS:0844 NULL 12 ? -- PASS:0844 NULL 12 ? -- NOTE:0844 nested booleans -- NOTE:0844 data types are merely comparable SELECT GRADE, T_FLOAT, T_DOUBLE --O FROM HU.STAFF LEFT JOIN SEVEN_TYPES T7 FROM HU.STAFF left outer JOIN SEVEN_TYPES T7 ON GRADE * -40 > T7.T_FLOAT OR (T_DOUBLE -542.5 < GRADE AND T_DOUBLE -541.5 > GRADE) ORDER BY GRADE; GRADE|T_FLOAT |T_DOUBLE --------------------------------------------------- 10 |-444.0 |-55.0 12 |NULL |NULL 12 |NULL |NULL 13 |44.0 |555.0 13 |44.0 |555.0 ij> -- PASS:0844 If 5 rows selected with ordered rows and column values ? -- PASS:0844 10 -444 (approximately) -55 (approximately) ? -- PASS:0844 12 NULL NULL ? -- PASS:0844 12 NULL NULL ? -- PASS:0844 13 44 (approximately) 555 (approximately) ? -- PASS:0844 13 44 (approximately) 555 (approximately) ? ROLLBACK WORK; ij> --ODROP TABLE SEVEN_TYPES CASCADE; DROP TABLE SEVEN_TYPES ; 0 rows inserted/updated/deleted ij> COMMIT WORK; ij> -- END TEST >>> 0844 <<< END TEST -- *************************************************////END-OF-MODULE ; ij>