Re: Check for no more than two entries of each value in a column?

From: ddf <oratune_at_msn.com>
Date: Fri, 17 Apr 2009 11:46:16 -0700 (PDT)
Message-ID: <60ebcd57-c137-4260-aabc-ebe29de0d2d8_at_n4g2000vba.googlegroups.com>



On Apr 17, 1:37 pm, ddf <orat..._at_msn.com> wrote:
> On Apr 17, 12:38 pm, livefreeordie <jpittm..._at_gmail.com> wrote:
>
>
>
>
>
> > Hi,
>
> > I need to create an app where any given employee may nominate up to
> > TWO other employees per year for an award.  This number could change.
>
> > Now I could create a table like this:
>
> > PK, U1  nominator         (varchar2(12))
> > PK, U1  year                 (int)
> > U1        nomination1_id  (int)
> > U1        nomination2_id  (int)
>
> > Then another table:
> > PK   nomination_id    (int)
> >         nominee           (varchar2(12))
> >         nomination_data   ....
>
> > However, this would force me to update any Views or Code every time
> > the number of allowed nominations changes.
>
> > I'd much rather do this:
>
> > PK  nomination_id   (int)
> >        year                 (int)
> >        nominator         (varchar2(12))
> >        nominee           (varchar2(12))
> >        nomination_data ...
>
> > And have some kind of Check Constraint to only allow two nominees per
> > nominator per year.  But I don't know how to do that.
>
> > Thoughts?
>
> > ~ Jamie
>
> Yes, you don't need or want a check constraint, you want a stored
> procedure called from an application to do the processing:
>
> SQL> create table nominations(
>   2          nomination_id   number,
>   3          year  number,
>   4          nominator         varchar2(12),
>   5          nominee           varchar2(12),
>   6          nomination_data   varchar2(255)
>   7  );
>
> Table created.
>
> SQL>
> SQL> create sequence nomseq
>   2  start with 1 increment by 1 nomaxvalue nocycle nocache order;
>
> Sequence created.
>
> SQL>
> SQL> create or replace trigger pop_nom_id
>   2  before insert on nominations
>   3  for each row
>   4  begin
>   5          select nomseq.nextval
>   6          into :new.nomination_id
>   7          from dual;
>   8  end;
>   9  /
>
> Trigger created.
>
> SQL>
> SQL> create or replace procedure nominate_emp(
>   2          p_nominee varchar2,
>   3          p_nominator varchar2,
>   4          p_reason  varchar2
>   5  )
>   6  as
>   7
>   8          nom_ct number:=0;
>   9          too_many_noms exception;
>  10          pragma exception_init(too_many_noms, -20009);
>  11
>  12  begin
>  13          select count(*)
>  14          into nom_ct
>  15          from nominations
>  16          where nominator = p_nominator;
>  17
>  18          if nom_ct < 2 then
>  19                  insert into nominations
>  20                  (year, nominator, nominee, nomination_data)
>  21                  values
>  22                  (to_number(to_char(sysdate, 'RRRR')),
>  23                          p_nominator,
>  24                          p_nominee,
>  25                          p_reason);
>  26
>  27                  commit;
>  28
>  29          else
>  30                  raise too_many_noms;
>  31          end if;
>  32
>  33  exception
>  34          when too_many_noms then
>  35                  raise_application_error(-20009, 'Too many nominations for
> user '||p_nominator);
>  36
>  37          when others then
>  38                  -- Output desired error message
>  39                  dbms_output.put_line('-20999: Sumthin'' bad happened --
> error stack follows');
>  40                  -- Output actual line number of error source
>  41                  dbms_output.put(dbms_utility.format_error_backtrace);
>  42                  -- Output the actual error number and message
>  43                  dbms_output.put_line(dbms_utility.format_error_stack);
>  44
>  45  end;
>  46  /
>
> Procedure created.
>
> SQL>
> SQL> show errors
> No errors.
> SQL>
> SQL> exec nominate_emp(user, user, 'Because I am worth it')
>
> PL/SQL procedure successfully completed.
>
> SQL> exec nominate_emp('LARDBUCKET', user, 'Because he is worth it')
>
> PL/SQL procedure successfully completed.
>
> SQL> exec nominate_emp('ZING', user, 'Because')
> BEGIN nominate_emp('ZING', user, 'Because'); END;
>
> *
> ERROR at line 1:
> ORA-20009: Too many nominations for user BING
> ORA-06512: at "BING.NOMINATE_EMP", line 35
> ORA-06512: at line 1
>
> SQL>
> SQL> select
>   2  nomination_id,
>   3  year,
>   4  nominator,
>   5  nominee,
>   6  nomination_data
>   7  from nominations;
>
> NOMINATION_ID       YEAR NOMINATOR    NOMINEE      NOMINATION_DATA
> ------------- ---------- ------------ ------------
> --------------------------
>             1       2009 BING         BING         Because I am worth
> it
>             2       2009 BING         LARDBUCKET   Because he is worth
> it
>
> SQL>
>
> With the commit inside the procedure you *should* be able to keep to
> the two nominations maximum per nominator (of course depending upon
> how active this nomination process can be with multiple users you may
> have a few slip through the cracks).
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

Gee, I forgot the year in the count(*) calculation. :( Here's a corrected copy:

SQL> create table nominations(

  2  	     nomination_id   number,
  3  	     year  number,
  4  	     nominator	       varchar2(12),
  5  	     nominee	       varchar2(12),
  6  	     nomination_data   varchar2(255)
  7 );

Table created.

SQL>
SQL> create sequence nomseq
  2 start with 1 increment by 1 nomaxvalue nocycle nocache order;

Sequence created.

SQL>
SQL> create or replace trigger pop_nom_id   2 before insert on nominations
  3 for each row
  4 begin

  5  	     select nomseq.nextval
  6  	     into :new.nomination_id
  7  	     from dual;

  8 end;
  9 /

Trigger created.

SQL>
SQL> insert into nominations
  2 (year, nominator, nominee, nomination_data)   3 values
  4 (to_number(to_char(sysdate, 'RRRR')) -2,   5 user, 'AL', 'He''s cool');

1 row created.

SQL>
SQL> insert into nominations
  2 (year, nominator, nominee, nomination_data)   3 values
  4 (to_number(to_char(sysdate, 'RRRR')) -2,   5 user, 'WANDA', 'She''s cool');

1 row created.

SQL>
SQL> insert into nominations
  2 (year, nominator, nominee, nomination_data)   3 values
  4 (to_number(to_char(sysdate, 'RRRR')) -1,   5 user, 'AL', 'He''s cool');

1 row created.

SQL>
SQL> insert into nominations
  2 (year, nominator, nominee, nomination_data)   3 values
  4 (to_number(to_char(sysdate, 'RRRR')) -1,   5 user, 'WANDA', 'She''s cool');

1 row created.

SQL>
SQL> create or replace procedure nominate_emp(

  2  	     p_nominee varchar2,
  3  	     p_nominator varchar2,
  4  	     p_reason  varchar2

  5 )
  6 as
  7
  8  	     nom_ct number:=0;
  9  	     too_many_noms exception;
 10  	     pragma exception_init(too_many_noms, -20009);
 11
 12 begin
 13  	     select count(*)
 14  	     into nom_ct
 15  	     from nominations
 16  	     where nominator = p_nominator
 17  	     and year = to_number(to_char(sysdate, 'RRRR'));
 18
 19
 20  	     if nom_ct < 2 then
 21  		     insert into nominations
 22  		     (year, nominator, nominee, nomination_data)
 23  		     values
 24  		     (to_number(to_char(sysdate, 'RRRR')),
 25  			     p_nominator,
 26  			     p_nominee,
 27  			     p_reason);
 28
 29  		     commit;
 30
 31  	     else
 32  		     raise too_many_noms;
 33  	     end if;

 34
 35 exception
 36  	     when too_many_noms then
 37  		     raise_application_error(-20009, 'Too many nominations for
user '||p_nominator);
 38
 39  	     when others then
 40  		     -- Output desired error message
 41  		     dbms_output.put_line('-20999: Sumthin'' bad happened --
error stack follows');
 42  		     -- Output actual line number of error source
 43  		     dbms_output.put(dbms_utility.format_error_backtrace);
 44  		     -- Output the actual error number and message
 45  		     dbms_output.put_line(dbms_utility.format_error_stack);
 46
 47 end;
 48 /

Procedure created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> exec nominate_emp(user, user, 'Because I am worth it')

PL/SQL procedure successfully completed.

SQL> exec dbms_lock.sleep(30)

PL/SQL procedure successfully completed.

SQL> exec nominate_emp('LARDBUCKET', user, 'Because he is worth it')

PL/SQL procedure successfully completed.

SQL> exec dbms_lock.sleep(30)

PL/SQL procedure successfully completed.

SQL> exec nominate_emp('ZING', user, 'Because') BEGIN nominate_emp('ZING', user, 'Because'); END;

*
ERROR at line 1:

ORA-20009: Too many nominations for user BING
ORA-06512: at "BING.NOMINATE_EMP", line 37
ORA-06512: at line 1


SQL>
SQL> select
  2 nomination_id,
  3 year,
  4 nominator,
  5 nominee,
  6 nomination_data
  7 from nominations;

NOMINATION_ID YEAR NOMINATOR NOMINEE NOMINATION_DATA ------------- ---------- ------------ ------------


            1       2007 BING         AL           He's cool
            2       2007 BING         WANDA        She's cool
            3       2008 BING         AL           He's cool
            4       2008 BING         WANDA        She's cool
            5       2009 BING         BING         Because I am worth
it
            6       2009 BING         LARDBUCKET   Because he is worth
it

6 rows selected.

SQL> David Fitzjarrell Received on Fri Apr 17 2009 - 13:46:16 CDT

Original text of this message