Composite Key Using Year from a Date

On Apr 21, 1:34*pm, jimmyb 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'))
)
/

On Apr 21, 1:42*pm, onedbguru wrote:
> On Apr 21, 1:34*pm, jimmyb 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.

On Apr 21, 5:55*pm, jimmyb wrote:
> On Apr 21, 1:42*pm, onedbguru wrote:
>
>
>
>
>
>
>
>
>
> > On Apr 21, 1:34*pm, jimmyb 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.


I have also used this VC as my partition key using INTERVAL
partitions. If you are need to use partitioning and are not using I-
P, you are causing yourself a whole lot of work by having to ensure
that someone - or some process ran the "add partition" scripts. I
have seen interval partitions at a ridiculously fine interval.

On Apr 21, 5:55*pm, jimmyb wrote:
> On Apr 21, 1:42*pm, onedbguru wrote:
>
>
>
>
>
> > On Apr 21, 1:34*pm, jimmyb 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 --

On Apr 22, 8:12*am, Mark D Powell wrote:
> On Apr 21, 5:55*pm, jimmyb wrote:
>
>
>
>
>
>
>
>
>
> > On Apr 21, 1:42*pm, onedbguru wrote:
>
> > > On Apr 21, 1:34*pm, jimmyb 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

On 22/04/11 18:21, jimmyb wrote:
> 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

Try

select to_date( sysdate,'yyyy') from dual;

and then try

select trunc(sysdate,'yyyy') from dual;

--
Tony Sequeira
++

On Apr 22, 10:21*am, jimmyb wrote:
> On Apr 22, 8:12*am, Mark D Powell wrote:
>
>
>
>
>
> > On Apr 21, 5:55*pm, jimmyb wrote:
>
> > > On Apr 21, 1:42*pm, onedbguru wrote:
>
> > > > On Apr 21, 1:34*pm, jimmyb 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

On Apr 22, 1:29*pm, ddf wrote:
> On Apr 22, 10:21*am, jimmyb wrote:
>
>
>
>
>
>
>
>
>
> > On Apr 22, 8:12*am, Mark D Powell wrote:
>
> > > On Apr 21, 5:55*pm, jimmyb wrote:
>
> > > > On Apr 21, 1:42*pm, onedbguru wrote:
>
> > > > > On Apr 21, 1:34*pm, jimmyb 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 thenuse
> > > > > 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@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@ddtms_d> insert into user_competencies
(user_id,comp_id,update_dt)
2 values(1,1,sysdate);

1 row created.

jbrock_dvlpr@ddtms_d> select * from user_competencies;
select * from user_competencies
*
ERROR at line 1:
ORA-01861: literal does not match format string

jbrock_dvlpr@ddtms_d> drop table user_competencies purge;

Table dropped.

jbrock_dvlpr@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@ddtms_d> insert into user_competencies
(user_id,comp_id,update_dt)
2 values(1,1,sysdate);

1 row created.

jbrock_dvlpr@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@ddtms_d> drop table user_competencies purge ;

Table dropped.

jbrock_dvlpr@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.

jbrock_dvlpr@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
from update_dt))
8 )
9 /

Table created.

jbrock_dvlpr@ddtms_d> insert into user_competencies
(user_id,comp_id,update_dt)
2 values(1,1,sysdate);

1 row created.

jbrock_dvlpr@ddtms_d> select * from user_competencies;

USER_ID COMP_ID U S UPDATE_DT UPDATE_YR
---------- ---------- - - ---------
----------------------------------------
1 1 22-APR-11 2011

jbrock_dvlpr@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.

On 22/04/11 23:16, jimmyb wrote:
> On Apr 22, 1:29 pm, ddf wrote:
>> On Apr 22, 10:21 am, jimmyb wrote:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>> On Apr 22, 8:12 am, Mark D Powell wrote:
>>
>>>> On Apr 21, 5:55 pm, jimmyb wrote:
>>
>>>>> On Apr 21, 1:42 pm, onedbguru wrote:
>>
>>>>>> On Apr 21, 1:34 pm, jimmyb 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@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@ddtms_d> insert into user_competencies
> (user_id,comp_id,update_dt)
> 2 values(1,1,sysdate);
>
> 1 row created.
>
> jbrock_dvlpr@ddtms_d> select * from user_competencies;
> select * from user_competencies
> *
> ERROR at line 1:
> ORA-01861: literal does not match format string
>
> jbrock_dvlpr@ddtms_d> drop table user_competencies purge;
>
> Table dropped.
>
> jbrock_dvlpr@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@ddtms_d> insert into user_competencies
> (user_id,comp_id,update_dt)
> 2 values(1,1,sysdate);
>
> 1 row created.
>
> jbrock_dvlpr@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@ddtms_d> drop table user_competencies purge ;
>
> Table dropped.
>
> jbrock_dvlpr@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.
>
> jbrock_dvlpr@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
> from update_dt))
> 8 )
> 9 /
>
> Table created.
>
> jbrock_dvlpr@ddtms_d> insert into user_competencies
> (user_id,comp_id,update_dt)
> 2 values(1,1,sysdate);
>
> 1 row created.
>
> jbrock_dvlpr@ddtms_d> select * from user_competencies;
>
> USER_ID COMP_ID U S UPDATE_DT UPDATE_YR
> ---------- ---------- - - ---------
> ----------------------------------------
> 1 1 22-APR-11 2011
>
> jbrock_dvlpr@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
++