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?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
timestamp issue?
Moderators: chulett, rschirm, roy
timestamp issue?
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?
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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'
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:
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.
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'
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.