Home » SQL & PL/SQL » SQL & PL/SQL » Simple FULL OUTER JOIN but yet to be doing the JOIN desired
Simple FULL OUTER JOIN but yet to be doing the JOIN desired [message #217113] Wed, 31 January 2007 13:17 Go to next message
dpong
Messages: 73
Registered: January 2007
Member
I'm going to post what i could post to illustrate the issue.

CREATE TABLE DM_METRICS.DP_vars2 as
		   SELECT DISTINCT
		   		  coalesce(a.org_id, b.org_id, c.org_id) org_id,
		   		  coalesce(c.T3_AS, a.T3_AS) T3_AS,
				  coalesce(c.T3_DB, a.T3_DB) T3_DB,
				  coalesce(c.T3_COLLABORATION, a.T3_COLLABORATION) T3_COLLABORATION,
				  coalesce(c.T3_CRM, a.T3_CRM) T3_CRM,
				  coalesce(c.T3_ERP, a.T3_ERP) T3_ERP,
				  coalesce(c.T3_EBS, a.T3_EBS) T3_EBS,
				  coalesce(c.T3_OTHER_APPLICATION, a.T3_OTHER_APPLICATION) T3_OTHER_APPLICATION,
				  coalesce(c.T3_UNSPECIFED_OTHER, a.T3_UNSPECIFED_OTHER) T3_UNSPECIFED_OTHER,
				  coalesce(c.IBM_DB, a.IBM_DB) IBM_DB,
				  coalesce(c.INFORMIX_DB, a.INFORMIX_DB) INFORMIX_DB,
				  coalesce(c.LOTUS_EMAIL, a.LOTUS_EMAIL) LOTUS_EMAIL,
				  coalesce(c.MICROSOFT_DB, a.MICROSOFT_DB) MICROSOFT_DB,
				  coalesce(c.MICROSOFT_EMAIL, a.MICROSOFT_EMAIL) MICROSOFT_EMAIL,
				  coalesce(c.SYBASE_DB, a.SYBASE_DB) SYBASE_DB,
				  coalesce(b.COMPANY_SPONSOR_OU, a.COMPANY_SPONSOR_OU) COMPANY_SPONSOR_OU,
				  DUNS_NUMBER, mkt_unit, STAND_ALONE_BUSINESS_FLG, MU_ATS_INDUSTRY, DOMESTIC_ULTIMATE_FLG, 
				  GCD_CUSTOMER_FLAG_GLOBAL_ULT, GCD_CUSTOMER_FLAG_MU, GLOBAL_ULTIMATE_FLG, GCD_CUSTOMER_FLAG_SITE,
				  GSRT_CUSTOMER_FLAG_GLOBAL_ULT, GSRT_CUSTOMER_FLAG_MU, GSRT_CUSTOMER_FLAG_SITE, MKT_UNIT_FLG, 
				  EMPLOYEES_TOTAL, ANNUAL_SALES_USD, SIC_CODE, MU_ANNUAL_SALES_USD, MU_EMPLOYEES_HERE, 
				  MU_NO_OF_YEARS, MU_STAND_ALONE_BUSINESS_FLG, MU_CREDIT_RATING, MU_URLS_REG, MU_PUBLIC_PRIVATE_INDICATOR, 
   				  FLG
				  				   FROM DM_METRICS.DP_vars a 
		   		  	  	 	  	  	 FULL OUTER JOIN DM_METRICS.DP_ou_org2 b
									 ON (a.org_id = b.org_id)
									 FULL OUTER JOIN DM_METRICS.DP_prod_org c
									 ON (a.org_id = c.org_id);


What I'm trying to achieve here essentially is to get the following records straightened out.


SELECT ORG_ID,T3_AS,IBM_DB,COMPANY_SPONSOR_OU FROM DP_vars2
WHERE org_id = 1613877
result >>
ORG_ID	T3_AS	IBM_DB	COMPANY_SPONSOR_OU ...

1613877	1	0	                   ...
1613877			0                  ...



Attached are the desc of the tables a, b, and c.



  • Attachment: DP_vars.txt
    (Size: 1.29KB, Downloaded 107 times)

[Updated on: Wed, 31 January 2007 13:21]

Report message to a moderator

Re: Simple FULL OUTER JOIN but yet to be doing the JOIN desired [message #217114 is a reply to message #217113] Wed, 31 January 2007 13:18 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
It doesn't let me do more than one file per message. SO, sorry, I'll have to spam a little bit to facilitate the readers by uploading the remaining DESC files for the tables mentioned above
Re: Simple FULL OUTER JOIN but yet to be doing the JOIN desired [message #217115 is a reply to message #217113] Wed, 31 January 2007 13:20 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
Let me know if anyone has any questions as to what I desired to get.

In a nutshell, I intended to get the following one record per org_id (1613877 in this case)

ORG_ID T3_AS IBM_DB COMPANY_SPONSOR_OU

1613877 1 0 0
Re: Simple FULL OUTER JOIN but yet to be doing the JOIN desired [message #217188 is a reply to message #217114] Thu, 01 February 2007 01:38 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
dpong wrote on Wed, 31 January 2007 20:18
It doesn't let me do more than one file per message. SO, sorry, I'll have to spam a little bit to facilitate the readers by uploading the remaining DESC files for the tables mentioned above

There is absolutely no need to add this as an attachment. Rewrite your <desc table> to create table statements and include those in your post; not as attachment but inline
Previous Topic: dbms_job related problem
Next Topic: how stored Functions Return More than one values?
Goto Forum:
  


Current Time: Sat Dec 10 22:21:36 CST 2016

Total time taken to generate the page: 0.10503 seconds