1 Reply Latest reply: Nov 13, 2012 10:20 PM by 894936 RSS

    help on putting A,B,C,D conditons into First query

    894936
      Hi Could you please suggest me how to satisfy my A,B,C,D conditions to put into my first query

      Actually i need to put second query into my first query.
      for example i took my conditions as A,B,C,D and i shown in my first query this is my requirement (i need to them into my first query...) could you please suggest me how to proceed further.
      Thanks in advance.

      I am using oracle 10g.
      First 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 invhead.inv_type='D' 
      and  substr(trim(invlog.tot_cust_no),decode(invhead.comp_code,2300,3,1),7) = substr(invhead.acct_no, 3, 7)
       
      and  SUBSTR(opdhlt.reference_identifier(+),1,length(opdhlt.reference_identifier(+))-1)=invhead.comp_code||'-'||SUBSTR(invhead.acct_no,3)||invhead.inv_no
          and invhead.comp_code = '2500' ---------------------------------------------------------A 
       
      and opdhlt.reference_identifier(+) = invhead.comp_code||'-'||
                                       SUBSTR(invhead.acct_no,3)||'  '||invhead.inv_no
          and invhead.comp_code = '1520'-----------------------------------------------------------------------B
       
      and opdhlt.reference_identifier(+) = invhead.comp_code||'-'||invhead.inv_no
          and invhead.comp_code not in ('1520','2500','1206')--------------------------------------------------------C
       
      select invhead.comp_code comp_code,    invhead.inv_no inv_no,
       invhead.acct_no acct_no
        from invoice_header_v invhead
       WHERE not EXISTS
       (select *
       from INVOICE_EXPORTS_FLAGS_V export
       where invhead.inv_no=export.inv_no
       and invhead.comp_code=export.comp_code)
       and comp_code='1206'---------------------------------------------------------------------------------------------------------D
      )
       
       
      *************************
       
      second query
       
       /*select   invhead.comp_code   comp_code,invhead.inv_no
       inv_no,invhead.acct_no acct_no
         from invoice_header_v invhead,
              o_pam_document_header_log_v opdhlt
        where SUBSTR(opdhlt.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
         from invoice_header_v invhead,
              o_pam_document_header_log_v 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
         from invoice_header_v invhead,
              o_pam_document_header_log_v opdhlt
        where opdhlt.reference_identifier(+) = invhead.comp_code||'-'||invhead.inv_no
          and invhead.comp_code not in ('1520','2500','1206')
       UNION ALL
       select invhead.comp_code comp_code,    invhead.inv_no inv_no,
       invhead.acct_no acct_no
        from invoice_header_v invhead
       WHERE not EXISTS
       (select *
       from INVOICE_EXPORTS_FLAGS_V export
       where invhead.inv_no=export.inv_no
       and invhead.comp_code=export.comp_code)
       and comp_code='1206'
       */
      Edited by: 891933 on Sep 30, 2012 10:07 PM