Page 1 of 1
timestamp issue?
Posted: Fri May 11, 2007 12:18 pm
by jshurak
P2P_Hist_Count..ODBC_0.DSLink3: DSD.BCIOpenR results of SQLColAttributes(batch_strt_tmstp) gave MetaData mismatch
COLUMN.TYPE Expected = Timestamp Actual = Decimal
P2P_Hist_Count..ODBC_0.DSLink3: DSD.BCIOpenR results of SQLColAttributes(batch_strt_tmstp) gave MetaData mismatch
MetaData mismatch on COLUMN.SCALE Expected = 0 Actual = 3
P2P_Hist_Count..ODBC_0.DSLink3: DSD.BCIOpenR results of SQLColAttributes(batch_end_tmstp) gave MetaData mismatch
COLUMN.TYPE Expected = Timestamp Actual = Decimal
P2P_Hist_Count..ODBC_0.DSLink3: DSD.BCIOpenR results of SQLColAttributes(batch_end_tmstp) gave MetaData mismatch
MetaData mismatch on COLUMN.SCALE Expected = 0 Actual = 3
P2P_Hist_Count..ODBC_0.DSLink3: DSD.BCIGetNext call to SQLFetch failed.
SQL statement:select batch_strt_tmstp, batch_end_tmstp
from DATAMGR.BATCH_LOG
where process_id = 'T1_LOAD_XT_P2P_HIST'
and trunc(batch_strt_tmstp) = '11-MAY-2007'
SQLSTATE=S1000, DBMS.CODE=932
[DataStage][SQL Client][ODBC][Oracle][ODBC][Ora]ORA-00932: inconsistent datatypes: expected got
I'm not sure what I'm doing wrong here. These columns are in fact timestamps. For some reason Datastage views them as decimals. Any ideas?
Posted: Fri May 11, 2007 12:45 pm
by DSguru2B
Are you able to view data? Can you post the sql you are using? Also you are using tunc() function on the timestamp, but I do not see any time part, just the date part. Is it really a timestamp or just date, and if just date, then why are you using the trunc() function?
Posted: Fri May 11, 2007 12:53 pm
by jshurak
Code: Select all
select batch_strt_tmstp, batch_end_tmstp
from DATAMGR.BATCH_LOG
where process_id = 'T1_LOAD_XT_P2P_HIST'
and trunc(batch_strt_tmstp) = '11-MAY-2007'
this is the actual query I'm using. the 11-MAY-2007 will be rplace with a variable. It is a timestamp. I'm trunc-ing it because I want to collect data only from that day.
I can't view the data. I get the error message I posted when I try. The query works perfectly fine outside of datastage. Here is a shot of the result out of datastage:
Posted: Fri May 11, 2007 1:40 pm
by DSguru2B
Use generated sql. Also, '11-MAY-2007' is a mere representation of the date format set for your sql tool. Also, trunc() will not elimiate the time part it will just set the time to
'00:00:00'.
Try the following if you insist for user defined sql.
Code: Select all
select TO_CHAR(batch_strt_tmstp, 'YYYY-MM-DD HH24:MI:SS'),
TO_CHAR(batch_end_tmstp, 'YYYY-MM-DD HH24:MI:SS')
from DATAMGR.BATCH_LOG
where process_id = 'T1_LOAD_XT_P2P_HIST'
and TO_CHAR(TRUNC(batch_strt_tmstp), 'YYYY-MM-DD HH24:MI:SS') = '2007-05-11 00:00:00'
Posted: Fri May 11, 2007 2:35 pm
by jshurak
excellent. That worked. Thank you so much!
Posted: Fri May 11, 2007 3:52 pm
by ray.wurlod
Time to mark this thread as resolved, then.
The issue appears to be that either the database or DataStage is using milliseconds in timestamps by default, and the other isn't. Forcing "no milliseconds" via the date picture in TO_CHAR() has rendered the formats compatible. You could have changed the metadata to precision 22 scale 3 to achieve the same result.