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 Unique Index: INPUT_ID, TYPE_ID, ENTRY_NUM


Source Table:

ID TYPE_ID
1 A
1 A
2 A
2 B

Target Table (Inital):

INPUT_ID TYPE_ID ENTRY_NUM
1 A 1
2 A 1

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.

Thanks

This message has been edited. Last edited by: Meena,

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.

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

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.