2 Replies Latest reply: Oct 7, 2012 10:05 AM by APC RSS

    How can i achive this result by writing SQL Query ?

    Asked to Learn
      Dear Exparts,
      Here is my banner
      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
      PL/SQL Release 10.2.0.3.0 - Production
      "CORE     10.2.0.3.0     Production"
      TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
      NLSRTL Version 10.2.0.3.0 - Production
      I have two table with relation. Here is the scripts of create table and insert data.

      TABLE
      CREATE TABLE TAB1 
      (
        T1_ID NUMBER(5) 
      , T1_NAME VARCHAR2(100) 
      );
      
      ALTER TABLE TAB1  
      MODIFY (T1_ID NOT NULL);
      
      ALTER TABLE TAB1
      ADD CONSTRAINT TAB1_PK PRIMARY KEY 
      (
        T1_ID 
      )
      ENABLE;
      
      
      CREATE TABLE TAB2 
      (
        T2_ID NUMBER(5) NOT NULL 
      , T2_T1_ID NUMBER(5) 
      , T2_RV_NO VARCHAR2(20) 
      , T2_RV_DATE DATE 
      , T2_F1 VARCHAR2(20) 
      , T2_F2 VARCHAR2(20) 
      , T2_F3 VARCHAR2(20) 
      , CONSTRAINT TAB2_PK PRIMARY KEY 
        (
          T2_ID 
        )
        ENABLE 
      );
      ALTER TABLE TAB2
      ADD CONSTRAINT TAB2_TAB1_FK1 FOREIGN KEY
      (
        T2_T1_ID 
      )
      REFERENCES TAB1
      (
        T1_ID 
      )
      ENABLE;
      INSERT DATA
      Insert into TAB1 (T1_ID,T1_NAME) values (1,'Test 1');
      Insert into TAB1 (T1_ID,T1_NAME) values (2,'Test 2');
      
      
      Insert into TAB2 (T2_ID,T2_T1_ID,T2_RV_NO,T2_RV_DATE,T2_F1,T2_F2,T2_F3) values (1,1,'00',to_date('01-OCT-2012','DD-MON-RRRR'),'Probation','05','Yes');
      Insert into TAB2 (T2_ID,T2_T1_ID,T2_RV_NO,T2_RV_DATE,T2_F1,T2_F2,T2_F3) values (2,1,'01',to_date('06-OCT-2012','DD-MON-RRRR'),'Confirm','06','Yes');
      Insert into TAB2 (T2_ID,T2_T1_ID,T2_RV_NO,T2_RV_DATE,T2_F1,T2_F2,T2_F3) values (3,2,'00',to_date('02-OCT-2012','DD-MON-RRRR'),'Probation','07','No');
      Insert into TAB2 (T2_ID,T2_T1_ID,T2_RV_NO,T2_RV_DATE,T2_F1,T2_F2,T2_F3) values (4,2,'01',to_date('09-OCT-2012','DD-MON-RRRR'),'Probation','07','Yes');
      Insert into TAB2 (T2_ID,T2_T1_ID,T2_RV_NO,T2_RV_DATE,T2_F1,T2_F2,T2_F3) values (5,1,'02',to_date('10-OCT-2012','DD-MON-RRRR'),'Confirm','06','No');
      Now i want to produce the following reports..
      Period:      06-OCT-2012 to 09-OCT-2012
      T2_RV_DATE     T1_NAME          T2_RV_NO     Revised_Column     Previous_Value     Revised_value
      06-OCT-2012     Test 1          01          T2_F1          Probation     Confirm
                                    T2_F2          05          06
      09-OCT-2012     Test 2          01          T2_F3          No          Yes
      I just achieve Revised_Column     Previous_Value     Revised_value for a particular name.

      Here is my code
      SELECT T2_RV_NO,T2_RV_DATE,T1_NAME,'T2_F1' Revised_Column,PREVIOUS_VALUE,REVISED_VALUE
      FROM(
      SELECT T2_RV_NO,T2_RV_DATE,T1_NAME,'T2_F1',
      LAG(T2_F1) OVER (ORDER BY T2_RV_DATE) PREVIOUS_VALUE ,T2_F1 REVISED_VALUE
      FROM TAB2,TAB1
      WHERE TAB1.T1_ID=TAB2.T2_T1_ID
      AND TAB1.T1_ID=:EMP_ID)
      WHERE NVL(PREVIOUS_VALUE,'null')<>NVL(REVISED_VALUE,'null')
      AND T2_RV_DATE BETWEEN :BEGIN_DATE AND :END_DATE
      AND T2_RV_NO<>'00'
      UNION ALL
      SELECT T2_RV_NO,T2_RV_DATE,T1_NAME,'T2_F2' Revised_Column,PREVIOUS_VALUE,REVISED_VALUE
      FROM(
      SELECT T2_RV_NO,T2_RV_DATE,T1_NAME,'T2_F2',
      LAG(T2_F2) OVER (ORDER BY T2_RV_DATE) PREVIOUS_VALUE ,T2_F2 REVISED_VALUE
      FROM TAB2,TAB1
      WHERE TAB1.T1_ID=TAB2.T2_T1_ID
      AND TAB1.T1_ID=:EMP_ID)
      WHERE NVL(PREVIOUS_VALUE,'null')<>NVL(REVISED_VALUE,'null')
      AND T2_RV_DATE BETWEEN :BEGIN_DATE AND :END_DATE
      AND T2_RV_NO<>'00'
      UNION ALL
      SELECT T2_RV_NO,T2_RV_DATE,T1_NAME,'T2_F3' Revised_Column,PREVIOUS_VALUE,REVISED_VALUE
      FROM(
      SELECT T2_RV_NO,T2_RV_DATE,T1_NAME,'T2_F3',
      LAG(T2_F3) OVER (ORDER BY T2_RV_DATE) PREVIOUS_VALUE ,T2_F3 REVISED_VALUE
      FROM TAB2,TAB1
      WHERE TAB1.T1_ID=TAB2.T2_T1_ID
      AND TAB1.T1_ID=:EMP_ID)
      WHERE NVL(PREVIOUS_VALUE,'null')<>NVL(REVISED_VALUE,'null')
      AND T2_RV_DATE BETWEEN :BEGIN_DATE AND :END_DATE
      AND T2_RV_NO<>'00'
      but it's not work for multiple employee id

      Thanks in advance

      Edited by: Asked to Learn on Oct 6, 2012 9:18 PM