An error ORA-01843 occured while performing SQL prompt insert in Cognos

Technote (troubleshooting)


Problem(Abstract)

Attempts to insert sql prompt date format result in error when using IBM Cognos 8.

Cause

The error snapshot shows error ORA-01843, which is due to invalid month. The user might be entering a month portion of the date which was not a valid month format.

Resolving the problem

Based on the SQL insertable statement, the following format are updated for the to_date function

SQL syntax to_date(#prompt('pt_start')#,'MON DD, YYYY HH:MI AM') and "HSIA_CELL_TAB_VIEW"."TSTAMP"<=to_date(#prompt('pt_end')#,'MON DD, YYYY HH:MI AM')

But from the SQL_Insertable_query_from_Generated_SQL_MDX.txt (after the prompt was issued), these date entries as shown, and the format of MON DD YYY HH:MI AM from the above SQL insertable statement does not match the 2012-04-06T00:00.000 format.

SQL syntax 2012-04-06T00:00:00.000','MON DD, YYYY HH:MI AM') and "HSIA_CELL_TAB_VIEW"."TSTAMP"<=to_date('2012-04-06T00:00:00.000','MON DD, YYYY HH:MI AM')

Based on the SQL oracle explanation of the to_date function:-

MON Means Abbreviated name of the month, JAN, FEB,MAR,APR,MAY,JUN,JUL,AUG,Sep,OCT,Nov,Dec
DD Day of the month(1-31)
HH Hour of the day 1-12
YYYY 4 digit of the year
AM Meridian Indicator
MI (Minute 0-59)

To resolve this error:-

Please change the SQL_insertable statement to have the correct date format mask to match the value from SQL_Insertable_query_from_Generated_SQL_MDX.txt

Product Alias/Synonym

TNPMW

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Tivoli Netcool Performance Manager for Wireless

Software version:

1.2.1, 1.3

Operating system(s):

AIX, Linux, Solaris

Software edition:

All Editions

Reference #:

1591150

Modified date:

2013-04-01

Translate my page

Machine Translation

Content navigation