HI COGNOS FOLKS,
We have a report which all columns from stored procedure along with parameter P_INDATE .
When i run the report i am prompted to provide the value for P_INDATE parameter in the textbox prompt which i am providing the value as 'DD-MON-YYYY'. Getting data based on the input.
Every thing is fine till here.
I have added the Dateprompt for this parameter P_INDATE. i just ran report and selected the date and clicked on finish. It has thrown error message.
""ORA-01861 Literal does not match format string""
it is taking the date in 'YYYY-MM-DD' format and the value for this column in db is 'DD-MON-YYYY'.
Can any one tell me the solution for this issue. !!!!!!
Can we get any JAVASCRIPT to pass date value from date prompt to text box prompt and hide text box prompt.??
OR
Can we convert date format??
Any help would be appreciated.
Thanks in advance
Gnani
Pls send your solution ASAP.
Thanks,
Gnani
I tried the below expression in FM
trim( substr( ?Parameter1? , instr ( ?Parameter1? , '-' , 1,2 )+1, 2 ) ) || '-' ||
(
if ( trim( substr( ?Parameter1? , instr ( ?Parameter1? , '-' ,1, 1 ) +1, 2 ) ) = '01' ) then
( 'JAN' )
else
if ( trim( substr( ?Parameter1? , instr ( ?Parameter1? , '-' ,1, 1 ) +1, 2 ) ) = '02' ) then
( 'FEB' )
else
if ( trim( substr( ?Parameter1? , instr ( ?Parameter1? , '-' ,1, 1 ) +1, 2 ) ) = '03' ) then
( 'MAR' )
else
if ( trim( substr( ?Parameter1? , instr ( ?Parameter1? , '-' ,1, 1 ) +1, 2 ) ) = '04' ) then
( 'APR' )
else
if ( trim( substr( ?Parameter1? , instr ( ?Parameter1? , '-' ,1, 1 ) +1, 2 ) ) = '05' ) then
( 'MAY' )
else
if ( trim( substr( ?Parameter1? , instr ( ?Parameter1? , '-' ,1, 1 ) +1, 2 ) ) = '06' ) then
( 'JUN' )
else
if ( trim( substr( ?Parameter1? , instr ( ?Parameter1? , '-' ,1, 1 ) +1, 2 ) ) = '07' ) then
( 'JUL' )
else
if ( trim( substr( ?Parameter1? , instr ( ?Parameter1? , '-' ,1, 1 ) +1, 2 ) ) = '08' ) then
( 'AUG' )
else
if ( trim( substr( ?Parameter1? , instr ( ?Parameter1? , '-' ,1, 1 ) +1, 2 ) ) = '09' ) then
( 'SEP' )
else
if ( trim( substr( ?Parameter1? , instr ( ?Parameter1? , '-' ,1, 1 ) +1, 2 ) ) = '10' ) then
( 'OCT' )
else
if ( trim( substr( ?Parameter1? , instr ( ?Parameter1? , '-' ,1, 1 ) +1, 2 ) ) = '11' ) then
( 'NOV' )
else
( 'DEC' )
)
|| '-' ||
substr( ?Parameter1? , 1, instr ( ?Parameter1? , '-' ,1 )-1 )
Replace Parameter1 with your parameter name in the expression and use it in the expression where you pass in value as parameter to stored procedure.
Thanks,
Gnani
When i tried the above expression i am gettting below error
RQP-DEF-0177 An error occurred while performing operation 'sqlExecute' status='-9'.
UDA-SQL-0107 A general exception has occurred during the operation "execute".
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "SCHEMANAME.TABLENAME", line no
ORA-06512: at line 1
Is any one here to help me.
Thanks,
Gnani