Link fails on VMS Date column

[Drewbrook, Neil EDUC:EX]

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/
> 
> 
> 
>