Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    iWay 'subselect' problem
Go
New
Search
Notify
Tools
Reply
  
iWay 'subselect' problem
 Login/Join
 
Member
posted
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


WebFOCUS 7.7
Windows, All Outputs
 
Posts: 12 | Location: Columbia Tennessee | Registered: June 13, 2012Reply With QuoteReport This Post
Guru
posted Hide Post
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.
 
Posts: 382 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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.


WebFOCUS 7.7
Windows, All Outputs
 
Posts: 12 | Location: Columbia Tennessee | Registered: June 13, 2012Reply With QuoteReport This Post
Guru
posted Hide Post
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.
 
Posts: 382 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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,


WebFOCUS 7.7
Windows, All Outputs
 
Posts: 12 | Location: Columbia Tennessee | Registered: June 13, 2012Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    iWay 'subselect' problem

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.