Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
1 reply Latest Post - ‏2012-11-18T17:37:43Z by CRPence@vnet.ibm.com
KelvinLiang
KelvinLiang
1 Post
ACCEPTED ANSWER

Pinned topic Problem in RTVQMQRY

‏2012-04-25T03:13:47Z |
I tried to convert QRYDFN to SQL with RTVQMQRY, but I found that there is a problem in RTVQMQRY to identify the Left outer join while the type of join in the QRYDFN is '2' in some of the cases.

It will identify the join to be a CROSS JOIN whie the criteria belows are met:
1. More or equal to 3 files.
2. The fileds input in the 'Specify How to Join Files' are without the file ID.

May I know is this a bug/limitation in RTVQMQRY and any workaround for this if I want to convert thousands of QRYDFN to SQL?

I have exmaple below.
1) The result QRYDFN without File ID. It's converted to CROSS JOIN.

H QM4 05 Q 01 E V W E R 01 03 25/12/04 10:43 V 1001 050 V 5001 004 *HEX SELECT ALL       T02.F1FLD1, T02.F1FLD2, T02.F1FLD3, T02.ALCBAL, T02.F1CBP1, T02.ALBSP1 ,           T02.ALCYCD, ((F1AMT/100)), ((ALREB1/100)), T02.ALACTP FROM      AOCHUBQT/FILE1 T02 CROSS JOIN AOCHUBQT/FILE2 T01 CROSS JOIN AOCHUBQT/FILE3 T03 WHERE     F1FLD1 = F2FLD1 AND     F1FLD2 = F2FLD2 AND     F1FLD3 = F2FLD3 AND     F1FLD1 = F3FLD1 AND     F1FLD2 = F3FLD2 AND     F1FLD3 = F3FLD3 AND(    F1AMT/100  0 AND     F1CBP1 IN (18017, 28182)) ORDER BY  005 ASC


2) The result QRYDFN with File ID. It's converted to Left Outer Join.

H QM4 05 Q 01 E V W E R 01 03 24/12/04 18:24 V 1001 050 V 5001 004 *HEX SELECT ALL       T02.F1FLD1, T02.F1FLD2, T02.F1FLD3, T02.ALCBAL, T02.F1CBP1, T02.ALBSP1 ,           T02.ALCYCD, ((F1AMT/100)), ((ALREB1/100)), T02.ALACTP FROM      AOCHUBQT/FILE1 T02 LEFT OUTER JOIN AOCHUBQT/FILE2 T01 ON        T02.F1FLD1 = T01.F2FLD1 AND     T02.F1FLD2 = T01.F2FLD2 AND     T02.F1FLD3 = T01.F2FLD3 LEFT OUTER JOIN AOCHUBQT/FILE3 T03 ON        T02.F1FLD1 = T03.F3FLD1 AND     T02.F1FLD2 = T03.F3FLD2 AND     T02.F1FLD3 = T03.F3FLD3 WHERE     F1AMT/100  0 AND     F1CBP1 IN (18017, 28182) ORDER BY  005 ASC
Updated on 2012-11-18T17:37:43Z at 2012-11-18T17:37:43Z by CRPence@vnet.ibm.com
  • CRPence@vnet.ibm.com
    33 Posts
    ACCEPTED ANSWER

    Re: Problem in RTVQMQRY

    ‏2012-11-18T17:37:43Z  in response to KelvinLiang
    ANZQRY informs of the limitation.

    It seems that an enhancement removed the limitation for the RTVQMQRY of a *QRYDFN, i.e. use of ALWQRYDFN(*ONLY|*YES) which retrieves a Query Definition object, even if the ANZQRY was not properly updated for consistency to no longer report the limitation:
    http://archive.midrange.com/midrange-l/201209/msg00531.html
    The enhancement apparently effects use of JOIN syntax which the OP shows already is seen, and apparently LEFT OUTER JOIN and EXCEPTION JOIN capability to represent the Type of Join defined in the Query/400 Query Definition Object.

    The messages at the above link gives the v6r1 and v7r1 PTF numbers, which presumably are the match for the v5r4 PTF SI41496 which gives effectively no indication of what the PTF provides :-(