Link fails on VMS Date column

[Linda Symonds]

I haven't been following this thread very closely and I apologize if
this is not applicable to your problem or it's been suggested before but
here goes.

We recently moved from to Powerhouse V8.4 for Windows and migrated our
data from an Interbase database to Oracle (using powerhouse qtp to
unload and reload the tables) and found that a 0 (blank) date in
Interbase translated not into a "null" in Oracle as we had expected, but
rather into a date like 01011800 or something like that (don't remember
the exact date).  We had a great deal of trouble tracking this problem
down but once we found it we were able to compensate for it.

When we looked at the data with Powerhouse (through quiz or quick) the
dates displayed as blanks - but when we looked at the dates using a sql
query into the Oracle database, they displayed as the 01011800 date.  I
believe our DBA determined that it was related to the way that interbase
stored and exported blank dates.  

Linda Symonds


-----Original Message-----
From: powerh-l-admin@lists.sowder.com
[mailto:powerh-l-admin@lists.sowder.com] On Behalf Of Drewbrook, Neil
EDUC:EX
Sent: Thursday, March 03, 2005 7:02 PM
To: 'powerh-l@lists.sowder.com'
Subject: RE: Link fails on VMS Date column

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.