This discussion is archived
1 Reply Latest reply: Nov 13, 2012 8:20 PM by 894936 RSS

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

894936 Newbie
Currently Being Moderated
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

Legend

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