Re: Composite Key Using Year from a Date
From: S. Anthony Sequeira <nobody_at_127.0.0.1>
Date: Sat, 23 Apr 2011 00:22:49 +0100
Message-ID: <4db20dca$0$2530$da0feed9_at_news.zen.co.uk>
On 22/04/11 23:16, jimmyb wrote:
> On Apr 22, 1:29 pm, ddf<orat..._at_msn.com> wrote:
> Neither select statement to_date, to_char or trunc worked.
> Really interesting what it take to make a virtual column work with a
> date data type.
> jbrock_dvlpr_at_ddtms_d> create table user_competencies
> 2 ( user_id number
> 3 ,comp_id number
> 4 ,user_score varchar2(1)
> 5 ,supervisor_score varchar2(1)
> 6 ,update_dt date
> 7 ,update_yr date generated always as
> (TO_CHAR(update_dt,'yyyy'))
> 8 )
> 9 /
> Table created.
> jbrock_dvlpr_at_ddtms_d> insert into user_competencies
> (user_id,comp_id,update_dt)
> 2 values(1,1,sysdate);
> 1 row created.
> jbrock_dvlpr_at_ddtms_d> select * from user_competencies;
> select * from user_competencies
> *
> ERROR at line 1:
> ORA-01861: literal does not match format string
> jbrock_dvlpr_at_ddtms_d> drop table user_competencies purge;
> Table dropped.
> Table created.
> jbrock_dvlpr_at_ddtms_d> insert into user_competencies
> (user_id,comp_id,update_dt)
> 2 values(1,1,sysdate);
> 1 row created.
> jbrock_dvlpr_at_ddtms_d> select * from user_competencies;
> select * from user_competencies
> *
> ERROR at line 1:
> ORA-01830: date format picture ends before converting entire input
> string
> jbrock_dvlpr_at_ddtms_d> drop table user_competencies purge ;
> Table dropped.
> jbrock_dvlpr_at_ddtms_d> create table user_competencies
> 2 ( user_id number
> 3 ,comp_id number
> 4 ,user_score varchar2(1)
> 5 ,supervisor_score varchar2(1)
> 6 ,update_dt date
> 7 ,update_yr varchar2(4) generated always as (EXTRACT(year from
> update_dt))
> 8 )
> 9 /
> ,update_yr varchar2(4) generated always as (EXTRACT(year from
> update_dt))
> *
> ERROR at line 7:
> ORA-12899: value too large for column "UPDATE_YR" (actual: 4, maximum:
> 40)
> Huh??? This I don't get.
> 8 )
> 9 /
> Table created.
> jbrock_dvlpr_at_ddtms_d> insert into user_competencies
> (user_id,comp_id,update_dt)
> 2 values(1,1,sysdate);
> 1 row created.
> jbrock_dvlpr_at_ddtms_d> select * from user_competencies;
> jbrock_dvlpr_at_ddtms_d> alter table user_competencies add constraint
> user_competencies_pk primary key(user_id,comp_id,update_yr);
> Table altered.
> Finally, that worked. But I can't make the virtual column a date data
> type and varchar2 must be defined to hold 40k.
>
insert into user_competencies
(user_id, comp_id, user_score, supervisor_score, update_dt) values( 10
select * from user_competencies;
Date: Sat, 23 Apr 2011 00:22:49 +0100
Message-ID: <4db20dca$0$2530$da0feed9_at_news.zen.co.uk>
On 22/04/11 23:16, jimmyb wrote:
> On Apr 22, 1:29 pm, ddf<orat..._at_msn.com> wrote:
>> On Apr 22, 10:21 am, jimmyb<jimmybr..._at_gmail.com> wrote: >> >> >> >> >> >> >> >> >> >>> On Apr 22, 8:12 am, Mark D Powell<Mark.Powe..._at_hp.com> wrote: >> >>>> On Apr 21, 5:55 pm, jimmyb<jimmybr..._at_gmail.com> wrote: >> >>>>> On Apr 21, 1:42 pm, onedbguru<onedbg..._at_yahoo.com> wrote: >> >>>>>> On Apr 21, 1:34 pm, jimmyb<jimmybr..._at_gmail.com> wrote: >> >>>>>>> I need to create a composite primary/unique constraint on a table >>>>>>> based on a year. >> >>>>>>> It is a competency rating table where each user should only have one >>>>>>> record per competency per year. >> >>>>>>> create table user_competencies >>>>>>> ( user_id number >>>>>>> ,comp_id number >>>>>>> ,user_score varchar2(1) >>>>>>> ,supervisor_score varchar2(1) >>>>>>> ,update_dt date >>>>>>> ) >>>>>>> / >> >>>>>>> alter table user_competencies add constraint user_competencies_pk >>>>>>> primary key(user_id, comp_id, extract(year from update_dt)); >> >>>>>>> alter table user_competencies add constraint user_competencies_pk >>>>>>> primary key(user_id, comp_id, extract(year from update_dt)) >> >>>>>>> * >>>>>>> ERROR at line 1: >>>>>>> ORA-00904: : invalid identifier >> >>>>>>> Evidently, I cannot use the Extract function in a constraint. It >>>>>>> there anyway to do this without changing the table design? >> >>>>>> If you are using 11gR2 you can use "generated always as" and then use >>>>>> that column in your constraint. >> >>>>>> create table user_competencies >>>>>> ( user_id number >>>>>> ,comp_id number >>>>>> ,user_score varchar2(1) >>>>>> ,supervisor_score varchar2(1) >>>>>> ,update_dt date >>>>>> ,update_yr date generated always as (TO_DATE(update_dt,'YYYY')) >>>>>> ) >>>>>> / >> >>>>> Thanks, onedbguru! Yes, I am using 11g R2...I should have included >>>>> that in my OP. >> >>>>> I forgot about the virtual column feature of 11g R2. That should work.- Hide quoted text - >> >>>>> - Show quoted text - >> >>>> Interesting solution but I have a couple of thoughts. The solution >>>> may not work if update_dt is the date the record (row) was last >>>> updated rather than the certification period depending when the data >>>> is updated. Depending on what kind of certifications are being >>>> tracked it is possible the certification period may cross calendar >>>> years in which case the table as displayed may need some additional >>>> columns. Neither of these conditions may apply but I thought I would >>>> mention the potential issues. >> >>>> HTH -- Mark D Powell -- >> >>> Thanks for your input Mark. >> >>> Users canupdate the record as often as they need - within the calendar >>> year. Once a calendar year changes they should not be making any >>> updates. The application has a business rule only to display >>> competencies for the current year. >> >>> I have another problem with this solution though. I'm an error when I >>> insert a record. >> >>> create table user_competencies >>> ( user_id number >>> ,comp_id number >>> ,user_score varchar2(1) >>> ,supervisor_score varchar2(1) >>> ,update_dt date >>> ,update_yr date generated always as (TO_DATE(update_dt,'YYYY')) >>> ) >>> / >> >>> alter table user_competencies add constraint user_comp_pk primary >>> key(user_id,comp_id,update_yr); >> >>> insert into user_competencies >>> (user_id, comp_id, user_score, supervisor_score, update_dt) >>> values( 10 >>> , 1 >>> , 'a' >>> , 'a' >>> , sysdate-1 >>> ) ; >> >>> ERROR at line 1: >>> ORA-01830: date format picture ends before converting entire input >>> string- Hide quoted text - >> >>> - Show quoted text - >> >> Why are you using TO_DATE() against a DATE value? It should be >> TO_CHAR, I believe. >> >> David Fitzjarrell >
> Neither select statement to_date, to_char or trunc worked.
>
> Really interesting what it take to make a virtual column work with a
> date data type.
>
> jbrock_dvlpr_at_ddtms_d> create table user_competencies
> 2 ( user_id number
> 3 ,comp_id number
> 4 ,user_score varchar2(1)
> 5 ,supervisor_score varchar2(1)
> 6 ,update_dt date
> 7 ,update_yr date generated always as
> (TO_CHAR(update_dt,'yyyy'))
> 8 )
> 9 /
>
> Table created.
>
> jbrock_dvlpr_at_ddtms_d> insert into user_competencies
> (user_id,comp_id,update_dt)
> 2 values(1,1,sysdate);
>
> 1 row created.
>
> jbrock_dvlpr_at_ddtms_d> select * from user_competencies;
> select * from user_competencies
> *
> ERROR at line 1:
> ORA-01861: literal does not match format string
>
> jbrock_dvlpr_at_ddtms_d> drop table user_competencies purge;
>
> Table dropped.
>
> jbrock_dvlpr_at_ddtms_d> create table user_competencies
> 2 ( user_id number
> 3 ,comp_id number
> 4 ,user_score varchar2(1)
> 5 ,supervisor_score varchar2(1)
> 6 ,update_dt date
> 7 ,update_yr date generated always as
> (TO_DATE(update_dt,'yyyy'))
> 8 )
> 9 /
>
> Table created.
>
> jbrock_dvlpr_at_ddtms_d> insert into user_competencies
> (user_id,comp_id,update_dt)
> 2 values(1,1,sysdate);
>
> 1 row created.
>
> jbrock_dvlpr_at_ddtms_d> select * from user_competencies;
> select * from user_competencies
> *
> ERROR at line 1:
> ORA-01830: date format picture ends before converting entire input
> string
>
> jbrock_dvlpr_at_ddtms_d> drop table user_competencies purge ;
>
> Table dropped.
>
> jbrock_dvlpr_at_ddtms_d> create table user_competencies
> 2 ( user_id number
> 3 ,comp_id number
> 4 ,user_score varchar2(1)
> 5 ,supervisor_score varchar2(1)
> 6 ,update_dt date
> 7 ,update_yr varchar2(4) generated always as (EXTRACT(year from
> update_dt))
> 8 )
> 9 /
> ,update_yr varchar2(4) generated always as (EXTRACT(year from
> update_dt))
> *
> ERROR at line 7:
> ORA-12899: value too large for column "UPDATE_YR" (actual: 4, maximum:
> 40)
>
> Huh??? This I don't get.
>> from update_dt))
> jbrock_dvlpr_at_ddtms_d> create table user_competencies
> 2 ( user_id number
> 3 ,comp_id number
> 4 ,user_score varchar2(1)
> 5 ,supervisor_score varchar2(1)
> 6 ,update_dt date
> 7 ,update_yr varchar2(40) generated always as (EXTRACT(year
> 8 )
> 9 /
>
> Table created.
>
> jbrock_dvlpr_at_ddtms_d> insert into user_competencies
> (user_id,comp_id,update_dt)
> 2 values(1,1,sysdate);
>
> 1 row created.
>
> jbrock_dvlpr_at_ddtms_d> select * from user_competencies;
>
> USER_ID COMP_ID U S UPDATE_DT UPDATE_YR
> ---------- ---------- - - ---------
> ----------------------------------------
> 1 1 22-APR-11 2011
>
> jbrock_dvlpr_at_ddtms_d> alter table user_competencies add constraint
> user_competencies_pk primary key(user_id,comp_id,update_yr);
>
> Table altered.
> >
> Finally, that worked. But I can't make the virtual column a date data
> type and varchar2 must be defined to hold 40k.
>
You can make it a date datatype if you use trunc. This may work with extract, try it.
SQL> drop table user_competencies purge;
create table user_competencies
( user_id number
,comp_id number
,user_score varchar2(1)
,supervisor_score varchar2(1)
,update_dt date
,update_yr date generated always as (trunc(TO_DATE(update_dt),'YYYY')));
alter table user_competencies add constraint user_comp_pk primary
key(user_id,comp_id,update_yr);
insert into user_competencies
(user_id, comp_id, user_score, supervisor_score, update_dt) values( 10
, 1
, 'a'
, 'a'
, sysdate-1
);
select * from user_competencies;
Table dropped.
SQL> 2 3 4 5 6 7
Table created.
SQL> 2
Table altered.
SQL> 2 3 4 5 6 7 8
1 row created.
SQL>
USER_ID COMP_ID U S UPDATE_DT UPDATE_YR
---------- ---------- - - --------- ---------
10 1 a a 22-APR-11 01-JAN-11
SQL>
-- Tony Sequeira ++Received on Fri Apr 22 2011 - 18:22:49 CDT