Rational Insight 1.0.1 Why is RIODS.REQUEST_CHANGE_HISTORY.CHANGE_DATE truncating time?
2 answers
RIODS.REQUEST_CHANGE_HISTORY.CHANGE_DATE is
Data Type : datetime
Length : 8
Numeric Precision : 23
Numeric Scale : 3
In looking at the fact build in cognos data manager, the TIMESTAMP_COL is going through a calculation to derive CHANGE_DATE:
if (DBMS( $Connection )='MICROSOFT SQL SERVER')
then return PosixTime_To_Timestamp( ToInteger( TIMESTAMP_COL ) );
else return ToDate( TIMESTAMP_COL, 'yyyy-mm-dd' );
When i try to test this query in data manager:
SELECT W."FACT_ID",
TI."{$TIMESTAMP_COL}",
W."WI_ID",
W."PREV_TEAM_AREA_ID",
W."PREV_CATEGORY_ID",
W."PREV_ITERATION_ID",
TP."WI_TYPE",
W."PREV_OWNER_ID",
ST."STATE_NAME",
P."WI_PRIORITY",
SV."WI_SEVERITY",
"PREV_COMMENT_COUNT",
"PREV_SUBSCRIBER_COUNT",
"PREV_TAGS",
"PREV_DURATION",
"PREV_TIME_SPENT",
"PREV_SUMMARY"
FROM {$WORKITEM_CHNGS} W
LEFT JOIN {$TIME_TBL} TI ON W."TIME_ID"=TI."TIME_ID"
LEFT JOIN {$TYPE_TBL} TP ON W."PREV_WI_TYPE_ID"=TP."TYPE_ID"
LEFT JOIN {$STATE_TBL} ST ON W."PREV_WI_STATE_ID"=ST."{$STATE_COL}"
LEFT JOIN {$PRIORITY} P ON W."PREV_WI_PRIORITY_ID"=P."PRIORITY_ID"
LEFT JOIN {$SEVERITY} SV ON W."PREV_WI_SEVERITY_ID"=SV."SEVERITY_ID"
i get prompted for the following variables and i'm not sure of the values to provide:
$TIMESTAMP_COL
$WORKITEM_CHNGS
$TIME_TBL
$TYPE_TBL
$STATE_TBL
$STATE_COL
$PRIORITY
$SEVERITY
and i'm challenged in getting the correct set of values for all...
Comments
Dave, as far as the variables, check a previous successful fact build log (for this specific fact build) and you may find some of those values specified, which will help your test scenario above.
Based on the query you provided above, I can see you are running on an older version of RTC (pre-3.0.1.1). Your column (CHANGE_DATE) you said is 8 in length and of type 'datetime'. If its 8 characters, then your date would have no day value and show something like '2014-03-' ? If so, you can increase the size of that column to 10, the date would then be '2014-03-11'. I am not sure what yours looks like. The ETL normally complains if it tries to insert a 10 character value into an 8 character column, so I am not positive this is the fix without looking at the environment.
I do not know how long this has been going on in your environment, or if you just happened to notice, but it may be best if you open a PMR with Support so they can look at the logs and your environment.