Hi, i've a big problem with Data Migator and Left Outer Join .

Well, suppose that i've 3 .foc files.

File A contains this information:

IST FIL NDG
47 010 1
47 011 2
47 013 3
47 014 4
47 015 5
46 415 1

 
FILENAME=A, SUFFIX=XFOCUS $
DATASET=D:\IBI\APPS\REPORTING\CASESTUDY\JOIN\A.foc, $
SEGNAME=A, SEGTYPE=S1, $
FIELDNAME=IST, FORMAT=I6, $
FIELDNAME=FIL, FORMAT=A3,   $
FIELDNAME=NDG, FORMAT=P20, $

the file .Foc named B contains this information:

IST FIL NOME
47 010 FILIALE 10
47 011 FILIALE 11
47 631 FILIALE 631

FILENAME=B, SUFFIX=XFOCUS, $
DATASET=D:\IBI\APPS\REPORTING\CASESTUDY\JOIN\B.foc, $
SEGNAME=B,  SEGTYPE=S1, $
FIELDNAME=IST, FORMAT=I6, $
FIELDNAME=FIL, FORMAT=A3,   $
FIELDNAME=NOME, FORMAT=A20, $  

and the file C contains :

IST FIL LINKS
47 010 WWW.GOOGLE.IT
44 810 WWW.BING.IT

  

FILENAME=C, SUFFIX=XFOCUS, $
DATASET=D:\IBI\APPS\REPORTING\CASESTUDY\JOIN\C.foc, $
SEGNAME=C,  SEGTYPE=S1, $
FIELDNAME=IST, FORMAT=I6, $
FIELDNAME=FIL, FORMAT=A3,   $
FIELDNAME=LINK, FORMAT=A20, $

.

my data flow is:



and the LAST SELECT STATEMENT is :

 

SELECT 
   T3.IST ,  
   T3.FIL ,  
   T3.NDG ,  
   T3.NOME ,  
   T4.LINK  
FROM 
   ((SELECT 
       T1.IST ,  
       T1.FIL ,  
       T1.NDG ,  
       T2.NOME  
    FROM 
       (a T1  LEFT OUTER JOIN b T2  
           ON 
          T1.IST = T2.IST  AND  
          T1.FIL = T2.FIL )  
    ) T3  
     LEFT OUTER JOIN c T4  
       ON 
      T3.IST = T4.IST  AND  
      T3.FIL = T4.FIL )
 

when I run the flow the result is wrong. I' ve only 1 record in target file T

All join component are in left outer join and the first file (A.Foc) contains 6 record therefore the result should be 6 record.

In fact, if I copy the SELECT STATEMENT on Microsoft SQL server Management Studio , I get the right result.






This is a log of data flow:

(ICM18451) HOLD file will be created for output file named: SQLIN.
1
0 NUMBER OF RECORDS IN TABLE= 3 LINES= 3
0
1
0 NUMBER OF RECORDS IN TABLE= 2 LINES= 2
0
1
0 NUMBER OF RECORDS IN TABLE= 2 LINES= 2
0
0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1
(ICM18054) Issuing CREATE and DROP TABLE for t
(ICM18743) Starting Load
0 WARNING.. ON MATCH INCLUDE INPUTS DUPLICATE SEGMENTS
(FOC1291) RECORDS AFFECTED DURING CURRENT REQUEST : 1/INSERT
(ICM18041) -- stats for source file
(ICM18516) 1 : Row(s) processed by job
(ICM18519) 0 : Row(s) rejected due to format error
(ICM18372) -- stats for target file: reporting/casestudy/join/t
(ICM18516) 1 : Row(s) processed by job
(ICM18514) 1 : Row(s) accepted by target table
(ICM18515) 1 : Row(s) inserted into target table
(ICM18517) 0 : Row(s) updated in target table
(ICM18518) 0 : Row(s) deleted from target table
(ICM18520) 0 : Row(s) rejected due to validation errors
(ICM18521) 0 : Row(s) rejected due to no match
(ICM18522) 0 : Row(s) rejected because duplicate exist
(ICM18808) 0 : Row(s) rejected due to DBMS error
(ICM18744) Ending Load
(ICM18040) Return Code = 0
(ICM18076) Request: reporting/casestudy/join/test_flow - finished processing
(ICM18007) CPU Time : 47
(ICM18031) Finished
(ICM18072) Elapsed run time 0:00:00


Where is the problem? why has this behavior?

For your testing purpose below the load data script and the .fex dataflow.

Thanks in advance.

LOAD script:

 
FILEDEF MASTER DISK D:\IBI\APPS\REPORTING\CASESTUDY\JOIN\A.mas
-RUN
 
-WRITE MASTER FILENAME=A, SUFFIX=XFOCUS $
-WRITE MASTER DATASET=D:\IBI\APPS\REPORTING\CASESTUDY\JOIN\A.foc, $
-WRITE MASTER SEGNAME=A, SEGTYPE=S1, $
-WRITE MASTER FIELDNAME=IST, FORMAT=I6, $
-WRITE MASTER FIELDNAME=FIL, FORMAT=A3,   $
-WRITE MASTER FIELDNAME=NDG, FORMAT=P20, $
 
-RUN
 
 
SET EMGSRV=OFF                                                                  
CREATE FILE A                                                              
SET EMGSRV=ON    
 
MODIFY FILE A                                                            
FREEFORM IST FIL NDG 
MATCH IST FIL NDG                                                                   
ON MATCH REJECT                                                                 
ON NOMATCH INCLUDE                                                              
CHECK OFF                                                                       
DATA                                                                            
47,010,1,$               
47,011,2,$  
47,013,3,$   
47,014,4,$           
47,015,5,$  
46,415,1,$ 
END
 
FILEDEF MASTER DISK D:\IBI\APPS\REPORTING\CASESTUDY\JOIN\B.mas
-RUN
 
-WRITE MASTER FILENAME=B, SUFFIX=XFOCUS, $
-WRITE MASTER DATASET=D:\IBI\APPS\REPORTING\CASESTUDY\JOIN\B.foc, $
-WRITE MASTER SEGNAME=B,  SEGTYPE=S1, $
-WRITE MASTER FIELDNAME=IST, FORMAT=I6, $
-WRITE MASTER FIELDNAME=FIL, FORMAT=A3,   $
-WRITE MASTER FIELDNAME=NOME, FORMAT=A20, $
 
-RUN
 
 
SET EMGSRV=OFF                                                                  
CREATE FILE B                                                              
SET EMGSRV=ON    
 
MODIFY FILE B                                                            
FREEFORM IST FIL NOME 
MATCH IST FIL NOME                                                                   
ON MATCH REJECT                                                                 
ON NOMATCH INCLUDE                                                              
CHECK OFF                                                                       
DATA                                                                            
47,010,FILIALE 10,$               
47,011,FILIALE 11,$
47,631,FILIALE 631,$
END
 
FILEDEF MASTER DISK D:\IBI\APPS\REPORTING\CASESTUDY\JOIN\C.mas
-RUN
 
-WRITE MASTER FILENAME=C, SUFFIX=XFOCUS, $
-WRITE MASTER DATASET=D:\IBI\APPS\REPORTING\CASESTUDY\JOIN\C.foc, $
-WRITE MASTER SEGNAME=C,  SEGTYPE=S1, $
-WRITE MASTER FIELDNAME=IST, FORMAT=I6, $
-WRITE MASTER FIELDNAME=FIL, FORMAT=A3,   $
-WRITE MASTER FIELDNAME=LINK, FORMAT=A20, $
 
-RUN
 
 
SET EMGSRV=OFF                                                                  
CREATE FILE C                                                              
SET EMGSRV=ON    
 
MODIFY FILE C                                                            
FREEFORM IST FIL LINK 
MATCH IST FIL LINK                                                                   
ON MATCH REJECT                                                                 
ON NOMATCH INCLUDE                                                              
CHECK OFF                                                                       
DATA                                                                            
47,010,WWW.GOOGLE.IT,$
44,810,WWW.BING.IT,$
                                      

the flow

  
-*DM_JOB_TYPE=1 
-*DM_USERID=Herojos 
 
-*************************************************** 
 
-:START_PRC 
SET PANEL=9999 
SET MORE=OFF 
SET 2PARTNAME=ON 
-RUN 
 
-*[Variables to Control Request] 
-SET &&CM__TARGET = 't'; 
-SET &&CM__AUTHOR = 'Herojos'; 
-SET &&CM__REQUEST = '&FOCFEXNAME.EVAL'; 
-SET &&CM__RETURN = 0; 
-SET &&CM__FOCCPU = &FOCCPU.EVAL; 
-DEFAULT &DBMSERROR = 10000000 
-DEFAULT &STARTAT = 0 
-DEFAULT &STOPAT  = 1000000000 
 
-TYPE  (ICM18122) Request - &FOCFEXNAME (Owner: Herojos) submitted. 
-GOTO :DEP_MAIN; 
 
-:DEP_MAIN 
-TYPE  (ICM18741) reporting/casestudy/join/t type Delimited Flat File New target 
 
SET CASESTAT=EXTENDED 
SQL SET UPCASE=OFF; END 
 
-TYPE  (ICM18429) Issuing PREPARE 
 
SQL PREPARE SQLIN FROM 
SELECT 
   T3.IST , 
   T3.FIL , 
   T3.NDG , 
   T3.NOME , 
   T4.LINK 
FROM 
   ((SELECT 
       T1.IST , 
       T1.FIL , 
       T1.NDG , 
       T2.NOME 
    FROM 
       (a T1  LEFT OUTER JOIN b T2 
           ON 
          T1.IST = T2.IST  AND 
          T1.FIL = T2.FIL ) 
    ) T3 
     LEFT OUTER JOIN c T4 
       ON 
      T3.IST = T4.IST  AND 
      T3.FIL = T4.FIL ) 
END 
-RUN 
-SET &&CM__RETURN = IF &FOCERRNUM EQ 14104 THEN 0 ELSE &FOCERRNUM; 
-IF (&&CM__RETURN NE 0) GOTO :ENDJOB; 
 
-IF (&SQLAPT EQ 'APT') GOTO :SKIPHOLD; 
 
-TYPE  (ICM18440) Request will process data via NON-Pass Through (NON-APT) 
-TYPE  (ICM18451) HOLD file will be created for output file named: SQLIN. 
 
SQL EXECUTE SQLIN; 
TABLE ON TABLE HOLD AS  
SQLIN 
 FORMAT DATREC 
IF RECORDLIMIT EQ &STOPAT  
END 
-RUN 
-SET &&CM__RETURN = IF &LINES EQ 0 THEN 18708 ELSE &FOCERRNUM; 
-IF (&&CM__RETURN NE 0) GOTO :ENDJOB; 
-:SKIPHOLD 
 
EX -lines 11 EDAPUT MASTER,reporting/casestudy/join/t,CV,FILE, 
FILENAME=t, SUFFIX=DFIX    , 
 DATASET=reporting/casestudy/join/t.ftm, $ 
  SEGMENT=T, SEGTYPE=S0, $ 
    FIELDNAME=IST, ALIAS=IST, USAGE=I6, ACTUAL=A6, $ 
    FIELDNAME=FIL, ALIAS=FIL, USAGE=A3, ACTUAL=A3, $ 
    FIELDNAME=NDG, ALIAS=NDG, USAGE=P20, ACTUAL=A20, $ 
    FIELDNAME=NOME, ALIAS=NOME, USAGE=A20, ACTUAL=A20, 
      MISSING=ON, $ 
    FIELDNAME=LINK, ALIAS=LINK, USAGE=A20, ACTUAL=A20, 
      MISSING=ON, $ 
 
EX -lines 2 EDAPUT ACCESS,reporting/casestudy/join/t,CV,FILE, 
SEGNAME=T, DELIMITER=TAB, HEADER=NO, $ 
 
 
-TYPE  (ICM18054) Issuing CREATE and DROP TABLE for t 
CREATE FILE reporting/casestudy/join/t DROP 
-RUN 
-SET &&CM__RETURN = &FOCERRNUM; 
-IF (&&CM__RETURN NE 0) GOTO :ENDJOB; 
 
-TYPE  (ICM18743) Starting Load 
 
MODIFY FILE reporting/casestudy/join/t 
 FIXFORM FROM SQLIN ALIAS PROPAGATE 
 GOTO MATCHIT1 
  
 CASE MATCHIT1 
  COMPUTE  
   IST/I6=E01; 
   FIL/A3=E02; 
   NDG/P20=E03; 
   NOME/A20 MISSING ON=E04; 
   LINK/A20 MISSING ON=E05; 
  MATCH IST 
   ON MATCH INCLUDE 
   ON NOMATCH INCLUDE 
  GOTO TOP 
 ENDCASE 
  
 CASE AT START 
  START &STARTAT 
  STOP  &STOPAT  
  STOP  DBMSERRORS &DBMSERROR 
  
  LOG DBMSERR MSG OFF 
  LOG DUPL    MSG OFF 
  LOG INVALID MSG OFF 
  LOG NOMATCH MSG OFF 
  LOG FORMAT  MSG OFF 
  LOG ACCEPT  MSG OFF 
  LOG TRANS   MSG OFF 
 ENDCASE 
 DATA VIA SQLGET 
END 
-RUN 
 
-TYPE  (ICM18744) Ending Load 
-SET &&CM__RETURN = IF &TRANS EQ 0 THEN 18708 ELSE &FOCERRNUM; 
 
-:ENDJOB 
-TYPE  (ICM18040) Return Code = &&CM__RETURN 
 
SET CASESTAT=OFF 
SQL SET UPCASE=ON; END 
SET EMGSRV=OFF 
SQL PURGE SQLIN; 
END 
-RUN 
FI SQLIN CLEAR 
 
SET EMGSRV=ON 
 
-TYPE  (ICM18076) Request: &FOCFEXNAME - finished processing 
 
-SET &&CM__FOCCPU = &FOCCPU.EVAL - &&CM__FOCCPU; 
-TYPE  (ICM18007) CPU Time : &&CM__FOCCPU 
 
-*[Main Condition] 
-*[Main End] 
 
-*[Dependence] 
-:ENDDEP 
SET PANEL=0 
SET MORE=ON 
SET 2PARTNAME=OFF 
-RUN 

This message has been edited. Last edited by: <Kathryn Henning>,

I have open a case on infor response. In this moment the status is: "IBI Reserching" . The last comment of IBI is:

----------------------------------------------
Updated: Jun 17, 2014 02:45 PM
Status/Action: RR/

Support Group: EDA Rep: MICHELLE ---(MXG2)
Severity: 3 - Application Issue

Hi Giuseppe,
I did some testing with one of the Product Manager's and we discovered that
the only way to get this to behave as you would 'expect' it to, is to do
2 seperate dataflows.
1 dataflow can join A to B and produce an XFOCUS target - with the left outer
and the 2nd dataflow can join the new XFOCUS file to C.
This will give you the expected results.
In the meantime, I am sending the issue upstairs for the division to review.
Regards,
Michelle.

------------------------------------------

Obviously this solution and certainly not usable. If a flow has 5 join means that there are at least five flow. It would be unmanageable.

I think it's a problem of translation of JOIN command to the adapter Focus.

As soon as I update more news.

Hi All,

This issue has been sent to programming to address. Please note that this issue does not occur if the data sources are relational.

Thanks and regards,

Kathryn

These are XFOCUS tables, if you remove the extra select and just join the tables to each other, it will do what you want.

It is correct, if I remove the SQL statment works correctly.

Unfortunately, this scenario is simplified.

In real scenario, the SQL block contains: transformations and calculations with a SELECT MAX and Group By.

It is therefore necessary to have the extra SQL block

Next time put an example together that is representative of your scenario.