Need help joining a table, getting errors

I want to join BA_VIEW_COMPANIES_ PARENT to the following statement by CMP_PARENT. How can I properly accomplish without adversely affecting the query? The CMP_PARENT within the statement below resides in a table named  COMPANIES which is a joined table. Any help will be greatly appreciated.

select GL_REV_COGS."GLT_AUTO_KEY",GL_REV_COGS."TRAN_DATE",GL_REV_COGS."ENTRY_DATE",GL_REV_COGS."ACCT_COMPANY",GL_REV_COGS."DOCUMENT_NUMBER",GL_REV_COGS."GL_TYPE",GL_REV_COGS."TRAN_TYPE",GL_REV_COGS."AMOUNT",GL_REV_COGS."REVENUE",GL_REV_COGS."COGS",GL_REV_COGS."ORDER_TYPE",GL_REV_COGS."ORDER_NUMBER",GL_REV_COGS."CUSTOMER_ORDER",GL_REV_COGS."CUSTOMER",GL_REV_COGS."CUSTOMER_CODE",GL_REV_COGS."INTER_COMPANY_CODE",GL_REV_COGS."CMP_PARENT",GL_REV_COGS."PBH",GL_REV_COGS."CMP_AUTO_KEY",GL_REV_COGS."PART_NUMBER",GL_REV_COGS."PART_DESCRIPTION",GL_REV_COGS."WO_NUMBER",GL_REV_COGS."WO_TYPE",GL_REV_COGS."SO_NUMBER",GL_REV_COGS."SO_ROUTE_DESC",GL_REV_COGS."WO_EXCHANGE",GL_REV_COGS."DEPT",GL_REV_COGS."CONTRACT_NUMBER",GL_REV_COGS."CONTRACT_TYPE",GL_REV_COGS."INVOICE",GL_REV_COGS."WO_BILLING_GROUP",GL_REV_COGS."SO_CATEGORY_CODE",GL_REV_COGS."ACCOUNT",GL_REV_COGS."ACCOUNT_NUMBER",GL_REV_COGS."BRIDGE_REFERENCE",GL_REV_COGS."TRANS_DESCRIPTION",GL_REV_COGS."BOM_ACTIVITY",GL_REV_COGS."GL_TRAN_MODEL",GL_REV_COGS."WOO_AUTO_KEY",GL_REV_COGS."SOH_AUTO_KEY",GL_REV_COGS."SOD_AUTO_KEY",GL_REV_COGS."POD_AUTO_KEY",GL_REV_COGS."POST_STATUS",GL_REV_COGS."BATCH_NUMBER",GL_REV_COGS."JOURNAL_CODE",GL_REV_COGS."SYSCM_AUTO_KEY",
case when SO_CATEGORY_CODE='GSTE' then
	'GSTE MANUFACTURED'
else
	case when SO_CATEGORY_CODE='GSTE-DIST' then
	'GSTE DISTRIBUTION'
	else
		case when SO_CATEGORY_CODE='DIST' or ((SO_CATEGORY_CODE='' or SO_CATEGORY_CODE='_' or SO_CATEGORY_CODE is null) and DEPT='SALES DISTRIBUTION') then
		'DISTRIBUTION'
		else
			case when DEPT='GSE SALES' then
			'GSTE DISTRIBUTION'
			else
				case  when PBH='Y' then
					case when (ORDER_TYPE='WO' and DEPT like 'SH 10 GSE%' and WO_TYPE='External') then
						'GSTE REPAIR'
					else
						case when SO_CATEGORY_CODE in ('PBH NNWT','PBH NON-RE','PBH O'||chr(38)||'A','PBH BER RE')
                                                then 'PBH O'||chr(38)||'A'
                                                else case when contract_type not like 'PBH%' then 'REPAIR' else 'PBH' end
                                                end
					end
				else
					case when (customer like '%TAM LINEAS AEREAS%' and syscm_auto_key=2) then
					'PASS THROUGH'
					else
						case when DEPT like '%TRADING%' or so_category_code like '%TRADING%' then
						'TRADING'
						else
							case when (ORDER_TYPE='WO' and DEPT like 'SH 10 GSE%' and WO_TYPE='External') then
							'GSTE REPAIR'
							else
								case when order_type='WO' then
								'REPAIR'
								else
									case when ACCOUNT = 'BIC-COGS PART-GSTE MANUFACTURED' then
									'GSTE MANUFACTURED'
									else
									         case when so_route_desc in ('Exchange','Repair')
                                                                                 then 'REPAIR'
                                                                                 else 'OTHER'
                                                                                 end
									end
								end
							end
						end
					end
				end
			end
		end
	end
end ACTIVITY,
case when inter_company_code is null then
	'EXTERNAL'
else
	case when inter_company_code in ('AFPRFXX','AFTOTNA','AFAFIUS','AKHFINX','AFTOTCB','AFBRITA','KLTOTMB') then
		'IC AFKL'
	else
		case when inter_company_code in ('AFBARFI') then
			'IC BIC'
		else
			case when inter_company_code in ('AFAMG02') then
				'IC BUSA'
			else
				'I/C'
			end
		end
	end
end INTER_COMPANY
from
(
select GL_TRANS.GLT_AUTO_KEY
,GL_TRANS.TRAN_DATE
,GL_TRANS.ENTRY_DATE
,syscm.company_name ACCT_COMPANY
,gl_trans.document_number
,case when GL_ACCOUNT.DESCRIPTION like '%REVENUE%' then
	'REV'
else
	'COGS'
end GL_TYPE
,gl_trans.TRAN_TYPE
,gl_trans.AMOUNT
,case when gl_trans.tran_type='C' and GL_ACCOUNT.ACCOUNT_NUMBER like '4%'
then
	GL_TRANS.AMOUNT
else
	case when gl_trans.tran_type='D' and GL_ACCOUNT.ACCOUNT_NUMBER like '4%' then
		-1*GL_TRANS.AMOUNT
	else
		0
	end
end REVENUE
,case when gl_trans.tran_type='C' and GL_ACCOUNT.DESCRIPTION like '%COGS%' then
	GL_TRANS.AMOUNT
else
	case when gl_trans.tran_type='D' and GL_ACCOUNT.DESCRIPTION like '%COGS%' then
		-1*GL_TRANS.AMOUNT
	else
	0
	end
end COGS
,decode(gl_trans.soh_auto_key,'',decode(gl_trans.woo_auto_key,'',decode(gl_trans.pod_auto_key,'','OTHER','PO'),'WO'),'SO') ORDER_TYPE
,decode(so_header.soh_auto_key,'',decode(gl_trans.woo_auto_key,'',decode(gl_trans.pod_auto_key,'','',poh.po_number),wo_operation.si_number),so_header.so_number) ORDER_NUMBER
,decode(gl_trans.woo_auto_key,'',so_header.COMPANY_REF_NUMBER,wo_operation.COMPANY_REF_NUMBER) CUSTOMER_ORDER
,cmp.company_name CUSTOMER
,cmp.company_code CUSTOMER_CODE
,cmp.cv_udf_013 INTER_COMPANY_CODE
,cmp.cmp_parent
,decode(cmp.cv_udf_002,'T','Y','N') PBH
,gl_trans.cmp_auto_key
,decode(pnm_wo.pn,'',pnm_so.pn,pnm_wo.pn) PART_NUMBER
,decode(pnm_wo.pn,'',pnm_so.description,pnm_wo.description) PART_DESCRIPTION
,wo_operation.si_number WO_NUMBER
,wo_operation.wo_type
,so_header.so_number SO_NUMBER
,so_detail.route_desc so_route_desc
,wooe.si_number wo_exchange
,decode(wo_department.dpt_auto_key,''
,decode(so_department.dpt_auto_key,'',department.dept_name,so_department.dept_name),wo_department.dept_name) DEPT
,contract_header.contract_number CONTRACT_NUMBER
,contract_header.SDF_CTH_001 CONTRACT_TYPE
,invc_header.invc_number INVOICE
,billing_group_master.description WO_BILLING_GROUP
,case when (sccgl.SO_CATEGORY_CODE is not null and sccgl.SO_CATEGORY_CODE<>'_')	then
	sccgl.SO_CATEGORY_CODE
else
	case when (sccsod.SO_CATEGORY_CODE is not null and sccsod.SO_CATEGORY_CODE<>'_') then
		sccsod.SO_CATEGORY_CODE
	else
		case when (sccsoh.SO_CATEGORY_CODE is not null and sccsoh.SO_CATEGORY_CODE<>'_') then
			sccsoh.SO_CATEGORY_CODE
		else
		''
		end
	end
end SO_CATEGORY_CODE
,GL_ACCOUNT.DESCRIPTION ACCOUNT
,GL_ACCOUNT.account_number ACCOUNT_NUMBER
,GL_ACCOUNT.bridge_reference
,GL_TRANS.DESCRIPTION TRANS_DESCRIPTION
,wo_bom.ACTIVITY BOM_ACTIVITY
,GL_TRANS.GL_TRAN_MODEL
,gl_trans.woo_auto_key
,gl_trans.soh_auto_key
,gl_trans.sod_auto_key
,gl_trans.pod_auto_key
,GL_BATCH.POST_DESC POST_STATUS
,GL_BATCH.batch_number
,gl_journal.code JOURNAL_CODE
,gl_account.syscm_auto_key
from GL_TRANS
inner join GL_BATCH
on GL_TRANS.GLB_AUTO_KEY=GL_BATCH.GLB_AUTO_KEY
left join SO_CATEGORY_CODES sccgl
on GL_TRANS.SCC_AUTO_KEY=sccgl.SCC_AUTO_KEY
inner join GL_ACCOUNT
on GL_TRANS.GLA_AUTO_KEY=GL_ACCOUNT.GLA_AUTO_KEY
left join wo_operation
on gl_trans.woo_auto_key=wo_operation.woo_auto_key
left join exchange exc
on gl_trans.sod_auto_key=exc.sod_auto_key
left join wo_operation wooe
on exc.woo_auto_key=wooe.woo_auto_key
left join so_header
on gl_trans.soh_auto_key=so_header.soh_auto_key
left join parts_master
on gl_trans.pnm_auto_key=parts_master.pnm_auto_key
left join contract_header
on gl_trans.cth_auto_key=contract_header.cth_auto_key
left join department
on gl_trans.dpt_auto_key=department.dpt_auto_key
left join department wo_department
on wo_operation.dpt_auto_key=wo_department.dpt_auto_key
left join department so_department
on so_header.dpt_auto_key=so_department.dpt_auto_key
left join so_detail
on gl_trans.sod_auto_key=so_detail.sod_auto_key
left join SO_CATEGORY_CODES sccsod
on so_detail.SCC_AUTO_KEY=sccsod.SCC_AUTO_KEY
left join SO_CATEGORY_CODES sccsoh
on so_header.SCC_AUTO_KEY=sccsoh.SCC_AUTO_KEY
left join ar_detail ard
on gl_trans.ard_auto_key=ard.ard_auto_key
left join ar_account ara
on ard.ara_auto_key=ara.ara_auto_key
left join companies cmp
on nvl(gl_trans.cmp_auto_key,nvl(so_header.cmp_auto_key,nvl(wo_operation.cmp_auto_key,nvl(ara.cmp_auto_key,Null))))=cmp.cmp_auto_key
left join parts_master pnm_WO
on wo_operation.pnm_auto_key=pnm_wo.pnm_auto_key
left join parts_master pnm_SO
on so_detail.pnm_auto_key=pnm_so.pnm_auto_key
left join invc_detail
on gl_trans.ind_auto_key=invc_detail.ind_auto_key
left join invc_header
on invc_detail.inh_auto_key=invc_header.inh_auto_key
left join wo_task
on gl_trans.wot_auto_key=wo_task.wot_auto_key
left join billing_groups
on wo_task.bgs_auto_key=billing_groups.bgs_auto_key
left join billing_group_master
on billing_groups.bgm_auto_key=billing_group_master.bgm_auto_key
left join wo_bom
on gl_trans.wob_auto_key=wo_bom.wob_auto_key
left join gl_journal
on gl_trans.glj_auto_key=gl_journal.glj_auto_key
left join sys_companies syscm
on gl_account.syscm_auto_key=syscm.syscm_auto_key
left join po_detail pod
on gl_trans.pod_auto_key=pod.pod_auto_key
left join po_header poh
on pod.poh_auto_key=poh.poh_auto_key
where (GL_ACCOUNT.ACCOUNT_NUMBER like '4%' or (GL_ACCOUNT.DESCRIPTION like '%COGS%' and GL_ACCOUNT.ACCOUNT_NUMBER like '5%'))
order by gl_trans.tran_date desc
) GL_REV_COGS

Open in new window

maximus1974Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
that isn't Microsoft SQL Server syntax, it uses NVL() which is found in Oracle

[edit] I removed SQL Server as a topic and added Oracle instead. Choosing topics is important as most experts monitor their favourite topics. So, choose them wisely and you get access to the experts in that field.
0
PortletPaulfreelancerCommented:
If I understand your wording correctly you just add it like any other table:
FROM GL_TRANS
INNER JOIN GL_BATCH ON GL_TRANS.GLB_AUTO_KEY = GL_BATCH.GLB_AUTO_KEY
LEFT JOIN SO_CATEGORY_CODES sccgl ON GL_TRANS.SCC_AUTO_KEY = sccgl.SCC_AUTO_KEY
INNER JOIN GL_ACCOUNT ON GL_TRANS.GLA_AUTO_KEY = GL_ACCOUNT.GLA_AUTO_KEY
LEFT JOIN wo_operation ON gl_trans.woo_auto_key = wo_operation.woo_auto_key
LEFT JOIN exchange exc ON gl_trans.sod_auto_key = exc.sod_auto_key
LEFT JOIN wo_operation wooe ON exc.woo_auto_key = wooe.woo_auto_key
LEFT JOIN so_header ON gl_trans.soh_auto_key = so_header.soh_auto_key
LEFT JOIN parts_master ON gl_trans.pnm_auto_key = parts_master.pnm_auto_key
LEFT JOIN contract_header ON gl_trans.cth_auto_key = contract_header.cth_auto_key
LEFT JOIN department ON gl_trans.dpt_auto_key = department.dpt_auto_key
LEFT JOIN department wo_department ON wo_operation.dpt_auto_key = wo_department.dpt_auto_key
LEFT JOIN department so_department ON so_header.dpt_auto_key = so_department.dpt_auto_key
LEFT JOIN so_detail ON gl_trans.sod_auto_key = so_detail.sod_auto_key
LEFT JOIN SO_CATEGORY_CODES sccsod ON so_detail.SCC_AUTO_KEY = sccsod.SCC_AUTO_KEY
LEFT JOIN SO_CATEGORY_CODES sccsoh ON so_header.SCC_AUTO_KEY = sccsoh.SCC_AUTO_KEY
LEFT JOIN ar_detail ard ON gl_trans.ard_auto_key = ard.ard_auto_key
LEFT JOIN ar_account ara ON ard.ara_auto_key = ara.ara_auto_key

LEFT JOIN companies cmp ON COALESCE(gl_trans.cmp_auto_key, so_header.cmp_auto_key, wo_operation.cmp_auto_key, ara.cmp_auto_key) = cmp.cmp_auto_key

LEFT JOIN BA_VIEW_COMPANIES_PARENT cparent ON cmp.CMP_PARENT = cparent.CMP_PARENT

LEFT JOIN parts_master pnm_wo ON wo_operation.pnm_auto_key = pnm_wo.pnm_auto_key
LEFT JOIN parts_master pnm_so ON so_detail.pnm_auto_key = pnm_so.pnm_auto_key

Open in new window

But also note, for the companies table I have swapped out the multiple NVL() uses to a single COALESCE()

What we cannot know through this web page is will that join adversely affect your existing query. We simply have no knowledge of your tables or data model.

IF there is only one row in the parent table for each company table row, then chances are it will not adversely affect the query.  However if there are multiple parent references per company row then the join will produce more rows tan you get now.

& If there are multiple parent rows per company row, you have to decide what you want to do. (e.g. which parent would you choose? on what basis?)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
maximus1974Author Commented:
I keep receiving the error attached.
error
0
PortletPaulfreelancerCommented:
I assume you have sorted this problem because I'm afraid I don't know which column is relevant from the companies table
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.