I have been reading posts on Left_Outer joins for almost an hour and can not figure out how to resolve my issue. I have a Left_Outer Join that returns the proper data if I use a "TO UNIQUE" but I really need to have a "TO MULTIPLE" for the program to work correctly. I know this sounds contradictory, and why not just use the "TO UNIQUE", but it causes another portion of my code to fail if I don't use a "TO MULTIPLE". It's dropping one record that is duplicated on the JOINed field when I use a "TO MULTIPLE" when I use the "TO UNIQUE" this all values get returned. I have used TABLE FILE blah, PRINT *, END, -EXIT and I can follow the data through my program and it is there until it hits the LEFT_OUTER JOIN TO MULTIPLE segment. I have tried to use SET ALL = PASS and it still doesn't work.I have tried to use a conditional join to force it to display the duplicated record and that didnt work either. I will try to recreate this behavior in the CAR file to see if I can get it to work like my current code...but until then I am open for suggestions??

Thanks,
Erin

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

Not knowing your code or your data...have you tried just a Join

JOIN BLAH IN BLAHBLAH TO ALL BLAH IN BLAHBLAH2 AS J1

Prarie,
I have tried just a plain old Join. I get One Record back. The only difference between the record being returned and the record that isnt is a different date. SO I have blah# with date 20020602 and (same) blah# with date 20020612. I have a conditional join that says where blah# eq blah# and another where date eq date. TO UNIQUE returns both records TO MULTIPLE returns one record. I did notice that some of the values in my data for the record that isnt returned with the TO MULTIPLE have blanks or zero values...How could I force a 'null' or 'blank' to display?

thanks,
Erin

I have created the same behavior with the car file. The last 2 columns (far right) COUNTRY and SALES have "." for values and this is the same behavior I am having with my report. How can I force these to repeat the values for COUNTRY and SALES for the output or force it to display. If I use a JOIN TO UNIQUE instead of MULTIPLE the values appear...??

 
-* File JoinIssue.fex
SET BYDISPLAY = ON
TABLE FILE CAR
PRINT 
     'CAR.ORIGIN.COUNTRY'
     'CAR.COMP.CAR'
     'CAR.CARREC.MODEL'
     'CAR.BODY.BODYTYPE'
     'CAR.BODY.SEATS'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
     'CAR.BODY.SALES'
     'CAR.SPECS.LENGTH'
     'CAR.SPECS.WIDTH'
     'CAR.SPECS.HEIGHT'
     'CAR.SPECS.WEIGHT'
     'CAR.SPECS.WHEELBASE'
     'CAR.SPECS.FUEL_CAP'
     'CAR.SPECS.BHP'
     'CAR.SPECS.RPM'
     'CAR.SPECS.MPG'
     'CAR.SPECS.ACCEL'
BY 'CAR.ORIGIN.COUNTRY' NOPRINT
BY 'CAR.COMP.CAR' NOPRINT
ON TABLE NOTOTAL
ON TABLE HOLD AS PASS1
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='TIMES NEW ROMAN',
     SIZE=10,
$
ENDSTYLE
END

TABLE FILE PASS1
SUM 
	'PASS1.PASS1.SALES'
BY 'PASS1.PASS1.COUNTRY'
ON TABLE HOLD AS PASS2
END 

JOIN
LEFT_OUTER PASS1.PASS1.COUNTRY IN PASS1 TO MULTIPLE PASS2.PASS2.COUNTRY IN PASS2 TAG J0 AS J0
END

TABLE FILE PASS1
PRINT *
END
-EXIT

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

Did you try using MATCH?

I have not tried using MATCH...I will get out the Big Book and look it up...Thanks Prarie for that suggestion I didn't think to try using MATCH.

Thanks,
Erin

Since the JOIN in your CAR example is a many-to-one JOIN, I'm not surprised that use of MULTIPLE results in unexpected results. Have you tried your JOIN without MULTIPLE and without UNIQUE? This approach does provide the correct results in your CAR example.

JOIN
LEFT_OUTER PASS1.PASS1.COUNTRY IN PASS1 TO PASS2.PASS2.COUNTRY IN PASS2 TAG J0 AS J0
END

This is a nice summary of the types of matches

http://documentation.informati...wf713crlang/mrg3.htm

Prarie,
I tried to use the MATCH method for the output of my report and received the same type of data from the CAR file. If you run this code the values I need to be repeated for output are all the way to the right. The Sum SALES BY COUNTRY in PASS3 values.

-* File MatchEP.fex
SET BYDISPLAY = ON
TABLE FILE CAR
PRINT
     'CAR.ORIGIN.COUNTRY'
     'CAR.COMP.CAR'
     'CAR.CARREC.MODEL'
     'CAR.BODY.BODYTYPE'
     'CAR.BODY.SEATS'
     'CAR.BODY.DEALER_COST'
     'CAR.BODY.RETAIL_COST'
     'CAR.BODY.SALES'
     'CAR.SPECS.LENGTH'
     'CAR.SPECS.WIDTH'
     'CAR.SPECS.HEIGHT'
     'CAR.SPECS.WEIGHT'
     'CAR.SPECS.WHEELBASE'
     'CAR.SPECS.FUEL_CAP'
     'CAR.SPECS.BHP'
     'CAR.SPECS.RPM'
     'CAR.SPECS.MPG'
     'CAR.SPECS.ACCEL'
BY 'CAR.ORIGIN.COUNTRY' NOPRINT
BY 'CAR.COMP.CAR' NOPRINT
ON TABLE NOTOTAL
ON TABLE HOLD AS PASS1 
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='TIMES NEW ROMAN',
     SIZE=10,
$
ENDSTYLE
END

TABLE FILE PASS1
SUM
 'PASS1.PASS1.SALES'
BY 'PASS1.PASS1.COUNTRY'
ON TABLE HOLD AS PASS2 
END

MATCH FILE PASS1
  PRINT 
	PASS1.PASS1.COUNTRY 
	PASS1.PASS1.CAR 
	PASS1.PASS1.MODEL 
	PASS1.PASS1.BODYTYPE 
	PASS1.PASS1.SEATS 
	PASS1.PASS1.DEALER_COST 
	PASS1.PASS1.RETAIL_COST 
	PASS1.PASS1.SALES 
	PASS1.PASS1.LENGTH 
	PASS1.PASS1.WIDTH 
	PASS1.PASS1.HEIGHT 
	PASS1.PASS1.WEIGHT 
	PASS1.PASS1.WHEELBASE
  BY PASS1.PASS1.COUNTRY
  BY PASS1.PASS1.CAR
RUN
FILE PASS2
  BY PASS2.PASS2.COUNTRY
  BY PASS2.PASS2.SALES
AFTER MATCH HOLD AS COMP1 OLD-OR-NEW
END

TABLE FILE COMP1
PRINT *
END
-EXIT

Dan,
I tried your code with the CAR File output and that is what I think I need. I will have to test that with my current fex to see if I get the desirable results...I will let you know first thing in the morning how it goes.

Thanks,
Erin

In some cases, using the CAR file to test complex JOINs might not work, as RDBMS joins work differently than FOCUS DB joins.

In your reading, you may have found a posting or two that describes a WebFOCUS left outer join does not behave as you'd expect it to and that this is supposed to be fixed in v7.7.

http://techsupport.information...om/sps/31052001.html

This hyperlink ended up solving our issue(s). After viewing the source code of the output we noticed we had a duplicate record in the FROM field for the JOIN. Therefore that record was being dropped. We ended up using the "ON TABLE HOLD AS HoldFileName FORMAT FOCUS INDEX '1stBYfield'" to create an indexed hold file to use to make the JOIN. Although I struggled with the segmented names and the fact that if the initial hold file has AS 'names' the AS Name gets used as the field reference after the FORMAT FOCUS INDEX file gets created. Once we realized the AS name was blowing up my define files and my output it worked like a charm.

I appreciate all of the help and suggestions...

Francis, one can only hope that this IS fixed in v7.7...and the LEFT_OUTER behavior is some what frustrating but I think I understand it a little bit better now.

Only one caveat to the above link it is a resolution for OS on an AS400...