Help with outer join

[mwolf00]

Hi, I'm having a little problem with and outer join (and I can't get TGML to work either). It's the one that I tried to highlight below.

SELECT trim(foc.code_name), trim(bc.code_name), TRIM(pt.code_name)
			, trim(pu.code_name)
			, SUBSTR(a.st_fcly_cde,3,2) AS st_fcly_cde
			, a.desgn_grss_sqft,	a.bldg_prk_spc
			, trim(ada.code_name), trim(oc.code_name), trim(hd.code_name)
			, trim(hnr.code_name)
			, trim(b.hstrc_ntnl_rgstr_cde),trim(a.cngrssnl_dstrct)
			, trim(a.cnty_nme),trim(a.cnty_cty_cde),
			SUM(CASE WHEN c.gsa_rspnsblty_cde = '1' THEN 1 ELSE 0 END) * -1 AS "RealEstate",
			SUM(CASE WHEN c.gsa_rspnsblty_cde = '6' THEN 1 ELSE 0 END) * -1 AS "Disposal",
			SUM(CASE WHEN c.gsa_rspnsblty_cde = '2' THEN 1 ELSE 0 END) * -1 AS "RealPropertyMgmt",
			SUM(CASE WHEN c.gsa_rspnsblty_cde = '3' THEN 1 ELSE 0 END) * -1 AS "RnA",
			SUM(CASE WHEN c.gsa_rspnsblty_cde = '4' THEN 1 ELSE 0 END) * -1 AS "FPS",
			SUM(CASE WHEN c.gsa_rspnsblty_cde = '5' THEN 1 ELSE 0 END) * -1 AS "PropertyDevelopment",
			SUM(CASE WHEN c.gsa_rspnsblty_cde = '9' THEN 1 ELSE 0 END) * -1 AS "DelegatedBldg",
			SUM(CASE WHEN c.gsa_rspnsblty_cde = '8' THEN 1 ELSE 0 END) * -1 AS "Other"
			FROM bis.bldg@bis a,bis.bldg_misc@bis b,bis.bldg_srvc_rspnsblty@bis c
				 ,RBM_TCODE foc, RBM_TCODE bc, RBM_TCODE pt, RBM_TCODE pu, RBM_TCODE ada
				 ,RBM_TCODE oc, RBM_TCODE hd, RBM_TCODE hnr
			WHERE  a.bldg_id_st_cde   =  b.bldg_id_st_cde  AND  a.bldg_id_nbr  =   b.bldg_id_nbr 
			AND  a.bldg_id_st_cde   =  c.bldg_id_st_cde  AND  a.bldg_id_nbr  =   c.bldg_id_nbr
			AND (trim(a.dstrct_fld_offc_cde) = foc.code_value AND foc.code_type LIKE 'FLDOFF%')
			AND (trim(a.clss_cde) = bc.code_value AND bc.code_type = 'BLDGCLASS')
			AND (trim(a.prprty_typ_cde) = pt.code_value AND pt.code_type = 'PROPTYPE')
			AND (trim(a.prdmnt_use_cde) = pu.code_value AND pu.code_type = 'PREDOMUSE')
			AND (trim(b.ada_accsblty_ind) = ada.code_value AND ada.code_type = 'TXTADA')
			AND (trim(occpncy_rght_cde) = oc.code_value AND oc.code_type = 'OCCRT')
			AND (trim(b.hstrc_data_cde) = hd.code_value AND hd.code_type = 'HISTDATA')

			[highlight]
            AND (trim(b.hstrc_ntnl_rgstr_cde) = hnr.code_value AND hnr.code_type = 'NATREG')
            [/highlight]

			AND  trim(a.Bldg_id_st_cde) || trim(a.bldg_id_nbr)  = 'some value'
			GROUP BY foc.code_name, bc.code_name, pt.code_name, pu.code_name
			,a.st_fcly_cde,a.desgn_grss_sqft,a.bldg_prk_spc,a.cngrssnl_dstrct,a.cnty_nme
			,a.cnty_cty_cde,ada.code_name,oc.code_name, hd.code_name, hnr.code_name
			,b.hstrc_ntnl_rgstr_cde

I there may or may not be a value in b.hstrc_ntnl_rgstr_cde but I need the row to be returned anyways.

Thank you for looking

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[LKBrwnDBA]

Try outer join:

SELECT ...etc...
            WHERE  a.bldg_id_st_cde   =  b.bldg_id_st_cde(+)
              AND  a.bldg_id_nbr  =   b.bldg_id_nbr(+) 
              AND  a.bldg_id_st_cde   =  c.bldg_id_st_cde
              AND  a.bldg_id_nbr  =   c.bldg_id_nbr
              AND (trim(a.dstrct_fld_offc_cde) = foc.code_value
              AND foc.code_type LIKE 'FLDOFF%')
              AND (trim(a.clss_cde) = bc.code_value 
                AND bc.code_type = 'BLDGCLASS')
              AND (trim(a.prprty_typ_cde) = pt.code_value
                AND pt.code_type = 'PROPTYPE')
              AND (trim(a.prdmnt_use_cde) = pu.code_value
                AND pu.code_type = 'PREDOMUSE')
              AND (trim(b.ada_accsblty_ind(+)) = ada.code_value
                AND ada.code_type = 'TXTADA')
              AND (trim(occpncy_rght_cde) = oc.code_value
                AND oc.code_type = 'OCCRT')
              AND (trim(b.hstrc_data_cde(+)) = hd.code_value
                AND hd.code_type = 'HISTDATA')
              AND (trim(b.hstrc_ntnl_rgstr_cde(+)) = hnr.code_value
                AND hnr.code_type = 'NATREG')

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[mwolf00]

Thank you but that throws the error that a table may be outer joined to at most one other table

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[LKBrwnDBA]

Ooops, yes, you may have to change the where clause so that only one of the tables 'OUTER JOINS' the "bis.bldg_misc@bis b" table.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb