Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

View creating Cartesian Product.. why....

889260Jan 30 2012 — edited Jan 30 2012
i am joining 5 tables in a view.. dont know exactly why it is creating Cartesian product.
Below is the query:

PROMPT CREATE OR REPLACE VIEW xxpd_ozf_aff_err_int_v
CREATE OR REPLACE VIEW xxpd_ozf_aff_err_int_v (
row_id,
source_system,
source_process_id,
source_process_date,
ora_process_id,
soa_instance_id,
customer_record_id,
process_status,
insert_update_flag,
process_stage,
process_start_time,
process_end_time,
error_code,
error_message,
error_type,
customer_process_status,
supplier_process_status,
customer_level,
source_customer_ref,
customer_class_code,
customer_type,
business_model_code,
source_parent_cust_ref,
source_parent_cust_type,
customer_name,
account_status,
customer_alias,
person_flag,
person_first_name,
person_last_name,
tax_reg_number,
tax_reg_type,
customer_id,
account_number,
party_id,
party_type,
party_number,
supplier_id,
affiliate_type,
check_payable_name,
payable_flag,
approved_flag,
primary_site,
site_status,
purpose,
address1,
address2,
address3,
address4,
city,
county,
state,
province,
postal_code,
country,
cust_acct_site_id,
party_site_id,
location_id,
reportable_1099,
tax_type_1099,
primary_contact,
contact_role,
contact_status,
first_name,
last_name,
salutation,
job_title,
email_address,
phone_line_type,
phone_number,
phone_extension,
phone_area_code,
phone_country_code,
preferred_language,
customer_profile_class,
profile_acct_status,
profile_active_status,
account_status_reason,
invoice_type
) AS
SELECT acct.ROWID row_id,
CTRL.SOURCE_SYSTEM,
CTRL.SOURCE_PROCESS_ID,
CTRL.SOURCE_PROCESS_DATE,
CTRL.ORA_PROCESS_ID,
CTRL.SOA_INSTANCE_ID,
ACCT.CUSTOMER_RECORD_ID,
CTRL.PROCESS_STATUS,
CTRL.INSERT_UPDATE_FLAG,
CTRL.PROCESS_STAGE,
CTRL.PROCESS_START_TIME,
CTRL.PROCESS_END_TIME,
CTRL.ERROR_CODE,
CTRL.ERROR_MESSAGE,
--ACCOUNT TABLE
ACCT.ERROR_TYPE,
ACCT.CUSTOMER_PROCESS_STATUS,
ACCT.SUPPLIER_PROCESS_STATUS,
ACCT.CUSTOMER_LEVEL,
ACCT.SOURCE_CUSTOMER_REF,
ACCT.CUSTOMER_CLASS_CODE,
ACCT.CUSTOMER_TYPE,
ACCT.BUSINESS_MODEL_CODE,
ACCT.SOURCE_PARENT_CUST_REF,
ACCT.SOURCE_PARENT_CUST_TYPE,
ACCT.CUSTOMER_NAME,
ACCT.ACTIVE_STATUS ACCOUNT_STATUS,
ACCT.CUSTOMER_ALIAS,
ACCT.PERSON_FLAG,
ACCT.PERSON_FIRST_NAME,
ACCT.PERSON_LAST_NAME,
ACCT.TAX_REG_NUMBER,
ACCT.TAX_REG_TYPE,
ACCT.CUSTOMER_ID,
ACCT.ACCOUNT_NUMBER,
ACCT.PARTY_ID,
ACCT.PARTY_TYPE,
ACCT.PARTY_NUMBER,
ACCT.SUPPLIER_ID,
ACCT.AFFILIATE_TYPE,
ACCT.CHECK_PAYABLE_NAME,
ACCT.PAYABLE_FLAG,
ACCT.APPROVED_FLAG,
--SITE TABLE
SITE.PRIMARY_FLAG PRIMARY_SITE,
SITE.ACTIVE_STATUS SITE_STATUS,
SITE.PURPOSE,
SITE.ADDRESS1,
SITE.ADDRESS2,
SITE.ADDRESS3,
SITE.ADDRESS4,
SITE.CITY,
SITE.COUNTY,
SITE.STATE,
SITE.PROVINCE,
SITE.POSTAL_CODE,
SITE.COUNTRY,
SITE.CUST_ACCT_SITE_ID,
SITE.PARTY_SITE_ID,
SITE.LOCATION_ID,
SITE.REPORTABLE_1099,
SITE.TAX_TYPE_1099,
--CONTACT
CONT.PRIMARY_FLAG PRIMARY_CONTACT,
CONT.CONTACT_ROLE,
CONT.ACTIVE_STATUS CONTACT_STATUS,
CONT.FIRST_NAME,
CONT.LAST_NAME,
CONT.SALUTATION,
CONT.JOB_TITLE,
CONT.EMAIL_ADDRESS,
CONT.PHONE_LINE_TYPE,
CONT.PHONE_NUMBER,
CONT.PHONE_EXTENSION,
CONT.PHONE_AREA_CODE,
CONT.PHONE_COUNTRY_CODE,
CONT.PREFERRED_LANGUAGE,
--PRO
PRFL.CUSTOMER_PROFILE_CLASS,
PRFL.ACCOUNT_STATUS PROFILE_ACCT_STATUS,
PRFL.ACTIVE_STATUS PROFILE_ACTIVE_STATUS,
PRFL.ACCOUNT_STATUS_REASON,
PRFL.INVOICE_TYPE
FROM XXEX_AR_CUST_INT_CTRL_T CTRL,
XXEX_AR_CUST_ACCT_INT_T ACCT,
XXEX_AR_CUST_SITE_INT_T SITE,
XXEX_AR_CUST_CONTACT_INT_T CONT,
XXEX_AR_CUST_PROFILE_INT_T PRFL
WHERE CTRL.ORA_PROCESS_ID = ACCT.ORA_PROCESS_ID
AND ACCT.CUSTOMER_RECORD_ID = SITE.CUSTOMER_RECORD_ID
AND SITE.CUSTOMER_RECORD_ID = CONT.CUSTOMER_RECORD_ID
AND CONT.CUSTOMER_RECORD_ID = PRFL.CUSTOMER_RECORD_ID
AND acct.Supplier_process_status = 'E'
/



How i should join the 6 tables..
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 27 2012
Added on Jan 30 2012
18 comments
463 views