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

help on combining two queries

894936 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    shall i send sample data along with table structure...........
  • 3. Re: help on combining two queries
    BluShadow Guru Moderator
    Currently Being Moderated
    Please read: {message:id=9360002}
  • 4. Re: help on combining two queries
    894936 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks

Legend

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