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

    help on combining two queries

    894936
      Hi Team,

      can you guide me how to combine first query and second query.
      i want to satisfy the first query condition in second part(main) query.
      i need to combine both queries together. can u guide me on this.


      first query :
      
      SELECT *
      FROM invoice_header_t head
      WHERE not EXISTS
      (select *
      from INVOICE_EXPORTS_FLAGS_V export
      where head.inv_no=export.inv_no
      and head.comp_code=export.comp_code)
      and comp_code='1206' and inv_status='J' 
      
      second query:
      
      
      SELECT/*+parellal(2)*/
      bu_code,bu_type,cust_no,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,inv_no,comp_code, 'N' AS Error_flag
      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,
      substr(INVHEAD.ACCT_NO,3,7) AS  cust_no,
      invhead.comp_code as comp_code,
      invhead.cash_no as till_no,
      invhead.receipt_no  as receipt_no,
      invhead.sales_date as sales_date,
      invhead.acct_usr_no as card_no,
      invhead.inv_no as inv_no,
      invhead.sto_no as bu_code, (SELECT NVL(MAX (DECODE (e.sum_code, 'TOTAL', e.amount_incl)),'0')
                                                FROM invoice_sums_v e
                                                WHERE e.inv_no = invsums.inv_no
                                                AND e.comp_code = invsums.comp_code) AS invoice_total,
                                               (SELECT NVL(MAX (DECODE (e.sum_code, 'PIA', e.amount_incl)),'0')
                                                 FROM invoice_sums_v e
                                                 WHERE e.inv_no = invsums.inv_no
                                                AND e.comp_code = invsums.comp_code) AS pay_in_advance,
                                               (SELECT NVL(SUM(e.amount_incl),'0')
                                                FROM invoice_sums_v e
                                                where  regexp_like(E.SUM_CODE, '^GOODS([[:digit:]]+|$)')
                                                AND e.inv_no = invsums.inv_no
                                                AND e.comp_code = invsums.comp_code) AS amount_of_goods,
                                               (SELECT NVL(SUM(e.amount_incl),'0')
                                                FROM invoice_sums_v e
                                                where  regexp_like(E.SUM_CODE, '^DISCOUNT([[:digit:]]+|$)')
                                                AND e.inv_no = invsums.inv_no
                                                AND e.comp_code = invsums.comp_code) AS amount_of_discounts
                                             FRom invoice_header_v invhead,
                invoice_sums_v invsums,
                o_pam_document_header_log_v opdhlt,
                i_invoice_info_t_log_v invlog
      where invhead.comp_code=invsums.comp_code
      and invhead.inv_no=invsums.inv_no
      and trim(invlog.sto_no)=trim(invhead.sto_no)
      and trim(INVLOG.CASH_NO)=trim(invhead.cash_no)
      and trim(invlog.receipt_no)=trim(invhead.receipt_no)
      and trim(invlog.sales_date)=to_char(invhead.sales_date,'YYMMDD')
      and (
      (substr(invlog.tot_cust_no,1,7)=substr(invhead.acct_no,3,7)
                 and invhead.comp_code NOT in (2300)
                 )
                OR
                (substr(invlog.tot_cust_no,3,7)=to_char(substr(invhead.acct_no,3,7))
                 and invhead.comp_code in (2300)
                )
      )
      and invhead.inv_type='D'   
      AND  SUBSTR(opdhlt.reference_identifier(+),1,length(opdhlt.reference_identifier(+))-1) =
                            invhead.comp_code||'-'||SUBSTR(invhead.acct_no,3)||invhead.inv_no
      AND opdhlt.reference_identifier(+) = invhead.comp_code||'-'||
      SUBSTR(invhead.acct_no,3)||'  '||invhead.inv_no
      AND  opdhlt.reference_identifier(+) = invhead.comp_code||'-'||invhead.inv_no
      AND invhead.comp_code not in ( 2500,1520)
      )
       
       
       
        • 1. Re: help on combining two queries
          BluShadow
          891933 wrote:
          Hi Team,

          can you guide me how to combine first query and second query.
          i want to satisfy the first query condition in second part(main) query.
          i need to combine both queries together. can u guide me on this.
          It's not clear how you want to combine them. We don't have your tables or data, so it's not clear what the queries are doing, or what results you are expecting.

          And what's with all the hints?
          SELECT/*+parellal(2)*/
          bu_code,bu_type,cust_no,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,inv_no,comp_code, 'N' AS Error_flag
          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)*/
          Apart from the fact that parallel is spelt wrongly (in which case the hint will be ignored), why all the hints telling the optimiser how to do it's job?
          That would indicate either a poorly designed database, a lack of statistics, or a very poorly designed query.
          • 2. Re: help on combining two queries
            894936
            shall i send sample data along with table structure...........
            • 3. Re: help on combining two queries
              BluShadow
              Please read: {message:id=9360002}
              • 4. Re: help on combining two queries
                894936
                Hi Team,
                I need your help.

                My Requirement is :
                below i have two queries. " first main query " and "Second query"

                i need to put my second query into my first main query.

                expected output is i need to get the records which satisfies the conditions specified in first main query and second query.

                i need to PUT my second query into First query.
                first main query:
                
                SELECT
                bu_code,bu_type,cust_no,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,inv_no,comp_code, 'N' AS Error_flag
                FROM
                (
                select
                distinct  'STO' AS BU_TYPE,
                substr(INVHEAD.ACCT_NO,3,7) AS  cust_no,
                invhead.comp_code as comp_code,
                invhead.cash_no as till_no,
                invhead.receipt_no  as receipt_no,
                invhead.sales_date as sales_date,
                invhead.acct_usr_no as card_no,
                invhead.inv_no as inv_no,
                invhead.sto_no as bu_code, (SELECT NVL(MAX (DECODE (e.sum_code, 'TOTAL', e.amount_incl)),'0')
                                                          FROM invoice_sums_v e
                                                          WHERE e.inv_no = invsums.inv_no
                                                          AND e.comp_code = invsums.comp_code) AS invoice_total,
                                                         (SELECT NVL(MAX (DECODE (e.sum_code, 'PIA', e.amount_incl)),'0')
                                                           FROM invoice_sums_v e
                                                           WHERE e.inv_no = invsums.inv_no
                                                          AND e.comp_code = invsums.comp_code) AS pay_in_advance,
                                                         (SELECT NVL(SUM(e.amount_incl),'0')
                                                          FROM invoice_sums_v e
                                                          where  regexp_like(E.SUM_CODE, '^GOODS([[:digit:]]+|$)')
                                                          AND e.inv_no = invsums.inv_no
                                                          AND e.comp_code = invsums.comp_code) AS amount_of_goods,
                                                         (SELECT NVL(SUM(e.amount_incl),'0')
                                                          FROM invoice_sums_v e
                                                          where  regexp_like(E.SUM_CODE, '^DISCOUNT([[:digit:]]+|$)')
                                                          AND e.inv_no = invsums.inv_no
                                                          AND e.comp_code = invsums.comp_code) AS amount_of_discounts
                                                       FRom invoice_header_v invhead,
                          invoice_sums_v invsums,
                          o_pam_document_header_log_v opdhlt,
                          i_invoice_info_t_log_v invlog
                where invhead.comp_code=invsums.comp_code
                and invhead.inv_no=invsums.inv_no
                and trim(invlog.sto_no)=trim(invhead.sto_no)
                and trim(INVLOG.CASH_NO)=trim(invhead.cash_no)
                and trim(invlog.receipt_no)=trim(invhead.receipt_no)
                and trim(invlog.sales_date)=to_char(invhead.sales_date,'YYMMDD')
                and (
                (substr(invlog.tot_cust_no,1,7)=substr(invhead.acct_no,3,7)
                           and invhead.comp_code NOT in (2300)
                           )
                          OR
                          (substr(invlog.tot_cust_no,3,7)=to_char(substr(invhead.acct_no,3,7))
                           and invhead.comp_code in (2300)
                          )
                )
                and invhead.inv_type='D'   
                )
                
                second query:
                
                select invhead.comp_code comp_code,invhead.inv_no inv_no
                from invoice_header_v invhead,
                o_pam_document_header_log_t ipdapt
                where SUBSTR(ipdapt.reference_identifier(+),1,length(ipdapt.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
                from invoice_header_v invhead,
                o_pam_document_header_log_t ipdapt
                where ipdapt.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
                from invoice_header_v invhead,
                o_pam_document_header_log_t ipdapt
                where ipdapt.reference_identifier(+) = invhead.comp_code||'-'||invhead.inv_no
                and invhead.comp_code not in ('1520','2500')
                • 5. Re: help on combining two queries
                  894936
                  Thanks