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

    Put second and Third queries into First Query

    894936
      Hi Team,

      could you please help me on below.
      I have three queries ... first , second and Third.

      i need to put my second and third part queries into first query.
      so how i need to satisfy both second query and third conditions.
      could you please suggest me on this.


       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,
                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_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
      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
      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')
      
      Third Query:
      
      SELECT *
      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'
      
      
      my table structures:
      
      CREATE TABLE RIMS.INVOICE_HEADER_V
      (
        INV_NO           VARCHAR2(10 BYTE)            NOT NULL,
        COMP_CODE        VARCHAR2(12 BYTE)            NOT NULL,
        ACCT_NO          VARCHAR2(216 BYTE)           NOT NULL,
        ACCT_USR_NO      VARCHAR2(3 BYTE)             NOT NULL,
        CASH_NO          VARCHAR2(5 BYTE),
        RECEIPT_NO       VARCHAR2(10 BYTE),
        ORDER_NO         VARCHAR2(12 BYTE),
        SALES_DATE       DATE                         NOT NULL,
        INV_DATE         DATE,
        INV_DUE_DATE     DATE,
        INV_TYPE         VARCHAR2(1 BYTE)             NOT NULL,
        INV_DATE_PRINT   DATE,
        INV_DATE_SEND    DATE,
        STO_NO           VARCHAR2(3 BYTE)             NOT NULL,
        INV_BATCH_DATE   DATE,
        UPD_DATE         DATE,
        INV_DISC_PROC    NUMBER(5,2),
        INV_AUTO         VARCHAR2(1 BYTE)             NOT NULL,
        INV_VAT_OBLIGED  VARCHAR2(1 BYTE)             NOT NULL,
        INV_ADM_COST_FR  VARCHAR2(1 BYTE)             NOT NULL,
        PAY_TYPE         VARCHAR2(1 BYTE),
        CUSDEL_TYPE      VARCHAR2(2 BYTE)             NOT NULL,
        INV_STATUS       VARCHAR2(1 BYTE),
        PU_ORDER_NO      VARCHAR2(90 BYTE),
        CONTROL_NO       NUMBER(4),
        ORIG_INVO        VARCHAR2(10 BYTE)
      )
      
      CREATE TABLE RIMS.INVOICE_SUMS_V
      (
        INV_NO       VARCHAR2(10 BYTE)                NOT NULL,
        COMP_CODE    VARCHAR2(12 BYTE)                NOT NULL,
        SUM_CODE     VARCHAR2(15 BYTE)                NOT NULL,
        AMOUNT_EXCL  NUMBER(11,2)                     NOT NULL,
        AMOUNT_INCL  NUMBER(11,2)                     NOT NULL,
        VAT          NUMBER(11,2),
        CUSDEL_NO    NUMBER(2)                        NOT NULL
      )
      
      CREATE TABLE RIMS.I_INVOICE_INFO_T_LOG_V
      (
        REG_DATE         VARCHAR2(6 BYTE),
        STO_NO           VARCHAR2(3 BYTE),
        SALES_DATE       VARCHAR2(6 BYTE),
        CASH_NO          NUMBER(5),
        RECEIPT_NO       NUMBER(10),
        ORDER_NO         NUMBER(10),
        TOT_CUST_NO      VARCHAR2(13 BYTE),
        CONTROL_NO       NUMBER(10),
        INV_ADM_COST_FR  VARCHAR2(1 BYTE),
        VAT_OBLIGED      VARCHAR2(1 BYTE),
        ART_NO           VARCHAR2(8 BYTE),
        SOLD_QTY         NUMBER(10),
        SOLD_AMOUNT      NUMBER(10),
        CUR_CODE         VARCHAR2(3 BYTE),
        ORIG_INVO        VARCHAR2(10 BYTE),
        CUSDEL_NO        NUMBER(2),
        SEQ_NO_SORT      NUMBER(10),
        GROUP_ID_PROD    NUMBER(10),
        GROUP_ID_TXT     NUMBER(10),
        CUSDEL_TYPE      VARCHAR2(2 BYTE),
        VAT_CODE         VARCHAR2(10 BYTE),
        VAT_RATE         NUMBER(6,2),
        TIMESTAMP        DATE,
        INV_TYPE         VARCHAR2(1 BYTE),
        INS_USER         VARCHAR2(30 BYTE),
        MACHINE          VARCHAR2(64 BYTE),
        SEQ              NUMBER(22)                   NOT NULL
      )
      
      CREATE TABLE RIMS.O_PAM_DOCUMENT_HEADER_LOG_V
      (
        BUSINESS_TYPE             VARCHAR2(4 BYTE),
        REFERENCE_IDENTIFIER      VARCHAR2(30 BYTE),
        CREDIT_DEBIT_INDICATOR    VARCHAR2(3 BYTE),
        PURCHASE_SALES_INDICATOR  VARCHAR2(8 BYTE),
        INVOICE_NUMBER            VARCHAR2(30 BYTE),
        INVOICE_DATE              DATE,
        TAX_CURRENCY_ANNUAL_RATE  VARCHAR2(1 BYTE),
        INVOICE_CURRENCY          VARCHAR2(3 BYTE),
        INVOICE_CUR_ALLOWED_DEC   NUMBER(1),
        INVOICE_GROSS_AMOUNT      NUMBER(11,2),
        TAX_INDICATOR             VARCHAR2(8 BYTE),
        TAX_COUNTRY               VARCHAR2(2 BYTE),
        TAX_CURRENCY              VARCHAR2(1 BYTE),
        PAYMENT_SCENARIO_FLAG     NUMBER(1),
        DUE_DATE                  DATE,
        COST_BOOKING_TYPE         VARCHAR2(8 BYTE),
        AUTHORIZING_USER          VARCHAR2(1 BYTE),
        DESCRIPTION               VARCHAR2(1024 BYTE),
        PAM_ACKNOWLEDGMENT        VARCHAR2(3 BYTE),
        TIMESTAMP                 DATE
      )
      
      CREATE TABLE RIMS.INVOICE_EXPORTS_FLAGS_V
      (
        INV_NO     VARCHAR2(10 BYTE)                  NOT NULL,
        COMP_CODE  VARCHAR2(12 BYTE)                  NOT NULL,
        FLAG       VARCHAR2(30 BYTE),
        VALUE      VARCHAR2(30 BYTE),
        INS_DATE   DATE,
        FILE_SEQ   NUMBER(9),
        FILE_NAME  NUMBER(9)
      )
        • 1. Re: Put second and Third queries into First Query
          BluShadow
          Well, thanks for posting your table structures and queries and for using code tags to format it... that helps.

          However, you haven't told us your database version (which could determine the solutions available to you) and we don't have any of your data (example or otherwise) or know what the output of these combined queries should be. You have to remember that we don't know your business model or logic, or the relationship between the tables these queries use.

          So what you've given us is three seperate queries, all selecting different columns, and you've said you want them combining, but not what the result of those queries should look like, or any explanation of the logic behind them.

          We're going to struggle to provide a correct solution in that case, and you'd likely have a better idea of how to do it than us at the minute.

          Please read: {message:id=9360002} and ensure you provide enough information to allow us to help you.