Skip to Main Content
  • Questions

  • User entered date in the form to validation from another table before cursor moves to next field

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, javed.

Asked: July 18, 2016 - 6:20 am UTC

Last updated: July 20, 2016 - 9:15 am UTC

Version: 9i

Viewed 1000+ times

You Asked

Sir
Hope you are doing well

I have query, please suggest me solution

I have two different tables

Holidays (Holiday_ID,From_Date,To_Date)
Emp_Attendance(Date,Emp_No,Attendance_Status)

I would like that when user enter the date in the Emp_Attendance, it should be validate that the entered date is not exit in the Holidays table. If exist then display message "This is holiday, please select another date" otherwise cursor moves to Emp_No field

Please suggest me solution Thanks
Javed

and Chris said...

Another use case for assertions!

It's tricky to do this currently. One way is to create a fast refresh, on commit materialized view that validates your rule.

This has a query that ensure there are no emp_attendance rows with values between the start and end of the holidays. To enforce this in the database, add a check constraint ensuring that the count is always zero:

SQL> create table Holidays (
  2    Holiday_ID int,
  3    Date_from date,
  4    Date_to   date
  5  );

Table created.

SQL> create table Emp_Attendance(
  2    att_Date date,Emp_No int,Attendance_Status int
  3  );

Table created.

SQL> create materialized view log on Emp_Attendance
  2    with rowid, sequence (att_date) including new values;

Materialized view log created.

SQL> create materialized view log on holidays
  2    with rowid, sequence (date_from, date_to) including new values;

Materialized view log created.

SQL>
SQL> drop materialized view mv;

Materialized view dropped.

SQL>
SQL> create materialized view mv
  2  refresh fast on commit
  3  as
  4    select count(*) c
  5    from   emp_attendance e
  6    join   holidays h
  7    on     e.att_date between h.date_from and h.date_to;

Materialized view created.

SQL>
SQL> alter table mv add constraint mv_zero_c check (c = 0);

Table altered.

SQL>
SQL> insert into holidays values (1, date'2015-12-25', date'2016-01-01');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into emp_attendance values (date'2015-12-24', 1, 1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into emp_attendance values (date'2015-12-26', 1, 1);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (CHRIS.MV_ZERO_C) violated


SQL> insert into emp_attendance values (date'2016-01-01', 1, 1);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (CHRIS.MV_ZERO_C) violated


SQL> insert into emp_attendance values (date'2016-01-02', 1, 1);

1 row created.

SQL> commit;

Commit complete.

You can then catch the exception in your code and display the holiday message.

But this is convoluted. And only one process at a time can refresh the MV. So this could become a bottleneck.

So we have a proposal to include SQL assertions in Oracle Database. These enable you to build constraints across tables, like your request.

If you'd like to see this, click the link below to vote for it:

https://community.oracle.com/ideas/13028

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

And in the future...

Toon Koppelaars, July 20, 2016 - 6:23 am UTC

Just for the record, maybe sometime you can avoid all code above and just do:

create assertion attend_outside_holidays as check
(not exists
  (select 'x'
     from holidays h
    where exists
           (select 'x'
              from emp_attendance ea
             where ea.att_date between h.date_from and h.date_t)
  )
)

Connor McDonald
July 20, 2016 - 7:13 am UTC

I hope we can! :)

Suggest

Ghassan, July 20, 2016 - 8:19 am UTC

Assume a work table or MV HolidayDays that contains simply the distinct values of the original holiday table ie single pk column say hday.
Then find a way to use a deffered constraint or a trigger on the other table and manage code in order to avoid insertion or flag rows so we could meet the need?
Is this approch reliable? As its a single fetch on pk.
Chris Saxon
July 20, 2016 - 9:15 am UTC

Trigger based solutions are generally either:

- Incorrect
- Slow
- Both!

This is because they have to account for concurrent actions (user 1 adds a holiday row while at the same time user 2 adds the same attendance row). Getting this correct is hard.

You could validate this with a foreign key. To do so you need to:

- Have a table of dates listing every date. Date is the PK
- A flag on this table saying if it's a holiday or not
- A unique constraint on (date, holiday)
- Attendance can then also have a holiday flag which must be N
- A foreign key from attendance to the unique constraint on dates:

SQL> create table dates (
  2    calendar_date date primary key
  3      check (calendar_date = trunc(calendar_date)),
  4    is_holiday    varchar2(1) not null
  5      check (is_holiday in ('Y', 'N')),
  6    unique (calendar_date, is_holiday)
  7  );

Table created.

SQL>
SQL> create table attendance (
  2    calendar_date date not null,
  3    is_holiday    varchar2(1) not null
  4      check (is_holiday = 'N'),
  5    foreign key (calendar_date, is_holiday)
  6      references dates (calendar_date, is_holiday)
  7  );

Table created.

SQL>
SQL> insert into dates values (date'2016-01-01', 'Y');

1 row created.

SQL> insert into dates values (date'2016-01-02', 'N');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into attendance values (date'2016-01-01','Y');
insert into attendance values (date'2016-01-01','Y')
*
ERROR at line 1:
ORA-02290: check constraint (CHRIS.SYS_C0016315) violated


SQL> insert into attendance values (date'2016-01-01','N');
insert into attendance values (date'2016-01-01','N')
*
ERROR at line 1:
ORA-02291: integrity constraint (CHRIS.SYS_C0016316) violated - parent key not found


SQL> insert into attendance values (date'2016-01-02','N');

1 row created.

This is also fiddly though.

An assertion as Toon showed is the best way to do this. Unfortunately we don't have them yet...
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.