We are moving from DataMigrator 7.1.4 to version 7.7.04 and have problems with ‘subselects’ in a DB2 query.

Here are some general notes:

1. We are running the exact same code and metadata in the new environment (which is DataMigrator 7.7.04) that we were in the old environment (which is DataMigrator 7.1.4).

2. We are accessing the exact same database.

3. The data base is DB2 version 9.7.

4. We brought the code into the GUI tool (Data Management Console) and re-saved it thinking that possibly the syntax has changed from one version to the next but that did not make any difference.

5. The release of the DataMigrator and the Data Management Console we are having a problem with is: 'R717704B' and Gen Number 643.

Here is the query in the flow that has the problem:

SQL PREPARE SQLIN FROM
SELECT
T2.POLICY_ID ,
T2.EFFECTIVE_DT ,
T2.EXPIRATION_DT ,
T2.FORM_ID ,
T2.FRM_SEQ_NBR ,
T2.FORM_EDITION_DT ,
T1.POLICY_NBR ,
T1.POLICY_VERSION_NBR ,
T1.POLICY_SUFFIX ,
T1.POL_EXP_DT ,
T1.ACCTG_YEAR ,
T1.ACCTG_MONTH ,
T1.LOB_SUB_CD
FROM
(MF_DTL_POL_STG1A T1 INNER JOIN POLICY_FORMS T2
ON
T1.SOURCE_POLICY_ID = T2.POLICY_ID AND
T1.LOB_SUB_CD IN ( 'FP' , 'SF' , 'PL' , 'MH' , 'FR' , 'HP' , 'ACV' , 'APV' ) AND
T1.POL_EXP_DT = T2.PLN_EXP_DT AND
T2.EFFECTIVE_DT <'&&TOO_DATE' AND
NOT ( T1.POL_STATUS_CD = 'I' AND
T2.EFFECTIVE_DT = T2.EXPIRATION_DT AND
T2.EFFECTIVE_TYPE_CD IN ( 'A' , 'X' ) AND
T2.LAST_MDF_ACY_TS <'&&TOO_DATE_TS' ) AND
T2.ISSUE_ACY_TS = ( SELECT
MAX(T3.ISSUE_ACY_TS )
FROM
POLICY_FORMS T3
WHERE
T3.POLICY_ID = T2.POLICY_ID AND
T3.PLN_EXP_DT = T2.PLN_EXP_DT AND
T3.EFFECTIVE_DT <'&&TOO_DATE' AND
T3.FORM_ID = T2.FORM_ID AND
T3.FRM_SEQ_NBR = T2.FRM_SEQ_NBR AND
T3.ISSUE_ACY_TS <'&&TOO_DATE_TS'
))


Here is the error I am getting:

10/26/2012 07:09:55 IRFD0 Successfully connected as user "isp3489"
10/26/2012 07:10:50 IRFD0 (ICM18122) Request - __WCFEX (Owner: isp3489) submitted.
10/26/2012 07:10:50 IRFD0 (ICM18015) DEP_1: procedure irf_amp_vars started.
10/26/2012 07:10:50 IRFD0 (ICM18039) DEP_1 irf_amp_vars Return Code = 0
10/26/2012 07:10:50 IRFD0 (ICM18742) irf_policy_form type DB2 Existing target
10/26/2012 07:10:50 IRFD0 (ICM18429) Issuing PREPARE
10/26/2012 07:10:50 IRFD0 (FOC14010) NOT A VALID COLUMN NAME: T2.POLICY_ID
10/26/2012 07:10:50 IRFD0 (FOC14010) NOT A VALID COLUMN NAME: T2.PLN_EXP_DT
10/26/2012 07:10:50 IRFD0 (FOC14010) NOT A VALID COLUMN NAME: T2.FORM_ID
10/26/2012 07:10:50 IRFD0 (FOC14010) NOT A VALID COLUMN NAME: T2.FRM_SEQ_NBR
10/26/2012 07:10:50 IRFD0 (ICM18040) Return Code = 14010
10/26/2012 07:10:50 IRFD0 (ICM18076) Request: __WCFEX - finished processing
10/26/2012 07:10:50 IRFD0 (ICM18007) CPU Time : 47

It appears to have a problem with the columns referenced in the subselect.

Thank you for your help.


WebFOCUS 7.7
Windows, All Outputs

The sub-select in the join condition should be fine. You could verify that's the problem by removing it and testing the SQL and seeing if you still get an error.

If you have not already done so please open a hottrack case and upload the synonyms for the three source tables so that hotline can test it.

Cliff, thank you for your response. We have already tested the query and it works fine outside of DataMigrator.

Also, as a result of additional testing, we found that the problem seems to be because of the inner join. When we changed this to a cross join, the flow worked. I realize that we can accomplish the same thing with a cross join rather than an inner join, but why should we have to change many flows when it worked before.

Plus, problems like this are a big concern to our supervisors when the code ran perfectly in a earlier version.

Again, thank you for your help.

Please check that you are getting the results you expect. A cross join returns every combination of rows from the left and right source; join criteria are not used.

Cliff,

It returns everything correctly. We did have to code the 'WHERE' statement to handle everything correctly. Unfortunally if you bring this back into the GUI tool, we lose the 'WHERE' statement. Obviously this is a problem.

Thanks,