PM08888: Incorrect SQL results returned when the same array is used in the same SQL statement with different index variables

APAR status

• Closed as program error.

Error description

• Rational Business Developer V7.5.1.4:
  
  Running the EGL generated COBOL, incorrect SQL results are
  returned when the same array is used in the same SQL statement
  with different index variables.
  
  The following is an example of the code causing this problem
  copied from the same SQL statement:
  
    COALESCE ( SUM (
      CASE WHEN T1.PLAN1 = :TAB_PLN_J1[INDEX1]
           AND  T1.PLAN2 = :TAB_PLN_M1[INDEX1]
           THEN T1.PLN_BEG_IKW
           ELSE 0
      END )  , 0 )           AS PLN_BEG_IKW,
    COALESCE ( SUM (
      CASE WHEN T1.PLAN1 = :TAB_PLN_J1[INDEX2]
            AND T1.PLAN2 = :TAB_PLN_M1[INDEX2]
           THEN T1.PLN_ENDE_IKW
           ELSE 0
      END )  , 0 )           AS PLN_ENDE_IKW
  
  The arrays :TAB_PLN_J1 and :TAB_PLN_M1 are used in the same SQL
  statement with different index variables.  Currently, the same
  temporary variable is being used incorrectly for both values.
  
  For example, the same temporary variable is being used for both
  :TAB_PLN_J1[INDEX1] and :TAB_PLN_J1[INDEX2].  Different
  temporary variables should be used in each case.  The same is
  true for :TAB_PLN_M1[INDEX1] and :TAB_PLN_M1[INDEX2].
  
  The following is the generated COBOL which shows the same
  temporary variable is used:
  
    PLN_REDUKT_IKW, COALESCE ( SUM ( CASE WHEN T1.PLAN1 =
    :EZESQL-HOST-VARS.EZESQL-HST-1
    :EZESQL-HOST-VARS.EZESQL-HST-1-I AND T1.PLAN2 =
    :EZESQL-HOST-VARS.EZESQL-HST-2
    :EZESQL-HOST-VARS.EZESQL-HST-2-I THEN T1.PLN_BEG_IKW
    ELSE 0 END ) , 0 ) AS PLN_BEG_IKW, COALESCE ( SUM ( CASE
    WHEN T1.PLAN1 =
    :EZESQL-HOST-VARS.EZESQL-HST-1
    :EZESQL-HOST-VARS.EZESQL-HST-1-I AND T1.PLAN2 =
    :EZESQL-HOST-VARS.EZESQL-HST-2
    :EZESQL-HOST-VARS.EZESQL-HST-2-I THEN T1.PLN_ENDE_IKW
  

Local fix

• A workaround is to use hardcoded values instead of variables for
  the index value.
  
  A fixtest is available on request from support.
  

Problem summary

• The temporary SQL host variable being created by EGL was
  being reused in the same SQL statement, when the same array
  was being used with different index variables.
  

Problem conclusion

• Software updated. Fixtest available.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  RBD EGL FOR COB

• Fixed component ID

  5724S5001

Applicable component levels

• R751 PSN

     UP