Conference ilbbak::ibi_focus

373.0. "ON TABLE HOLD-RDB w.JOIN PROB" by FDCV09::BCOX () Mon Oct 22 1990 16:35

FOCUS 5.2.1
PROBLEM STATEMENT
-----------------
    Joining more than three(3) RDB tables, together with a HOLD file statement
produces information different from using a PRINT command without a HOLD file.

1)	RDB five(5) Table join:

	First joins embedded
		DBA to REG 	VIA DBA_NO
		REG to GRANT	VIA DBA/REG_NO
(DBA is PARENT of REG,  REG is PARENT of GRANT)


	Dynamic joins
		REG to ALL HWPO	VIA HW_PO_NO
		REG to ALL SWPO	VIA SW_PO_NO

N.B. Embedding the dynamic joins does not affect the outcome.

2)	PRINT Name 	from DBA
	      Reg 	from REG
	      Hdwr  $'s from HWPO
	      Sftwr $'s from SWPO
	      Grant $'s from GRANT

3)	ON TABLE HOLD AS TEST99
	END

4)	TABLE FILE TEST99

5)	PRINT Name 	from DBA
	      Reg	from REG
	      Hdwr  $'s from HWPO
	      Sftwr $'s from SWPO
	      Grant $'s from GRANT
        END

6)      All data is accounted for and formatted correctly

HOWEVER:

	Out of 168 line items three(3) Grant $ amounts were associated
	with the wrong REG_NO's and therefore the wrong DBA's.

	If no intermediate file is created; in this case TEST99, the data
	prints correctly.

	If Only the DBA to REG to GRANT joins are performed the data is
	also correct.

	The above example was also tested using a SAVE followed by a FIN
	and a subsequent Typeout of the saved file shows the data to be
	saved incorrectly.

	Two points:

	1) Any insight on this syndrome would be appreciated as it is
	still desirable to isolate the user from all of the data elements
	in all of the tables both for ease of use and security.

	2) Is a caveat. Since only 2% of the data was incorrect and since
	the control totals are correct as well as the format only an
	exhaustive analysis will determine if correct relationships were
	established and maintained throughout the FOCUS procedure.

    Have you checked out the fact that a hold table treats missing data
    differently than a straight table??

    SET ALL = ON is the first statement in the .FEX.
    Could you please explain further what you mean by missing data ?
    In my case it is non-existent records.
    			Thanx,
    
    			Bob C.

    To solve this type of problem I can think of either one of two
    approaches:
       1. design the tables and the logical relationships between them 
    	  so there is one logical retrieval path for all the records 
    	  in your request.  FOCUS has a CHECK FILE .... RETREIVAL PICTURE
    	  command to see what the path or paths are.
    
      2. If your request requires records from multiple paths, go down 
    	 each path put the records in a hold file and merge the hold
    	 files with MATCH FILE  command.