I have a source file which contains nation-wide merchandise return data(M); the nation has 10 regions under it, each region includes many states. The cross reference Return_Reason table(R) is a two columns table with return_ reason_code, and return_reason_text, the table has 20 return reasons. I have to create a report which will display the return reason code, return reason text and the summarized number of return merchandise within each state. The report should sorted by Region, than State. The report of each state should display all 20 return reason codes.

For example, State NY only have rows with return-code 1, 2, 5, and 10. But when I display for State NY, I need to display all 20 rows for each return code, only code 1,2,5 and 10 have none zero number, the rest should be displayed with 0 count.

When I use LEFT_OUTER join to JOIN Return_reason_code in table R to Return_reason_code in table M, the left-outer join work for whole nation’s data; but not at the state level, it did not show all the 20 reason code rows. How do I force the left-outer join apply to the lowest State level?
WF7.6.11 mainframe, DB2

Emily,

Could you also post your code here so we can actualy see what you are trying to do?

Thanks,

Ok, there is a technique that I have used for 15 years to accomplish this, and it is not documented in any FOCUS manual.
Do not do the join first.
TABLE FILE .....
SUM RETURNS
BY REGION
BY STATE
BY REASON_CODE ROWS 1 OVER 2 OVER 3 OVER 4
OVER 5 OVER 6 OVER 7 OVER 8 OVER 9 OVER 10
ON TABLE HOLD AS PULL1
END
-*
-*AT THIS POINT IF YOU LOOK AT YOUR HOLDMAST
-*YOU WILL FIND IT LOOKS STRANGE. REGION HAS
-*AN ALIAS AS E01, STATE HAS AN ALIAS OF E02,
-*AND REASON CODE HAS NO FIELDNAME, BUT IT HAS
-*AN ALIAS OF E03.
JOIN E03 IN PULL1 TO RETURN_CODE IN ???FILE (YOUR CROSS REFERENCE FILE) AS J1
TABLE FILE PULL1
SUM REASON_TEXT RETURNS
BY REGION
BY STATE
BY E03 AS REASON_CODE
END
-RUN
This is just a nice little twist on MacGyver.
Keep in mind that you cannot join to your cross reference file until AFTER you force all 10 reason codes into your hold file.

JJI,
my code is very simple as :
SQL DB2
SELECT
R.CD
,R.CDESC
,M.STORE_CD
,M.ST
,M.RGN_NUM
,M.CD
,M.CASE_NUM
FROM RETURN_REASON AS R
LEFT OUTER JOIN MERCHAN AS M
ON R.CD = M.CD
WHERE M.RPT_DT BETWEEN date1 and date2
FOR FETCH ONLY WITH UR;
TABLE
ON TABLE HOLD AS OUT1 FORMAT FOCUS INDEX STORE_CD
END
-RUN


TABLE FILE OUT1
PRINT
CDESC
CASE_NUM
BY RGN_NUM
BY ST
BY STORE_CD
BY CD
ON TABLE SET NODATA '0'
ON TABLE HOLD AS OUT2
END
-RUN

TABLE FILE OUT2
SUM
'CNT.CASE_NUM' AS 'CASECNT'
'PCT.CNT.CASE_NUM/D6.2%' AS 'PERCENT'
BY RGN_NUM
BY ST
BY CD
BY CDESC
ON ST PAGE-BREAK
END
-RUN

I will need the output of each ST( State) has 20 rows as

01 Reason-1 30 20%
02 Reason-2 0 0%
03 Reason-3 0 0%
04 Reason-4 15 10%
05 ---
06 ---
--
--
19 Reason-19 0 0%
20 Reason-20 22 14%

I hope there is a simple way to force Left-Outer JOIN to the ST level. thanks.

Bethanne,
Thanks for yout tip, meanwhile, I will try your tip to work around. I will try and let you know the result. thanks.

Emilee,

This was my first post in response to your question. For some reason it never got displayed on this forum. So here is the next attempt

  
-* File Forced_Join-to_all_records.fex
DEFINE FILE CAR
DFN_1BLANC/A1 WITH COUNTRY = '';
END 
TABLE FILE CAR
PRINT 
    CAR
BY COUNTRY
BY DFN_1BLANC
ON TABLE HOLD AS HOLD1 FORMAT FOCUS INDEX DFN_1BLANC
END

JOIN CLEAR *
JOIN 
    LEFT_OUTER DFN_2BLANC WITH EMP_ID IN EMPLOYEE TO MULTIPLE DFN_1BLANC  IN HOLD1
END

DEFINE FILE EMPLOYEE
DFN_2BLANC/A1 WITH EMP_ID = '' ;
END

TABLE FILE EMPLOYEE
PRINT 
-*    COUNTRY
    CAR
BY DEPARTMENT
BY EMP_ID             NOPRINT
BY LAST_NAME
BY FIRST_NAME  

ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD FORMAT EXL2K
END

I hope this gives you what you want in a very simple way.
Most of the time I use this technique if I need to work with dates, or calendars.
Hope this helps.

JJI and Bethanne,

I took ideas from each of you and come up with a solution myself. Thanks for all your help.
The logic is : I use a loop, which will loop through all the State name one at a time. (I have to use loop because there are other requirements I have to meet.)
At each state level, I will sub-select the data rows which belong to the selected State, save it as hold file(ST_HOLD), then use reference table (R) LEFT_OUTER JOIN to ST_HOLD again before present the report. That is how I force the left-outer join to the lowest level.
Here is part of my code:

SQL DB2
SELECT
R.CD ,R.CDESC
,M.STORE_CD ,M.ST ,M.RGN_NUM ,M.CD ,M.CASE_NUM
FROM RETURN_REASON AS R
LEFT OUTER JOIN MERCHAN AS M
ON R.CD = M.CD
WHERE M.RPT_DT BETWEEN date1 and date2
FOR FETCH ONLY WITH UR;
TABLE
ON TABLE HOLD AS OUT1 FORMAT FOCUS INDEX STORE_CD
END
-RUN


TABLE FILE OUT1
PRINT CDESC CASE_NUM
BY RGN_NUM
BY ST
BY STORE_CD
BY CD
ON TABLE SET NODATA '0'
ON TABLE HOLD AS OUT2
END
-RUN

-*BEGIN of LOOP, each time select a ST name from ST-list , set it as &ST
-LOOP_TOP
TABLE FILE OUT2
SUM
'CNT.CASE_NUM' AS 'CASECNT'
'PCT.CNT.CASE_NUM/D6.2%' AS 'PERCENT'
BY CD
BY CDESC
WHERE ST EQ '&ST’
ON TABLE HOLD AS ST_HOLD FORMAT FOCUS INDEX CD
END
-RUN

-*CODE_HOLD is the cross reference table,RETURN_REASON hold file index with CD
-*
JOIN LEFT_OUTER CD IN CODE_HOLD TO MULTIPLE CD IN ST_HOLD AS J3
END


TABLE FILE CODE_HOLD
SUM
CASECNT/I11C AS ‘Returns count'
PERCENT/D6.1% AS 'Percent of, Return'
BY CD AS ‘Return Reason Code'
BY CDESC AS 'Return Reason'
ON TABLE NOTOTAL
ON TABLE SET NODATA '0.0%'
ON TABLE SET STYLE *
TYPE=REPORT,
..
..
-* pick another &ST GOTO LOOP_TOP create a page for next state
-GOTO LOOP_TOP