Is there a column-data-type restricted to truncated date format? [Oracle Help]

Prev: Oracle Materialized Views in a RAC
Next: Video Resource: R12 Inventory Management, New Features

From: Norbert Winkler on 12 Apr 2010 06:22

Hi,

for creating a unique index with a date-column A_DATE I'm using a
calculated column X_DATE as (trunc(A_DATE)):

CREATE UNIQUE INDEX UX_A_TABLE
ON A_TABLE (
...,
X_DATE
)
/

Using A_DATE in UX_A_TABLE would fail if someone would use sysdate two
times a day.
Is there a column-data-type restricted to truncated date format?

--
Norbert
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production

From: Ed Prochak on 12 Apr 2010 09:31

From: Mark D Powell on 12 Apr 2010 10:54

On Apr 12, 6:22 am, Norbert Winkler <norbert.winkl...(a)gmx.de> wrote:
> Hi,
>
> for creating a unique index with a date-column A_DATE I'm using a
> calculated column X_DATE as (trunc(A_DATE)):
>
> CREATE UNIQUE INDEX UX_A_TABLE
> ON A_TABLE (
> ...,
> X_DATE
> )
> /
>
> Using A_DATE in UX_A_TABLE would fail if someone would use sysdate two
> times a day.
> Is there a column-data-type restricted to truncated date format?
>
> --
> Norbert
> Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
> Production

Normally indexes on date columns are non-unique but if you want to
restrict a user to only one entry per day then using trunc(sysdate)
would result in a time of midnight. All date columns always have a
time component. However I think you would need composite index on
user and date if you want to perform this test per user.

Either a column constraint or perhaps a before insert trigger that
truncates the input date value might suite your need.

HTH -- Mark D Powell --

From: Robert Klemme on 12 Apr 2010 16:57

On 04/12/2010 04:54 PM, Mark D Powell wrote:
> On Apr 12, 6:22 am, Norbert Winkler <norbert.winkl...(a)gmx.de> wrote:
>> Hi,
>>
>> for creating a unique index with a date-column A_DATE I'm using a
>> calculated column X_DATE as (trunc(A_DATE)):
>>
>> CREATE UNIQUE INDEX UX_A_TABLE
>> ON A_TABLE (
>> ...,
>> X_DATE
>> )
>> /
>>
>> Using A_DATE in UX_A_TABLE would fail if someone would use sysdate two
>> times a day.
>> Is there a column-data-type restricted to truncated date format?
>
> Normally indexes on date columns are non-unique

Err, doesn't that depend on the index definition? I don't see any
general rule that would make indexes specifically on DATE columns non
unique. Or did you mean that usually people define non unique indexes
on DATE columns?

> but if you want to
> restrict a user to only one entry per day then using trunc(sysdate)
> would result in a time of midnight. All date columns always have a
> time component. However I think you would need composite index on
> user and date if you want to perform this test per user.

What user? Am I missing something from the original question?

> Either a column constraint or perhaps a before insert trigger that
> truncates the input date value might suite your need.

I assume, with the constraint you would ensure that each DATE inserted
would be a truncated one.

Cheers

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

From: joel garry on 12 Apr 2010 17:39

On Apr 12, 1:57 pm, Robert Klemme <shortcut...(a)googlemail.com> wrote:
> On 04/12/2010 04:54 PM, Mark D Powell wrote:
>
>
>
> > On Apr 12, 6:22 am, Norbert Winkler <norbert.winkl...(a)gmx.de> wrote:
> >> Hi,
>
> >> for creating a unique index with a date-column A_DATE I'm using a
> >> calculated column X_DATE as (trunc(A_DATE)):
>
> >> CREATE UNIQUE INDEX UX_A_TABLE
> >> ON A_TABLE (
> >> ...,
> >> X_DATE
> >> )
> >> /
>
> >> Using A_DATE in UX_A_TABLE would fail if someone would use sysdate two
> >> times a day.
> >> Is there a column-data-type restricted to truncated date format?
>
> > Normally indexes on date columns are non-unique
>
> Err, doesn't that depend on the index definition? I don't see any
> general rule that would make indexes specifically on DATE columns non
> unique. Or did you mean that usually people define non unique indexes
> on DATE columns?

For one of my systems, and of course I haven't made sure that the
requirement for date is met:

1 select uniqueness,count(*) from dba_indexes a,dba_ind_columns b
2 where a.index_name=b.index_name
3 and a.owner=b.index_owner
4 and b.column_name like '%_DATE%'
5* group by uniqueness
SYS(a)TTST> /

UNIQUENES COUNT(*)
--------- ----------
NONUNIQUE 34
UNIQUE 213

I think I agree with where Mark is coming from, though, if he is
assuming primary key v. other keys. This system is kinda weird that
way for an ERP/MRP.

>
> > but if you want to
> > restrict a user to only one entry per day then using trunc(sysdate)
> > would result in a time of midnight. All date columns always have a
> > time component. However I think you would need composite index on
> > user and date if you want to perform this test per user.
>
> What user? Am I missing something from the original question?

The original question was kind of ambiguous, perhaps Norbert could
tell us what he is trying to do rather than how to do something
mysterious.

jg
--
@home.com is bogus.
http://www.infoworld.com/print/119525

| Next | Last
Pages: 1 2
Prev: Oracle Materialized Views in a RAC
Next: Video Resource: R12 Inventory Management, New Features