Home » SQL & PL/SQL » SQL & PL/SQL » outer join
outer join [message #248909] Mon, 02 July 2007 12:04 Go to next message
vamcs
Messages: 20
Registered: June 2007
Location: somerset
Junior Member

HI
i want to make outer join two tables which is not allowing .
can u help me how to do with out degrating performance issue
l.attribute12 = f.order_number||'-'||ool.line_number (+)
Re: outer join [message #248912 is a reply to message #248909] Mon, 02 July 2007 12:19 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Can you post the rest of the query ? Showing only 1 line isn't very helpful.
Re: outer join [message #248913 is a reply to message #248909] Mon, 02 July 2007 12:25 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Use this method:
select c1 from t1,
 (select b2||'a' c2 from u2) t2
where  t1.c1=t2.c2(+)
Re: outer join [message #248921 is a reply to message #248912] Mon, 02 July 2007 13:38 Go to previous messageGo to next message
vamcs
Messages: 20
Registered: June 2007
Location: somerset
Junior Member

insert into XXDSC_REVENUE_REPORT_DETAIL_MV
(
SOURCE,
bill_customer_name,
BILL_CUSTOMER_CLASS_CODE,
BILL_CUSTOMER_CATEGORY_CODE,
BILL_CUSTOMER_TYPE,
CUSTOMER,
GL_PERIOD, PERIOD_SET_NAME, PERIOD_YEAR, PERIOD_NUM,
COMPANY, SEGMENT, PRODUCT, ACCOUNT,
PROJECT, PROD_DESC, SEG_DESC, ACCT_DESC,
revenue_amount, set_of_books_id, je_category, je_source,
memo_or_gl_line_desc, gl_date, je_line_num, set_of_books,
code_combination_id, gl_abbrev,
-- adding the columns for order and line record
order_number ,Order_Header_ID,Header_ID,campaign_name,account_coordinator,
ordered_date,currency_code,closed_flag,purchase_order,sfa_order_id,order_source_id,
bill_to_site_use_id,ship_to_site_use_id,line_start_date,line_end_date,net_cpm,
revenue_rule,billing_rule,OM_SOURCE_REF,hard_end_date,line_ORIG_SYS_DOCUMENT_REF,
line_ORIG_SYS_LINE_REF,line_group_id,"Min$ Per Line Group", "Cap$ Per Month or Line Group",
languages,markets,billing_period,web_service_reference,tax_state_country,subscription_customer,
tax_value,tax_code,tax_date,open_quantity
)
SELECT 'GL' SOURCE,
SUBSTRB (NVL (hp.party_name, 'zManual Entry'),
1,
50
) bill_customer_name,
hca.customer_class_code bill_customer_class_code,
hp.category_code bill_customer_category_code,
hca.customer_type bill_customer_type ,
SUBSTRB (NVL (hp.party_name,
'zManual Entry'), 1, 50) customer,
period.period_name gl_period,
period.period_set_name period_set_name,
period.period_year period_year,
period.period_num period_num,
c.segment1 company,
c.segment2 SEGMENT,
c.segment3 product,
c.segment4 ACCOUNT,
c.segment5 project,
SUBSTRB (prod.description, 1, 150) prod_desc,
SUBSTRB (seg.description, 1, 150) seg_desc,
SUBSTRB (acct.description, 1, 150) acct_desc,
NVL (accounted_cr, 0) -
NVL (accounted_dr, 0) revenue_amount,
h.set_of_books_id set_of_books_id,
je_category je_category,
je_source je_source,
SUBSTRB (l.description, 1, 300) memo_or_gl_line_desc,
h.default_effective_date gl_date,
l.je_line_num je_line_num,
sob.NAME set_of_books,
c.code_combination_id code_combination_id,
SUBSTRB (( c.segment1
|| c.segment2
|| c.segment3
|| c.segment4
|| c.segment5
),
1,
40
) gl_abbrev,
f.ORDER_NUMBER order_number ,
f.header_id Order_Header_ID,
f.header_id Header_ID,
substrb(f.ATTRIBUTE5,1,150) campaign_name,
substrb(f.ATTRIBUTE6,1,50) account_coordinator,
f.ORDERED_DATE ordered_date,
f.transactional_curr_code currency_code,
decode(f.open_flag,'Y','N','Y') closed_flag,
f.cust_po_number purchase_order,
f.orig_sys_document_ref sfa_order_id,
f.order_source_id order_source_id,
f.INVOICE_TO_ORG_ID bill_to_site_use_id,
f.SHIP_TO_ORG_ID ship_to_site_use_id,
to_date(decode(length(ool.attribute1),
9,to_date(ool.attribute1,'DD-MON-RR'),
11,to_date(ool.attribute1,'DD-MON-RRRR'))
) line_start_date,
to_date(decode(length(ool.attribute2),
9,to_date(ool.attribute2,'DD-MON-RR'),
11,to_date(ool.attribute2,'DD-MON-RRRR'))
) line_end_date,
substrb(ool.ATTRIBUTE13,1,20) net_cpm,
substrb(ool.ATTRIBUTE3,1,50) revenue_rule,
substrb(ool.ATTRIBUTE4,1,50) billing_rule,
substrb(ool.ATTRIBUTE5,1,50) OM_SOURCE_REF,
substrb(ool.ATTRIBUTE6,1,20) hard_end_date,
substrb(ool.ORIG_SYS_DOCUMENT_REF,1,30) line_ORIG_SYS_DOCUMENT_REF,
substrb(ool.ORIG_SYS_LINE_REF,1,30) line_ORIG_SYS_LINE_REF,
substrb(ool.attribute7,1,50) line_group_id,
substrb(ool.attribute8,1,50) "Min$ Per Line Group",
substrb(ool.attribute9,1,50) "Cap$ Per Month or Line Group",
substrb(ool.ATTRIBUTE12,1,200) languages,
substrb(ool.ATTRIBUTE13,1,50) markets,
substrb(ool.attribute14,1,50) billing_period,
substrb(ool.attribute16,1,50) web_service_reference,
substrb(ool.attribute17,1,50) tax_state_country,
substrb(ool.attribute18,1,50) subscription_customer,
ool.tax_value tax_value,
ool.tax_code tax_code,
ool.tax_date tax_date,
ool.ORDERED_QUANTITY open_quantity
from
rep_admin.gl_periods period,
appsr.fnd_flex_values_vl prod,
appsr.fnd_flex_values_vl seg,
appsr.fnd_flex_values_vl acct,
rep_admin.gl_je_headers h,
rep_admin.gl_code_combinations c,
rep_admin.gl_sets_of_books sob,
--rep_admin.ra_customers cust,
rep_admin.hz_parties hp ,
rep_admin.hz_cust_accounts hca,
rep_admin.gl_je_lines l,
rep_admin.oe_order_headers_all f,
rep_admin.oe_order_lines_all ool,
(select f.ORDER_NUMBER order_number ,
f.header_id Order_Header_ID,
f.header_id Header_ID,
f.order_number||'-'||ool.line_number ord_det,
substrb(f.ATTRIBUTE5,1,150) campaign_name,
substrb(f.ATTRIBUTE6,1,50) account_coordinator,
f.ORDERED_DATE ordered_date,
f.transactional_curr_code currency_code,
decode(f.open_flag,'Y','N','Y') closed_flag,
f.cust_po_number purchase_order,
f.orig_sys_document_ref sfa_order_id,
f.order_source_id order_source_id,
f.INVOICE_TO_ORG_ID bill_to_site_use_id,
f.SHIP_TO_ORG_ID ship_to_site_use_id,
to_date(decode(length(ool.attribute1),
9,to_date(ool.attribute1,'DD-MON-RR'),
11,to_date(ool.attribute1,'DD-MON-RRRR'))
) line_start_date,
to_date(decode(length(ool.attribute2),
9,to_date(ool.attribute2,'DD-MON-RR'),
11,to_date(ool.attribute2,'DD-MON-RRRR'))
) line_end_date,
substrb(ool.ATTRIBUTE13,1,20) net_cpm,
substrb(ool.ATTRIBUTE3,1,50) revenue_rule,
substrb(ool.ATTRIBUTE4,1,50) billing_rule,
substrb(ool.ATTRIBUTE5,1,50) OM_SOURCE_REF,
substrb(ool.ATTRIBUTE6,1,20) hard_end_date,
substrb(ool.ORIG_SYS_DOCUMENT_REF,1,30) line_ORIG_SYS_DOCUMENT_REF,
substrb(ool.ORIG_SYS_LINE_REF,1,30) line_ORIG_SYS_LINE_REF,
substrb(ool.attribute7,1,50) line_group_id,
substrb(ool.attribute8,1,50) "Min$ Per Line Group",
substrb(ool.attribute9,1,50) "Cap$ Per Month or Line Group",
substrb(ool.ATTRIBUTE12,1,200) languages,
substrb(ool.ATTRIBUTE13,1,50) markets,
substrb(ool.attribute14,1,50) billing_period,
substrb(ool.attribute16,1,50) web_service_reference,
substrb(ool.attribute17,1,50) tax_state_country,
substrb(ool.attribute18,1,50) subscription_customer,
ool.tax_value tax_value,
ool.tax_code tax_code,
ool.tax_date tax_date,
ool.ORDERED_QUANTITY open_quantity from rep_admin.oe_order_headers_all f,
rep_admin.oe_order_lines_all ool where f.header_id=ool.header_id) ord
where h.je_header_id=l.je_header_id and
h.period_name=period.period_name and
l.code_combination_id=c.code_combination_id
and to_number(l.attribute11)=hca.account_number(+)
and hca.party_id = hp.party_id (+)
and c.segment3=prod.flex_value
and prod.flex_value_set_id=1002431 and
c.segment2=seg.flex_value and
seg.flex_value_set_id=1002434 and
c.segment4=acct.flex_value and
acct.flex_value_set_id=1002432 and
c.segment4 between 4000 and 4999 and
h.status='P' and
h.actual_flag='A' and
--and h.set_of_books_id=1
h.currency_code <> 'STAT' and
h.je_source not in ('Receivables','1','Consolidation','AR Translator') and
period.period_year >= 2004 and
nvl(accounted_cr,0)-nvl(accounted_dr,0) <> 0 and h.je_header_id not in (37841,37843,37845,37846,37849)
and h.set_of_books_id = sob.set_of_books_id and
l.attribute12 = ord.ord_det(+);
Re: outer join [message #248938 is a reply to message #248921] Mon, 02 July 2007 15:10 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
./fa/1620/0/
Re: outer join [message #249012 is a reply to message #248938] Tue, 03 July 2007 01:34 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I quick strip & format leads to this (simplified) select:
SELECT hca.customer_class_code bill_customer_class_code
     , hp.category_code bill_customer_category_code
     , period.period_name gl_period
     , c.segment1 company
     , SUBSTRB (prod.description, 1, 150) prod_desc
     , SUBSTRB (seg.description, 1, 150) seg_desc
     , SUBSTRB (acct.description, 1, 150) acct_desc
     , h.set_of_books_id set_of_books_id
     , l.je_line_num je_line_num
     , f.order_number order_number
     , TO_DATE (DECODE (LENGTH (ool.attribute1)
                      , 9, TO_DATE (ool.attribute1, 'DD-MON-RR')
                      , 11, TO_DATE (ool.attribute1, 'DD-MON-RRRR')
                       )
               ) line_start_date
     , TO_DATE (DECODE (LENGTH (ool.attribute2)
                      , 9, TO_DATE (ool.attribute2, 'DD-MON-RR')
                      , 11, TO_DATE (ool.attribute2, 'DD-MON-RRRR')
                       )
               ) line_end_date
     , ool.tax_value tax_value
FROM   rep_admin.gl_periods period
     , appsr.fnd_flex_values_vl prod
     , appsr.fnd_flex_values_vl seg
     , appsr.fnd_flex_values_vl acct
     , rep_admin.gl_je_headers h
     , rep_admin.gl_code_combinations c
     , rep_admin.gl_sets_of_books sob
     ,
--rep_admin.ra_customers cust,
       rep_admin.hz_parties hp
     , rep_admin.hz_cust_accounts hca
     , rep_admin.gl_je_lines l
     , rep_admin.oe_order_headers_all f
     , rep_admin.oe_order_lines_all ool
     , (SELECT f.order_number order_number
             , f.header_id order_header_id
             , f.header_id header_id
             , f.order_number || '-' || ool.line_number ord_det
             , SUBSTRB (f.attribute5, 1, 150) campaign_name
             , SUBSTRB (f.attribute6, 1, 50) account_coordinator
             , f.ordered_date ordered_date
             , f.transactional_curr_code currency_code
             , DECODE (f.open_flag, 'Y', 'N', 'Y') closed_flag
             , f.cust_po_number purchase_order
             , f.orig_sys_document_ref sfa_order_id
             , f.order_source_id order_source_id
             , f.invoice_to_org_id bill_to_site_use_id
             , f.ship_to_org_id ship_to_site_use_id
             , TO_DATE (DECODE (LENGTH (ool.attribute1)
                              , 9, TO_DATE (ool.attribute1, 'DD-MON-RR')
                              , 11, TO_DATE (ool.attribute1, 'DD-MON-RRRR')
                               )
                       ) line_start_date
             , TO_DATE (DECODE (LENGTH (ool.attribute2)
                              , 9, TO_DATE (ool.attribute2, 'DD-MON-RR')
                              , 11, TO_DATE (ool.attribute2, 'DD-MON-RRRR')
                               )
                       ) line_end_date
             , SUBSTRB (ool.attribute13, 1, 20) net_cpm
             , SUBSTRB (ool.attribute3, 1, 50) revenue_rule
             , SUBSTRB (ool.attribute4, 1, 50) billing_rule
             , SUBSTRB (ool.attribute5, 1, 50) om_source_ref
             , SUBSTRB (ool.attribute6, 1, 20) hard_end_date
             , SUBSTRB (ool.orig_sys_document_ref, 1, 30)
                                                   line_orig_sys_document_ref
             , SUBSTRB (ool.orig_sys_line_ref, 1, 30) line_orig_sys_line_ref
             , SUBSTRB (ool.attribute7, 1, 50) line_group_id
             , SUBSTRB (ool.attribute8, 1, 50) "Min$ Per Line Group"
             , SUBSTRB (ool.attribute9, 1, 50) "Cap$ Per Month or Line Group"
             , SUBSTRB (ool.attribute12, 1, 200) languages
             , SUBSTRB (ool.attribute13, 1, 50) markets
             , SUBSTRB (ool.attribute14, 1, 50) billing_period
             , SUBSTRB (ool.attribute16, 1, 50) web_service_reference
             , SUBSTRB (ool.attribute17, 1, 50) tax_state_country
             , SUBSTRB (ool.attribute18, 1, 50) subscription_customer
             , ool.tax_value tax_value
             , ool.tax_code tax_code
             , ool.tax_date tax_date
             , ool.ordered_quantity open_quantity
        FROM   rep_admin.oe_order_headers_all f
             , rep_admin.oe_order_lines_all ool
        WHERE  f.header_id = ool.header_id) ord
WHERE  h.je_header_id = l.je_header_id
AND    h.period_name = period.period_name
AND    l.code_combination_id = c.code_combination_id
AND    TO_NUMBER (l.attribute11) = hca.account_number(+)
AND    hca.party_id = hp.party_id(+)
AND    c.segment3 = prod.flex_value
AND    prod.flex_value_set_id = 1002431
AND    c.segment2 = seg.flex_value
AND    seg.flex_value_set_id = 1002434
AND    c.segment4 = acct.flex_value
AND    acct.flex_value_set_id = 1002432
AND    c.segment4 BETWEEN 4000 AND 4999
AND    h.status = 'P'
AND    h.actual_flag = 'A'
AND
--and h.set_of_books_id=1
       h.currency_code <> 'STAT'
AND    h.je_source NOT IN
                       ('Receivables', '1', 'Consolidation', 'AR Translator')
AND    period.period_year >= 2004
AND    NVL (accounted_cr, 0) - NVL (accounted_dr, 0) <> 0
AND    h.je_header_id NOT IN (37841, 37843, 37845, 37846, 37849)
AND    h.set_of_books_id = sob.set_of_books_id
AND    l.attribute12 = ord.ord_det(+);


MHE
Previous Topic: FORCE VIEW
Next Topic: Level Pseudo Column
Goto Forum:
  


Current Time: Thu Dec 08 12:08:46 CST 2016

Total time taken to generate the page: 0.07258 seconds