Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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:MISmiley FrustratedSxFF')

FROM TABLENAME;


Cheers,

Jane

4 Replies

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

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

Not applicable

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

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

Hi,

   Can you tell me what kind of error its giving.

Regards,

Kaushik Solanki

Not applicable

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

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:MISmiley FrustratedSxFF') 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