5 Replies Latest reply on Nov 22, 2012 11:09 AM by user12957777

    validation

    user12957777
      I have table as follows:
      TABLE1
      ----------
      
      EMP       EMPLOYER   DEPT       DATE           IN_OUT   SR     TIME
      AAA       XXX            ADMIN     01-11-2012     IN           1       01-11-2012 02:08:13 PM
      BBB       YYY            ADMIN     01-11-2012     IN           1       01-11-2012 02:09:13 PM  
      
      I want to write a validation to restrict the second IN entry in table for the employee before the OUT transaction happens.
      
      Note:-   SR is inserted for every combination of DATE and IN_OUT i.e  for DATE 01-11-2012 and IN_OUT as IN 1 is inserted. 
                2 will get inserted for the same DATE and IN_OUT combination. Same in case of OUT operation.
      
      
      Please guide to write the validation and raise error.
      
      Sanjay
        • 1. Re: validation
          Paul  Horth
          user12957777 wrote:
          I have table as follows:
          TABLE1
          ----------
          
          EMP       EMPLOYER   DEPT       DATE           IN_OUT   SR     TIME
          AAA       XXX            ADMIN     01-11-2012     IN           1       01-11-2012 02:08:13 PM
          BBB       YYY            ADMIN     01-11-2012     IN           1       01-11-2012 02:09:13 PM  
          
          I want to write a validation to restrict the second IN entry in table for the employee before the OUT transaction happens.
          
          Note:-   SR is inserted for every combination of DATE and IN_OUT i.e  for DATE 01-11-2012 and IN_OUT as IN 1 is inserted. 
          2 will get inserted for the same DATE and IN_OUT combination. Same in case of OUT operation.
          
          
          Please guide to write the validation and raise error.
          
          Sanjay
          Please read {message:id=9360002} and follow the advice.

          In particular post create table statements, test data inserts and the output that you expect.
          • 2. Re: validation
            user12957777
            CREATE TABLE TABLE1 
            (
              EMP         VARCHAR2(10 BYTE),
              EMPLOYER    VARCHAR2(10 BYTE),
              DEPT        VARCHAR2(10 BYTE),
              DATE1       DATE,
              IN_OUT      VARCHAR2(10 BYTE),
              SR          NUMBER,
              TIME1        DATE
            )
            
            
            
            INSERT INTO TABLE1 VALUES('AAA','XXX','ADMIN',01-11-2012,'IN',1,01/11/2012 02:08:13 PM);
            INSERT INTO TABLE1 VALUES('BBB','YYY','ADMIN',01-11-2012,'IN',1,01/11/2012 02:09:13 PM);
            • 3. Re: validation
              AlbertoFaenza
              Hi,

              What about creating a UNIQUE index on EMP, DATE1, IN_OUT columns?

              The second insert for the same employee, same date, same operation will fail.

              i.e.:
              SQL> DROP TABLE  TABLE1
              Table dropped.
              SQL> CREATE TABLE TABLE1 
              (
                EMP         VARCHAR2(10 BYTE),
                EMPLOYER    VARCHAR2(10 BYTE),
                DEPT        VARCHAR2(10 BYTE),
                DATE1       DATE,
                IN_OUT      VARCHAR2(10 BYTE),
                SR          NUMBER,
                TIME1       DATE
              )
              Table created.
              SQL> CREATE UNIQUE INDEX INDEX1 ON TABLE1 (EMP, DATE1, IN_OUT)
              Index created.
              SQL> INSERT INTO TABLE1 VALUES ('AAA', 'XXX', 'ADMIN', TO_DATE ('01-11-2012', 'DD-MM-YYYY'), 'IN', 1, TO_DATE ('01/11/2012 02:08:13 PM', 'DD/MM/YYYY HH:MI:SS AM'))
              1 row created.
              SQL> INSERT INTO TABLE1 VALUES('BBB', 'YYY', 'ADMIN', TO_DATE('01-11-2012', 'DD-MM-YYYY'),'IN', 1, TO_DATE('01/11/2012 02:09:13 PM', 'DD/MM/YYYY HH:MI:SS AM'))
              1 row created.
              SQL> INSERT INTO TABLE1 VALUES ('AAA', 'XXX', 'ADMIN', TO_DATE ('01-11-2012', 'DD-MM-YYYY'), 'IN', 1, TO_DATE ('01/11/2012 02:09:13 PM', 'DD/MM/YYYY HH:MI:SS AM'))
              INSERT INTO TABLE1 VALUES ('AAA', 'XXX', 'ADMIN', TO_DATE ('01-11-2012', 'DD-MM-YYYY'), 'IN', 1, TO_DATE ('01/11/2012 02:09:13 PM', 'DD/MM/YYYY HH:MI:SS AM'))
              Error at line 23
              ORA-00001: unique constraint (SYSADM.INDEX1) violated
              Script Terminated on line 23.
              Regards.
              Al

              Edited by: Alberto Faenza on Nov 22, 2012 11:57 AM
              • 4. Re: validation
                stefan nebesnak
                2 will get inserted for the same DATE and IN_OUT combination
                is value for column SR automatically updated when new row is inserted? You can use trigger for that.

                http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm
                • 5. Re: validation
                  user12957777
                  Yes the value comes from trigger in SR.