Forum Stats

  • 3,780,464 Users
  • 2,254,398 Discussions
  • 7,879,339 Comments

Discussions

Validation procedure to check for columns not null in a table if satifies "not null" then no need to

3648470
3648470 Member Posts: 38
edited Mar 1, 2018 5:56AM in SQL & PL/SQL

I have a emp table in which i have a do a validation check after loading data into table.

Emp Table:-

eid int,

ename varchar,

desig varchar,

sal number,

validation_error varchar

for Example after imporing/loading data if i have eid not null, ename not null,  desig not null, sal not null, then it should log the error into the validation error column...

o/p:-

empid,   ename   desig    sal        validation_error

1             -             IT        2000     Ename should not be null
2             -             -         4000      Ename should not be null | desig should not be null
3               -             -          -          Ename should not be null | desig should not be null | sal should not be null
-             -              -          -            Empid should not be null | Ename should not be null | desig should not be null | sal should not be null

As am learning sql on my own, am unable to get it.pls help. thanks.

Tagged:
EtbinGregV

Best Answer

  • Hans Steijntjes
    Hans Steijntjes Member Posts: 614 Bronze Trophy
    edited Feb 28, 2018 4:59AM Accepted Answer

    OK, place the update in a procedure then, as in:

    create procedure validate_emp

    as

    begin

      update emp

      set validation_error = substr(     case when empid is null then ' | EmpId should not be null' else '' end

                                      || case when ename is null then ' | ename should not be null' else '' end

                                      || case when desig is null then ' | desig should not be null' else '' end

                                      || case when sal is null then ' | Sal should not be null' else '' end

                                     ,4

                                      );

    end validate_emp;

    you can then call this procedure from a shell script;

«1

Answers

  • GregV
    GregV Member Posts: 3,075 Gold Crown
    edited Feb 28, 2018 4:21AM

    Hi,

    A NOT NULL constraint is meant to meet this requirement. Just declare one for each column that's not supposed to be null.

  • 3648470
    3648470 Member Posts: 38
    edited Feb 28, 2018 4:31AM

    As i have to import more than 3 million records, i dont want to use not null constraint in the create table statement itself...Dono the datatype and due to requirements, i need to write the proc for this ...Hope you understand.

  • Hans Steijntjes
    Hans Steijntjes Member Posts: 614 Bronze Trophy
    edited Feb 28, 2018 4:48AM

    Try something like:

    update emp

      set validation_error = substr(     case when empid is null then ' | EmpId should not be null' else '' end

                                      || case when ename is null then ' | ename should not be null' else '' end

                                      || case when desig is null then ' | desig should not be null' else '' end

                                      || case when sal is null then ' | Sal should not be null' else '' end

                                     ,4

                                      )

    Instead of storing validation_error in the table, you could also have created a virtual column as in:

    alter table emp add ( validation_error as

        ,substr(    case when empid is null then ' | EmpId should not be null' else '' end

                 || case when ename is null then ' | ename should not be null' else '' end

                 || case when desig is null then ' | desig should not be null' else '' end

                 || case when sal is null then ' | Sal should not be null' else '' end

                ,4

               ) );

  • GregV
    GregV Member Posts: 3,075 Gold Crown
    edited Feb 28, 2018 4:48AM

    Is this requirement for one table only? Suppose you need to do that for many tables with many columns, are you going to hardcode all the IF/CASE statements? Or, would you use a table to setup all the rules? In both cases this is going to be very cumbersome.

    For your requirement, you'll need an UPDATE statement on the whole table, something like:

    UPDATE emp

    SET validation_error = case when empid is null and ename is null and desig is null and sal is null then ...

                                                  when empid is null and ename is null and desig is null then ...

                                                  when empid is null and ename is null then ...

                                                  when empid is null then ..

                                        end

    you see how complicated it is, not even talking about the performance aspect.

  • 3648470
    3648470 Member Posts: 38
    edited Feb 28, 2018 4:54AM

    Hi Hans, direct up date is not correct to my requirement, i also wrote update stmt..

    I need like procedure which can satisfy the requiement. this procedure will be automated using shell script after validating the data. Thanks.

  • Hans Steijntjes
    Hans Steijntjes Member Posts: 614 Bronze Trophy
    edited Feb 28, 2018 4:59AM Accepted Answer

    OK, place the update in a procedure then, as in:

    create procedure validate_emp

    as

    begin

      update emp

      set validation_error = substr(     case when empid is null then ' | EmpId should not be null' else '' end

                                      || case when ename is null then ' | ename should not be null' else '' end

                                      || case when desig is null then ' | desig should not be null' else '' end

                                      || case when sal is null then ' | Sal should not be null' else '' end

                                     ,4

                                      );

    end validate_emp;

    you can then call this procedure from a shell script;

  • 3648470
    3648470 Member Posts: 38
    edited Feb 28, 2018 5:53AM

    Hi Cookiemonster76,

    am using 10g version, moreover using virutal column, it will reject records during the import.

    My reqmt  is for multiple tables ....update statement will take more time as we have some millions of records.

    In one short of procedure i need to update multiple tables having "not null" to be captured in the validation_error column which is there in multiple tables(around 45 tables). Please assist.

    procedure should take 3 parameters as  table_name, column_name, not null then it should give the required output. Thanks.

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410
    edited Feb 28, 2018 5:33AM

    If you're on 11g or higher then you could just use a virtual column:

    SQL> create table Emp (eid number,  2                    ename varchar(100),  3                    desig varchar(100),  4                    sal number,  5                    validation_error varchar(1000) GENERATED ALWAYS AS (rtrim((CASE WHEN eid IS NULL THEN 'Eid Should not be null | ' END  6                                                                              ||CASE WHEN ename IS NULL THEN 'ename Should not be null | ' END  7                                                                              ||CASE WHEN desig IS NULL THEN 'desig Should not be null | ' END  8                                                                              ||CASE WHEN sal IS NULL THEN 'sal Should not be null | ' END  9                                                                              )10                                                                              , ' | '11                                                                              )12                                                                        ) VIRTUAL13                  );Table createdSQL> insert into emp (eid, ename, desig, sal) values (1, null, null, 2);1 row insertedSQL> insert into emp (eid, ename, desig, sal) values (null, 'a name', 'a desig', null);1 row insertedSQL> insert into emp (eid, ename, desig, sal) values (3, null, null, null);1 row insertedSQL> select * from emp;      EID ENAME      DESIG            SAL VALIDATION_ERROR---------- ---------- ---------- ---------- --------------------------------------------------------------------------------        1                                2 ename Should not be null | desig Should not be null          a name    a desig              Eid Should not be null | sal Should not be null        3                                  ename Should not be null | desig Should not be null | sal Should not be nullSQL>

    Anytime you need to work something out that depends solely on the contents of the current row you can use a virtual column.

  • Hans Steijntjes
    Hans Steijntjes Member Posts: 614 Bronze Trophy
    edited Feb 28, 2018 6:14AM

    You could consider using triggers, as in:

    create or replace trigger emp_validation_trg

    before insert or update

    on emp

    for each row

    begin

      :NEW.validation_error := SUBSTR(case when :NEW.empid is null then ' | EmpId should not be null' else '' end

                                      || case when :NEW.ename is null then ' | ename should not be null' else '' end

                                      || case when :NEW.desig is null then ' | desig should not be null' else '' end

                                      || case when :NEW.sal is null then ' | Sal should not be null' else '' end

                                    ,4

                                      ) ;

    end;

    The value for the field will then be determined during the load process.

  • 3648470
    3648470 Member Posts: 38
    edited Feb 28, 2018 7:07AM

    My reqmt  is for multiple tables ....

    In one short of procedure i need to update multiple tables having "not null" to be captured in the validation_error column which is there in multiple tables(around 45 tables).

    Trying from my side also, but unable to get the o/p...

    Am looking for procedure which should take 3 parameters as  table_name, column_name, not null then it should give the required output. Thanks Hans.

This discussion has been closed.