5 Replies Latest reply: Nov 13, 2012 10:21 PM by 894936 RSS

    how to merge my second part query into first query

    894936
      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
          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
            Most of those hints won't work (probably a good thing).
            • 3. Re: how to merge my second part query into first query
              894936
              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
                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
                  Thanks Team...