timestamp issue?

jshurak · Post by **jshurak** » Fri May 11, 2007 12:18 pm

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?

DSguru2B · Post by **DSguru2B** » Fri May 11, 2007 12:45 pm

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?

jshurak · Post by **jshurak** » Fri May 11, 2007 12:53 pm

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:

DSguru2B · Post by **DSguru2B** » Fri May 11, 2007 1:40 pm

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'

jshurak · Post by **jshurak** » Fri May 11, 2007 2:35 pm

excellent. That worked. Thank you so much!

ray.wurlod · Post by **ray.wurlod** » Fri May 11, 2007 3:52 pm

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.