Conference ilbbak::ibi_focus

430.0. "3-table JOIN for reporting" by MILPND::DANDREA (no tan lines) Mon Jul 08 1991 19:46

    
	Would someone give me a hand converting the following SQL code 
	into FOCUS code...?  I want to JOIN these three RDB tables 
	(from the same database) and access the JOINed table via FOCUS.

	The .MAS and .ACX files already exist for the three source
    	tables, and they can be provided if necessary.

	The documentation is confusing and I want to make sure I have
	the proper selection of data in my JOIN.  Any help is greatly
	appreciated.
								-Nancy-

CREATE VIEW FOCUS_REPORT_VIEW
(COC_ORGNZN_UNIT_CD, COC_ORGNZN_TYPE_CD, SUBMSN_MO, SUBMSN_YR, FYR, 
REDBK_LNITM_CD, BDGT_FRCST_TYPE_CD, REDBK_LNITM_DSCRPN, REDBK_LNITM_TYPE,
LVL_0_COC_ORGNZN_UNIT_CD,  LVL_0_COC_ORGNZN_TYPE_CD)

AS SELECT BD.COC_ORGNZN_UNIT_CD, BD.COC_ORGNZN_TYPE_CD, BD.SUBMSN_MO, 
	  BD.SUBMSN_YR, BD.FYR,  BD.REDBK_LNITM_CD,     BD.BDGT_FRCST_TYPE_CD, 
	  LN.REDBK_LNITM_DSCRPN, 	LN.REDBK_LNITM_TYPE,
	  FL.LVL_0_COC_ORGNZN_UNIT_CD,	FL.LVL_0_COC_ORGNZN_TYPE_CD

FROM REDBK_BDGT_FRCST BD, REDBK_LNITM LN, FLAT_ORGNZN_TREE FL

WHERE

FL.COC_ORGNZN_UNIT_CD  =  BD.COC_ORGNZN_UNIT_CD AND
BD.COC_ORGNZN_TYPE_CD  =  "C" AND
BD.REDBK_LNITM_CD      =  LN.REDBK_LNITM_CD AND
BD.BDGT_FRCST_TYPE_CD  =  "F"

GROUP BY  BD.COC_ORGNZN_UNIT_CD, BD.COC_ORGNZN_TYPE_CD, BD.SUBMSN_MO, 
	  BD.SUBMSN_YR, BD.FYR,  BD.REDBK_LNITM_CD,     BD.BDGT_FRCST_TYPE_CD, 
	  LN.REDBK_LNITM_DSCRPN, 	LN.REDBK_LNITM_TYPE,
	  FL.LVL_0_COC_ORGNZN_UNIT_CD,	FL.LVL_0_COC_ORGNZN_TYPE_CD;
    
    				< the end >

    See page 10-5 in the Summary of New Features Rel 6.1, for documentation
    on the new Focus SQL Translator.  It translates SQL Select and Create View
    statements into a Focus Table request.  Let us know how it works.
    
    	Richard	

    
    
    	see note 433, which shows how I tried to join three tables