Re: finding duplicates via sql-help

Yes indeed - but things like LIST in the record selection are rendered with IN(...) - nice.

There probably need to be some parentheses added for the JOINs, what with the new recognition of standards. This example came from a V5R4 box, so maybe the newer releases do it better.

I have not tested how ANDs and ORs are rendered - very clumsy in Query/i, would be nice if they got nicely nested. Another day for that, eh?

Vern

On 9/14/2012 12:17 PM, rob@xxxxxxxxx wrote:

That's good news, with some area for improvement...


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