ORA 01830 in Information Design Tool (Date Format Problem)

[former_member199597]

Hello Guys,

I am facing a problem with a relational connection to an Oracle DB via Oracle Client (not JDBC).

The connection to the DB works fine but I cannot execute a "SELECT * FROM xxx".

I always get the error on a field called "ACT_DATE" which is (type 13,len 😎 in the IDT - where in SQL Developper its working fine.

The field is defined as DATE (german format).

Now I tried the following in the SQL Developper:

TO_CHAR(
     ACT_DATE, 'dd.MM.YY',
     'NLS_DATE_LANGUAGE = German')
     ,'dd.MM.yyyy')

working fine -> Also DUMP() will show me Type 1.

When I create a dimension in IDT and the following:

SELECT ACT_DATE FROM xxx .

-> ORA 01830.

When I try the TO_CHAR

-> ORA 01830.

I cannot get the value from the table, where if I use SQL Developper its working fine.

Again I just what to view the content of the table - I do not want any conversion or what so ever.

Its just not working for this date column.

Anyone can help me out here?

Cheers,

andreas

[former_member199597]

can anyone help me out here?

*bump*

[sateesh_kumar1]

Hi Andreas ,

As an option  you can apply the same logic in webi , since it is not filtering out any data from backend.Doing this in webi will not cause any performance issue.

[former_member199597]

Hello Sathish,

Thank you for your reply.

Maybe I did not explain precisely - my problem is not regarding performance but I cannot even analyse the data from the table. When I want to see the table content I get the error ORA 01830. To make my life easier I would really appreciate to see the contents of the table within IDT.

Maybe the error is caused by regional settings - since the database is "GERMAN" where we develop in SWITZERLAND.

I added parameters e.g.

NLS_DATE_FORMAT ..

No result. I belive this has to be done before the connection or with somthing like:

ALTER CONNECTION

Can I do this somewhere in the Information Design Tool?

Cheers,

Andreas