4 Replies Latest reply: Nov 13, 2012 8:16 PM by 894936 RSS

    Output as 2 records.

    894936
      Hi Team,
      i am using oracle 10g.
      below is my table with sample insert statements.
      i want the expected output as mentioned below.

      ---two rows are having same values like below then we wont consider that record. because the values like comp_code,tot_cust_no,sto_no,sales_date,cash_no are same
      but trans_type are having HOLD and UNHOLD with same date... then we consider that record at all.

      1801     1504522002     601     120306     1     142     110125611     120628     HOLD     Y     6/18/2012 12:47:20 PM     6/18/2012 12:47:20 PM
      1801     1504522002     601     120306     1     142     110125611     120701     UNHOLD     3     6/28/2012 3:26:39 PM     6/28/2012 3:26:39 PM

      could you please help me on getting my expected output.
      CREATE TABLE RIMS.WORK_INVOICE_HOLD_T
      (
        COMP_CODE                 VARCHAR2(4 BYTE),
        TOT_CUST_NO               VARCHAR2(13 BYTE),
        STO_NO                    VARCHAR2(3 BYTE),
        SALES_DATE                VARCHAR2(6 BYTE),
        CASH_NO                   NUMBER(5),
        RECEIPT_NO                NUMBER(10),
        ORDER_NO                  NUMBER(10),
        ESTIMATED_COMPLETED_DATE  VARCHAR2(6 BYTE),
        TRANS_TYPE                VARCHAR2(10 BYTE),
        PROCESSED                 VARCHAR2(1 BYTE),
        INS_DATE                  DATE,
        UPD_DATE                  DATE
      )
       
      insert statement:
       
      SET DEFINE OFF;
      Insert into RIMS.WORK_INVOICE_HOLD_T
         (COMP_CODE, TOT_CUST_NO, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, ESTIMATED_COMPLETED_DATE, TRANS_TYPE, PROCESSED, INS_DATE, UPD_DATE)
       Values
         ('2300', '0062090790', '121', '120306', 3, 
          132, 110125611, '120628', 'HOLD', 'Y', 
          TO_DATE('06/18/2012 12:47:20', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/18/2012 12:47:20', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into RIMS.WORK_INVOICE_HOLD_T
         (COMP_CODE, TOT_CUST_NO, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, ESTIMATED_COMPLETED_DATE, TRANS_TYPE, PROCESSED, INS_DATE, UPD_DATE)
       Values
         ('1801', '1504522002', '601', '120306', 1, 
          142, 110125611, '120628', 'HOLD', 'Y', 
          TO_DATE('06/18/2012 12:47:20', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/18/2012 12:47:20', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into RIMS.WORK_INVOICE_HOLD_T
         (COMP_CODE, TOT_CUST_NO, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, ESTIMATED_COMPLETED_DATE, TRANS_TYPE, PROCESSED, INS_DATE, UPD_DATE)
       Values
         ('1801', '1504522002', '601', '120306', 1, 
          142, 110125611, '120701', 'UNHOLD', '3', 
          TO_DATE('06/28/2012 15:26:39', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/28/2012 15:26:39', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into RIMS.WORK_INVOICE_HOLD_T
         (COMP_CODE, TOT_CUST_NO, STO_NO, SALES_DATE, CASH_NO, RECEIPT_NO, ORDER_NO, ESTIMATED_COMPLETED_DATE, TRANS_TYPE, PROCESSED, INS_DATE, UPD_DATE)
       Values
         ('1801', '1504522002', '601', '120618', 1, 
          142, 110125611, '120701', 'HOLD', 'Y', 
          TO_DATE('06/29/2012 11:17:37', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/29/2012 11:17:37', 'MM/DD/YYYY HH24:MI:SS'));
      COMMIT;
        
      when i run the above table and insert statements i will get below records from the table 
      2300     0062090790     121     120306     3     132     110125611     120628     HOLD     Y     6/18/2012 12:47:20 PM     6/18/2012 12:47:20 PM
      1801     1504522002     601     120306     1     142     110125611     120628     HOLD     Y     6/18/2012 12:47:20 PM     6/18/2012 12:47:20 PM
      1801     1504522002     601     120306     1     142     110125611     120701     UNHOLD     3     6/28/2012 3:26:39 PM       6/28/2012 3:26:39 PM
      1801     1504522002     601     120618     1     142     110125611     120701     HOLD     Y     6/29/2012 11:17:37 AM     6/29/2012 11:17:37 AM
      
      expected output:
      2300     0062090790     121     120306     3     132     110125611     120628     HOLD     Y     6/18/2012 12:47:20 PM     6/18/2012 12:47:20 PM
      1801     1504522002     601     120618     1     142     110125611     120701     HOLD     Y     6/29/2012 11:17:37 AM     6/29/2012 11:17:37 AM
      Edited by: 891933 on Oct 4, 2012 3:48 AM

      Edited by: 891933 on Oct 4, 2012 3:54 AM

      Edited by: 891933 on Oct 4, 2012 4:05 AM
        • 1. Re: Output as 2 records.
          Ashu_Neo
          I am not getting your query ?

          You have inserted 4 records and it will show 4 records right ! Could you please elaborate/specify it more ?
          when i run the above table and insert statements i will get below records from the table 
          2300     0062090790     121     120306     3     132     110125611     120628     HOLD     Y     6/18/2012 12:47:20 PM     6/18/2012 12:47:20 PM
          1801     1504522002     601     120306     1     142     110125611     120628     HOLD     Y     6/18/2012 12:47:20 PM     6/18/2012 12:47:20 PM
          1801     1504522002     601     120306     1     142     110125611     120701     UNHOLD     3     6/28/2012 3:26:39 PM      6/28/2012 3:26:39 PM
          1801     1504522002     601     120618     1     142     110125611     120701     HOLD     Y     6/29/2012 11:17:37 AM     6/29/2012 11:17:37 AM
          
          expected output:
          2300     0062090790     121     120306     3     132     110125611     120628     HOLD     Y     6/18/2012 12:47:20 PM     6/18/2012 12:47:20 PM
          1801     1504522002     601     120618     1     142     110125611     120701     HOLD     Y     6/29/2012 11:17:37 AM     6/29/2012 11:17:37 AM
          Better use constraint on specified columns for uniqueness on data. And later update other columns with changing values.

          Edited by: Ashu_Neo on Oct 4, 2012 4:33 PM
          • 2. Re: Output as 2 records.
            803439
            SELECT * FROM 
            (SELECT T.*,RANK() OVER(PARTITION BY COMP_CODE ORDER BY SALES_DATE DESC) RN FROM WORK_INVOICE_HOLD_T T)
            where rn = 1;
            • 3. Re: Output as 2 records.
              894936
              Hi,

              I want the expected output which i mentioned in the post.like below.

              1801     1504522002     601     120618     1     142     110125611     120701     HOLD     Y     6/29/2012 11:17:37 AM     6/29/2012 11:17:37 AM     1     0
              2300     0062090790     121     120306     3     132     110125611     120628     HOLD     Y     6/18/2012 12:47:20 PM     6/18/2012 12:47:20 PM     1     0
              • 4. Re: Output as 2 records.
                894936
                Thanks