That's good news, with some area for improvement...
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive Garrett, IN 46738 Ship to: Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com From: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>, Date: 09/14/2012 01:11 PM Subject: Re: finding duplicates via sql-help Sent by: midrange-l-bounces@xxxxxxxxxxxx There are PTFs for V5R4 and up that have changed how RTVMQRY works. First of all, it now handles type 2 and 3 JOINs correctly - for the longest time, they were treated as type 1s. And they now produce statements using JOIN syntax, not WHERE syntax. Here's the result for, I think, a type 2 - H QM4 05 Q 01 E V W E R 01 03 12/09/14 11:56 V 1001 050 V 5001 004 *HEX SELECT ALL T01.DEPTNO, T01.DEPTNAME, T01.MGRNO, T01.ADMRDEPT, T01.LOCATION, T02.EMPNO, T02.FIRSTNME, T02.MIDINIT, T02.LASTNAME, T02.WORKDEPT, T02.PHONENO, T02.HIREDATE, T02.JOB, T02.EDLEVEL, T02.SEX, T02.BIRTHDATE, T02.SALARY, T02.BONUS, T02.COMM, T03.EMPNO, T03.PROJNO, T03.ACTNO, T03.EMPTIME, T03.EMSTDATE, T03.EMENDATE FROM LABQMXX/DEPARTMENT T01 LEFT OUTER JOIN LABQMXX/EMPLOYEE T02 ON T01.DEPTNO = T02.WORKDEPT LEFT OUTER JOIN LABQMXX/EMPPROJACT T03 ON T02.EMPNO = T03.EMPNO WHERE T01.DEPTNO = 'A00' The bug was finally fixed - it has been far too long. Still ANZQRY uses a message about the JOIN types that is bogus - at least it did, last year. I still strongly recommend using RTVQMQRY to move from QRYDFNs to using SQL. It can even turn merge fields into substitution variables, which is really cool, IMO. The PTFs were as follows, as of April, 2011 - they may be superseded, of course. V5R4 SI42309 6.1 SI42345 7.1 SI42363 Vern
amazon.com ads help fund midrange.com operations