Outer Joins in Reports 
This technote explains how to do an outer join in Superbase using Custom Reports with one or more linked files. The concept of outer join can be described as follows: Using an outer join, you can include records from a file for which there are no corresponding records in another linked file. For more information on outer joins, refer to page 5-10 in the Working with Data manual. The outer join option is only available when the Query Optimizer is functioning. To use the Query Optimizer with the report, select Use Query optimizer in the Report Properties dialog box. Refer to page 16-3 in Designing Forms and Custom Reports manual for more information.
Outer-Join between Master and one Detail file
Consider the Product (Master) and Customer (Detail) files shown below:
PRODUCT
	CustomerNo	AccountNo	Product
	123		111	Pencil
	234		222	Pen
	123		333	Bulbs
	456		111	Tubes
CUSTOMER
	CustomerNo	Name	
	123		Bill	
	234		Jo	
	789		Mark	

To output all Product records, whether or not they have matching customer record, you use an outer join. The result is output that looks like this:
Report Output_Ordered on CustomerNo.
	CustomerNo	AccountNo	Product	Name
	123		111	Pencil	Bill
	123		333	Bulbs	Bill
	234		222	Pen	Jo
	456		111	Tubes	
How to create the report
Create a Product file with the following fields: CustomerNo (Integer-Indexed), AccountNo (Integer-Indexed), Product (Text).
Create a Customer file with the following fields: CustomerNo (Integer-Unique Index), Name (Text).
Create a new report and place CustomerNo.Product, AccountNo.Product, Product.Product, and Name.Customer in the body of the report. 
Choose File Links from the Define menu and link the two files by the CustomerNo field.
Save the report and name it Product.
Exit the Report Designer and return to Superbase. Choose Edit from the Program menu  and open the Product.sbp file.
In the WHERE clause, in the SELECT section (towards the end of the program), make the following modification:
		WHERE CustomerNo.PRODUCT * = CustomerNo.CUSTOMER
	8.	If the Query Optimizer is off, turn it on by modifying the statement SET QUERY OFF to 		SET QUERY ON.
	9. Save the program and run the report to get the desired output.
Using an outer join between one master and two detail files
Consider the Product (Master) and Customer (Detail1) files along with the Company (Detail2) file shown below:
COMPANY
	AccountNo	CompanyName	
	111		SPC	
	333		Tech	
	888		Quartz	

To output all Product records, whether or not they have matching records either in Company or Customer, use double outer join. The resulting output looks like this:
	Report Output_Ordered on CustomerNo.
	CustomerNo	AccountNo	Product	Name	CompanyName
	123		111	Pencil	Bill	SPC
	123		333	Bulbs	Bill	Tech
	234		222	Pen	Jo
	456		111	Tubes		SPC

How to create the report
Create a Company file with the following fields: AccountNo (Integer-Unique Index), CompanyName (Text).
Create a new report and open the Product file.
Place CustomerNo.Product, AccountNo.Product, and Product.Product in the body of the report. 
Place two calculated fields, field1$ and field2$, on the body of the report to hold Name.Customer and CompanyName.Company information.
Attach Exist1$() and Exist2$() calculations to the variables respectively.
Save the report and name it Product.
Exit the Report Designer and return to Superbase and open the program Product.sbp in the Program Editor.
Make the following modifications in the SUB main()
           SUB main()
 		OPEN FILE "company"
 		OPEN FILE "customer"
 		CALL Product()
	END SUB 

	9. Add the following two functions right below 
	FUNCTION Exist1$()
		IF EXISTS (customerno.product,customerno.customer) THEN 
   			SELECT KEY customerno.product FILE "customer" INDEX 	
						customerno.customer
			Exist1$ = name.customer
 		ELSE 
   			Exist1$ = ""
 		END IF 
 	END FUNCTION 

	FUNCTION Exist2$()
 		IF EXISTS (accountno.product,accountno.company) THEN 
   			SELECT KEY accountno.product FILE "company" INDEX 	
						accountno.company
   			Exist2$ = companyname.company
 		ELSE 
   			Exist2$ = ""
 		END IF 
 	END FUNCTION 

8. Save the program and run the report to get the desired output.
For more information about Custom Reports, see technote number 4040, "Debugging Custom Reports" which discusses the differences of Save and Save As Program. 
You can access this technote using the SPC ReadyReference System, (408) 988-6126 or (608)274-7284. 
Technotes are also available on the SPC BBS, (408) 986-1272 (9600 & up) (408) 986-0342 (2400 & below), as well as the SPC forum on CompuServe.





















Program:	Superbase	
Versions:	2.0
Date:	February 7, 1994		
D
Date: