Data Migrator Assistance needed when using function Lookup_MFD.
Statement: (SELECT MAX(SQ.ENTRY_NUM) FROM TARGET_TABLE SQ WHERE SQ.INPUT_ID = SOURCE.ID AND SQ.TYPE_ID = SOURCE.TYPE_ID)
ENTRY_NUM = ENTRY_NUM + 1
Does the Lookup_MFD build the result set 1 time initally, or it it executed for each source record?
What I'm seeing is the function works fine for the first record in the group, but not for subsequent records in the same group. The inital value for the group is retuned for each record, causing duplicate key violation.
Target Table (After applying my source data should look like):
INPUT_ID TYPE_ID ENTRY_NUM 1 A 1 1 A 2 (1st increment occurs) 1 A 3 (2nd increment does not occur - ENTRY_NUM= 2 is returned again) 2 A 1 2 A 2 2 B 1
Please let me know how I shoul handle this situation.
ThanksThis message has been edited. Last edited by: Meena,
WebFOCUS 7.6 Windows, All Outputs
September 23, 2013, 03:50 PM
Clif
The select is passed through to the underlying relational database which optimizes the sub-select.
I'm sorry the only way I've been able to come up with to address this is to run the flow iteratively, once for each input row. If your source is an RDBMS table that means generating the row number:
SELECT
T2.ID ,
T2.TYPE_ID ,
T2.MAX_ENTRY
FROM
(SELECT
T1.ID ,
T1.TYPE_ID ,
1 + (SELECT MAX(SQ.ENTRY_NUM ) FROM
TARGET SQ WHERE SQ.TYPE_ID = T1.TYPE_ID AND
SQ.INPUT_ID = T1.ID ) AS MAX_ENTRY ,
DB_EXPR(ROW_NUMBER() OVER (ORDER BY ID,TYPE_ID) ) AS ROWN
FROM
sourcet T1
) T2
WHERE
T2.ROWN = &NUM
Then you could use the Iterator to run once for each row number, 1 through 4, supplying values for &NUM.
N/A
September 24, 2013, 12:02 PM
Meena
Thanks Clif for your response.
If this isn't possible. Is it possible to use function LAST or something, to say if the current value of a field is different from the value of the field from the previous record reset counter.
Thanks for you help in advance
WebFOCUS 7.6 Windows, All Outputs
September 24, 2013, 01:35 PM
Clif
I couldn't figure out a way to use LAST to solve the problem as you've described it. Perhaps someone else who views the forums could.