This discussion is archived
5 Replies Latest reply: Nov 13, 2012 8:21 PM by 894936 RSS

how to merge my second part query into first query

894936 Newbie
Currently Being Moderated
Below are the two queries which i have prepared.could you please help me in merging these two queries together.
Thanks for ur help in advance.

And could you please suggest me for tuning this query.

part 1:

SELECT/*+parellal(2)*/
bu_code,bu_type,cust_no ,cur_code,sales_date,receipt_no,till_no,card_no,invoice_total,amount_of_goods,
(invoice_total - (amount_of_goods+pay_in_advance+amount_of_discounts)) AS amount_of_non_goods,
pay_in_advance AS amounts_of_advance_pay,amount_of_discounts, Error_flag,inv_no,comp_code
FROM
(select
/*+ use_merge (workinv invhead) */
           /*+ use_nl (invlog invhead) */
           /*+ all_rows (workinv invsums) */
           /*+ use_hash (invsums invlog) */
           /*+ USE_NL_WITH_INDEX (invsums invoice_sums_pk)
           leading (invsums workinv)*/
            distinct  'STO' AS BU_TYPE,
workinv.tot_cust_no AS  cust_no,
workinv.comp_code as comp_code,
workinv.cash_no as till_no,
workinv.receipt_no  as receipt_no,
workinv.sales_date as sales_date,
--workinv.error_flag as error_flag,
workinv.cur_code as cur_code,
invhead.acct_usr_no as card_no,
invhead.inv_no as inv_no,
invhead.sto_no as bu_code, (SELECT MAX (DECODE (e.sum_code, 'TOTAL', e.amount_incl))
                                          FROM invoice_sums_t e
                                          WHERE e.inv_no = invsums.inv_no
                                          AND e.comp_code = invsums.comp_code) AS invoice_total,
                                         (SELECT MAX (DECODE (e.sum_code, 'PIA', e.amount_incl))
                                           FROM invoice_sums_t e
                                           WHERE e.inv_no = invsums.inv_no
                                          AND e.comp_code = invsums.comp_code) AS pay_in_advance,
                                         (SELECT SUM(e.amount_incl)
                                          FROM invoice_sums_t e
                                          where  regexp_like(E.SUM_CODE, '^GOODS([[:digit:]]+|$)')
                                          --where  regexp_like(e.SUM_CODE, 'GOODS0[[:digit:]]+')
                                          --WHERE E.SUM_CODE LIKE 'GOODS0%'---goodso+goods01+goods02
                                          AND e.inv_no = invsums.inv_no
                                          AND e.comp_code = invsums.comp_code) AS amount_of_goods,
                                         (SELECT SUM(e.amount_incl)
                                          FROM invoice_sums_t e
                                          where  regexp_like(E.SUM_CODE, '^DISCOUNT([[:digit:]]+|$)')
                                        -- WHERE E.SUM_CODE LIKE 'DISCOUNT0%'
                                          AND e.inv_no = invsums.inv_no
                                          AND e.comp_code = invsums.comp_code) AS amount_of_discounts ,
                                  CASE workinv.error_flag WHEN 'H' THEN 'Y' ELSE 'N' END
                                  AS Error_flag,
                                  WORKINV.ERROR_FLAG AS invoice_on_hold
FROM  work_invoice_info_t workinv, --select from views  and try to create views if not available. no problem we can create the views.
          invoice_header_t invhead,  --follow the naming conventions.
          invoice_sums_t invsums,
          i_invoice_info_t_log invlog,
          o_pam_document_header_log_t opdhlt
 WHERE  invhead.comp_code= workinv.comp_code
 AND TRIM(workinv.Tot_cust_no) =TRIM(invlog.tot_cust_no)
 AND TRIM (workinv.sto_no) =invhead.sto_no
 AND TRIM (workinv.sales_date) =TO_CHAR (invhead.sales_date, 'YYMMDD')
 AND TRIM (workinv.cash_no) =TO_NUMBER (TRIM (INVhead.cash_no))
 AND TRIM (workinv.receipt_no) =TO_NUMBER (TRIM (invhead.receipt_no))
 --not required nvl(not null values)
 AND invhead.comp_code = invsums.comp_code
 AND invhead.inv_no = invsums.inv_no
 AND invhead.inv_type='D'
 AND TRIM(workinv.sto_no) = invlog.sto_no
 AND TRIM(workinv.receipt_no) =TO_NUMBER(TRIM(invlog.receipt_no))
 AND TRIM(workinv.cash_no) =TO_NUMBER(TRIM(invlog.cash_no))
 AND TRIM(workinv.sales_date) = invlog.sales_date)
 
second part:


select *
  from
(
select invhead.comp_code comp_code,invhead.inv_no inv_no,invhead.acct_no acct_no,
       opdhlt.reference_identifier ref_id
  from invoice_header_t invhead,
       o_pam_document_header_log_t opdhlt
 where SUBSTR(ipdapt.reference_identifier(+),1,length(opdhlt.reference_identifier(+))-1) =
                            invhead.comp_code||'-'||SUBSTR(invhead.acct_no,3)||invhead.inv_no
   and invhead.comp_code = '2500'
  

UNION ALL

select invhead.comp_code comp_code,invhead.inv_no inv_no,invhead.acct_no acct_no,
       opdhlt.reference_identifier ref_id
  from invoice_header_t invhead,
       o_pam_document_header_log_t opdhlt
 where opdhlt.reference_identifier(+) = invhead.comp_code||'-'||
                                SUBSTR(invhead.acct_no,3)||'  '||invhead.inv_no
   and invhead.comp_code = '1520'
 
UNION ALL

select invhead.comp_code comp_code,    invhead.inv_no inv_no,    invhead.acct_no acct_no,
       opdhlt.reference_identifier ref_id
  from invoice_header_t invhead,
       o_pam_document_header_log_t opdhlt
 where opdhlt.reference_identifier(+) = invhead.comp_code||'-'||invhead.inv_no
   and invhead.comp_code not in ('1520','2500')

)
Edited by: 891933 on Sep 13, 2012 7:47 AM
  • 1. Re: how to merge my second part query into first query
    Pleiadian Journeyer
    Currently Being Moderated
    What exactly do you mean by merge?

    Why do you have all those trims in part 1? Doesn't it make more sense to clean up the data and make sure it is stored in a uniform manner? I sure hope my invoice isn't in there somewhere ;)

    You can avoid those union all statements in part 2 and write something like (unchecked):
    select  invhead.comp_code           comp_code,
            invhead.inv_no              inv_no,    
            invhead.acct_no             acct_no,
            opdhlt.reference_identifier ref_id
    from    invoice_header_t            invhead,
            o_pam_document_header_log_t opdhlt
    where ( invhead.comp_code = '2500' and substr(ipdapt.reference_identifier(+),1,length(opdhlt.reference_identifier(+))-1) = invhead.comp_code||'-'||substr(invhead.acct_no,3)||invhead.inv_no ) 
    or    ( invhead.comp_code = '1520' and opdhlt.reference_identifier(+) = invhead.comp_code || '-' || substr(invhead.acct_no,3) || '  ' || invhead.inv_no )
    or    ( invhead.comp_code not in ('1520','2500') and opdhlt.reference_identifier(+) = invhead.comp_code||'-'||invhead.inv_no )
  • 2. Re: how to merge my second part query into first query
    BrendanP Journeyer
    Currently Being Moderated
    Most of those hints won't work (probably a good thing).
  • 3. Re: how to merge my second part query into first query
    894936 Newbie
    Currently Being Moderated
    Hi,

    here my requirement is in first part i need to satisfy all the conditions.
    and i need to include second part query into my first part query.
    ....like how i need to write both queries together by satisfying all the conditions.

    When i tried to run the below query its throwing me an error like
    "outer join operator will not operand or or and."
    could you please suggest me on this.

    Thanks,
    Mamatha.B
    select  invhead.comp_code           comp_code,
            invhead.inv_no              inv_no,    
            invhead.acct_no             acct_no,
            opdhlt.reference_identifier ref_id
    from    invoice_header_t            invhead,
            o_pam_document_header_log_t opdhlt
    where ( invhead.comp_code = '2500' and substr(opdhlt.reference_identifier(+),1,length(opdhlt.reference_identifier(+))-1) = invhead.comp_code||'-'||substr(invhead.acct_no,3)||invhead.inv_no ) 
    or    ( invhead.comp_code = '1520' and opdhlt.reference_identifier(+) = invhead.comp_code || '-' || substr(invhead.acct_no,3) || '  ' || invhead.inv_no )
    or    ( invhead.comp_code not in ('1520','2500') and opdhlt.reference_identifier(+) = invhead.comp_code||'-'||invhead.inv_no )
    Edited by: 891933 on Sep 13, 2012 10:25 PM
  • 4. Re: how to merge my second part query into first query
    user10566312 Newbie
    Currently Being Moderated
    For the immediately above post check if this works:
    select invhead.comp_code comp_code,
    invhead.inv_no inv_no, 
    invhead.acct_no acct_no,
    opdhlt.reference_identifier ref_id
    from invoice_header_t invhead
    LEFT OUTER JOIN
    o_pam_document_header_log_t opdhlt
    ON
    (
       ( invhead.comp_code = '2500' and substr(opdhlt.reference_identifier,1,length(opdhlt.reference_identifier)-1) = invhead.comp_code||'-'||substr(invhead.acct_no,3)||invhead.inv_no ) 
    or ( invhead.comp_code = '1520' and opdhlt.reference_identifier = invhead.comp_code || '-' || substr(invhead.acct_no,3) || ' ' || invhead.inv_no )
    or ( invhead.comp_code not in ('1520','2500') and opdhlt.reference_identifier = invhead.comp_code||'-'||invhead.inv_no )
    )
  • 5. Re: how to merge my second part query into first query
    894936 Newbie
    Currently Being Moderated
    Thanks Team...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points