24 August 2011

Business Rule: Only One per Day, but keep the time

The business rule states:

Only one entry is allowed per ID and per day and the time should be recorded.

The table involved (simplified for the blog post)

SQL> create table test
2 (id number
3 ,inspection_dt date
4 );

Wouldn't it be nice if it was possible to do it like this?

SQL> create table test
2 (id number
3 ,inspection_dt date
4 ,constraint one_per_day unique (id, trunc (inspection_dt))
5 );
,constraint one_per_day unique (id, trunc (inspection_dt))
ERROR at line 4:
ORA-00904: : invalid identifier

This way you still have the complete date information (time is a component of the date column), and only use the TRUNC (inspection_dt) to constrain the data entry.
As you can tell from the error message, this is not allowed.
Oracle 11g Release 1 introduced Virtual Columns which can implement this requirement declaratively.

08 August 2011

Splitting a comma delimited string the RegExp way, Part Two

Over two years ago I wrote about a way to split a comma delimited string using Regular Expresssions. Just a little while ago someone asked how to split it when you have more records involved than just one (as I used in my example).