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

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
Function: Lookup_MFD
 Login/Join
 
Member
posted
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,


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 8 | Registered: June 06, 2013Reply With QuoteReport This Post
Guru
posted Hide Post
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.
 
Posts: 383 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 8 | Registered: June 06, 2013Reply With QuoteReport This Post
Guru
posted Hide Post
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.
 
Posts: 383 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 


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