Link fails on VMS Date column

[brian_matthewsbrian matthews]

I read somewhere that when using OracleRDB you must use the 'date ansi' 
datatype to perform date-time math against dates, and this can't be done 
using 'date vms' types. Could the index retreival in the database be aware 
of this and therefore truncating ?

regards Bri,




>From: "Drewbrook, Neil EDUC:EX" <Neil.Drewbrook@gems3.gov.bc.ca>
>To: "'powerh-l@lists.sowder.com'" <powerh-l@lists.sowder.com>
>Subject: RE: Link fails on VMS Date column
>Date: Thu, 3 Mar 2005 16:01:45 -0800
>
>If Powerhouse is rounding VMS Date values before using them for indexed
>retrieval, then that's a pretty big flaw; but it's the only theory that 
>fits
>-- the link works using a new (non-unique) index, with:
>
>	> define d_date_only num*8 = dateextract( R_DATE of TABLE_1, date )
>	> define d_time_only num*8 = dateextract( R_DATE of TABLE_1, time )
>	> select A2     &
>	>   if d_date_only = dateextract( R_DATE of A2, date)    &
>	>  and d_time_only = dateextract( R_DATE of A2, time )
>
>Does anyone from Cognos have any insight into what's happening here?
>
> > -----Original Message-----
> > From: Kevin Erne [mailto:kevin.erne@lineone.net]
> > Sent: February 28, 2005 2:15 PM
> > To: Drewbrook, Neil EDUC:EX; 'powerh-l@lists.sowder.com'
> > Subject: RE: Link fails on VMS Date column
> >
> >
> > If you create a view in the db on the self join  and access
> > it via quiz it
> > shoudl return the right no of records.
> > My guess is that some sort of rounding may be happening
> > between the Powerhouse
> > data access layer and
> > Rdb. Date VMS is a 64 bit timestamp (no of seconds since
> > 117th Nov 1858??)
> > so it could easily get corrupted.
> >
> > >-- Original Message --
> > >From: "Drewbrook, Neil EDUC:EX" <Neil.Drewbrook@gems3.gov.bc.ca>
> > >To: "'powerh-l@lists.sowder.com'" <powerh-l@lists.sowder.com>
> > >Subject: Link fails on VMS Date column
> > >Date: Fri, 25 Feb 2005 14:35:56 -0800
> > >
> > >
> > >This issue is specific to OracleRdb on VMS.
> > >
> > >I'm trying to link to a table using an index, but the link
> > fails when a
> > >matching record does exist.  In the example below, the link
> > should succeed
> > >for every record; each row should join to itself.  It also
> > fails in Quick
> > >
> > >R_DATE is Date VMS; it is the first column in an index.
> > >I've dropped and re-created the index.
> > >Joining the table to itself using SQL works correctly.
> > >Other tables with indexes on Date VMS columns link to
> > themselves correctly
> > >using quiz.
> > >
> > >> access TABLE_1
> > >> go
> > >
> > >Records selected: 24623
> > >
> > >> acc TABLE_1  link R_DATE  to R_DATE  of TABLE_1 alias a1
> > >> go
> > >
> > >Records selected: 8
> > >
> > >
> > >Any ideas?
> > >= = = = = = = = = = = = = = = = = = = = = = = = = = = =
> > >Mailing list: powerh-l@lists.sowder.com
> > >Subscribe: "subscribe" in message body to
> > powerh-l-request@lists.sowder.com
> > >Unsubscribe: "unsubscribe <password>" in message body to
> > powerh-l-request@lists.sowder.com
> > >http://lists.sowder.com/mailman/listinfo/powerh-l
> > >This list is closed, thus to post to the list you must be a
> > subscriber.
> >
> >
> >
> > ___________________________________________________________
> >
> > Book yourself something to look forward to in 2005.
> > Cheap flights - http://www.tiscali.co.uk/travel/flights/
> > Bargain holidays - http://www.tiscali.co.uk/travel/holidays/
> >
> >
> >
> >
>= = = = = = = = = = = = = = = = = = = = = = = = = = = =
>Mailing list: powerh-l@lists.sowder.com
>Subscribe: "subscribe" in message body to powerh-l-request@lists.sowder.com
>Unsubscribe: "unsubscribe <password>" in message body to 
>powerh-l-request@lists.sowder.com
>http://lists.sowder.com/mailman/listinfo/powerh-l
>This list is closed, thus to post to the list you must be a subscriber.

_________________________________________________________________
Want to block unwanted pop-ups? Download the free MSN Toolbar now!  
http://toolbar.msn.co.uk/