topic Oracle timestamp (6) gives SQL_UNKNOWN_TYPE (0) as DATA_TYPE in QlikView

Oracle timestamp (6) gives SQL_UNKNOWN_TYPE (0) as DATA_TYPE

Tue, 26 Jul 2011 11:00:00 GMT

Hi All,

I have a client who is connecting to both tables and views in Oracle 11 via the 32 bit ODBC driver MSORCL32.dll v 6.01.7601.17514 from qv 10. He is not able to acquire data from Timestamp (6) datatype columns, they are marked as SQL_UNKNOWN_TYPE (0) as DATA_TYPE in the table information box. The TYPE_NAME is TIMESTAMP (6). Any ideas please? Do you think I should pass this back formally to support?

I ahve asked him to try this in the mean time:-

Load *;

SQL SELECT TO_CHAR(DTCOL,'DD-MON-YYYY HH24:MI:SSxFF')

FROM TABLENAME;

Cheers,

Jane

Oracle timestamp (6) gives SQL_UNKNOWN_TYPE (0) as DATA_TYPE

kaushiknsolanki — Tue, 26 Jul 2011 11:05:07 GMT

HI,

I dont know this will solve your problem or not, but you can try this,

Load *, Date(DTCOL,'DD-MM-YYYY hh:mm:ss') as DTCOL;

Select DTCOL

From TableName;

Regards,

Kaushik Solanki

Oracle timestamp (6) gives SQL_UNKNOWN_TYPE (0) as DATA_TYPE

Tue, 26 Jul 2011 11:15:17 GMT

Hi Kaushik,

The problem is that the SQL SELECT DTCOL gives an error. I am trying to also troubleshhot this over the phone which is hard.

Cheers,

Jane

Oracle timestamp (6) gives SQL_UNKNOWN_TYPE (0) as DATA_TYPE

kaushiknsolanki — Tue, 26 Jul 2011 11:16:51 GMT

Hi,

Can you tell me what kind of error its giving.

Regards,

Kaushik Solanki

Oracle timestamp (6) gives SQL_UNKNOWN_TYPE (0) as DATA_TYPE

Tue, 26 Jul 2011 12:05:49 GMT

Hi Kaushik,

It gives this very helpful message!!

SQL##f - SqlState: 00000,ErrorCode: 0, ErrorMsg:

SQL SELECT DTCOL as RawDate

FROM CIRCNETTRUNK.VW_DISTRIBUTION;

If I wrap the DTCOL in the Select statement thus

TO_CHAR(DTCOL,'DD-MON-YYYY HH24:MI:SSxFF') ad CharDate

it works. The ODBC SQL Engine is not parsing timestamp data types correctly so if you force them to char strings it loads and then you have to unpick thedate from it in the Load statement thus

Date#(left(CharDate, 20), 'DD-MMM-YYYY hh:mm:ss') as DateStampDate

Which is obviously tortuous for a new QV user. I think this is a bug situation.

Cheers,

Jane