Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

ORA 01841 in Portlet

Highlighted
cat_gon0208
Super Collector

ORA 01841 in Portlet

Hi All,

 

Strange that this code of ours is working fine in Dev TOAD but not in DEV Sqlplus and Prod. Any suggestion is much appreciated.

 

Thanks.

 

Catherine

 

select * from kcrt_requests_v req,kcrt_fg_master_proj_ref prj,

kcrt_request_types rty

WHERE1=1AND req.status_name <>'Not Submitted'AND(req.status_code NOTLIKE'CLOSED%'AND req.status_code NOTLIKE'CANCEL%')AND req.request_id = prj.request_id

 

AND req.request_type_id = rty.request_type_id

 

AND rty.request_type_name =' Asset - Log'ANDNVL(TO_DATE(req.visible_parameter2,'yyyy-mm-dd hh24:mi:ss'),SYSDATE)-NVL(TO_DATE(req.visible_parameter7,'yyyy-mm-dd hh24:mi:ss'),SYSDATE)>=0

5 REPLIES
Utkarsh_Mishra
Honored Contributor

Re: ORA 01841 in Portlet

Check that any of your request is having incorrect data (non-date) either in req.visible_parameter2 or req.visible_parameter7.

 

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
cat_gon0208
Super Collector

Re: ORA 01841 in Portlet

Hi Utkarsh, Thanks for your attention. Tried that but no luck. the date minus is working good if you place it in the select and not in the where clause thanks Catherine
Celil
Esteemed Contributor

Re: ORA 01841 in Portlet

Hi Catherine,

Is there any error message ?
Celil

IT Governance Professional
& PPM Solution Architect
cat_gon0208
Super Collector

Re: ORA 01841 in Portlet

Hi Celil,

 

Thanks.

 

No other error message is shown aside from the oracle error.

 

In dev environment the query used in the portlet is running fine using TOAD but is ORA 01841 in sqlplus

In UAT environment, we are getting ORA 1861 in both Workbench and sqlplus (we do not have TOAD for UAT)

In Prod, we are getting ORA 01841 in workbench, we have yet to try using sqlplus.

 

Could it be a NLS format setting issue?

 

Thanks.

 

Catherine

Utkarsh_Mishra
Honored Contributor

Re: ORA 01841 in Portlet

Is you DEV database completely in SYNC with Production database.

 

If it is no then can you plase share the result of below query.  All the error highlighted are due to invalid data.

Secondly your are also not including the BATCH_NUMBER, so it might be returing PARAM2 and PARAM7 from different batch that is not a DATE.

 

 

select NVL(TO_DATE(req.visible_parameter2,'yyyy-mm-dd hh24:mi:ss'),SYSDATE),
NVL(TO_DATE(req.visible_parameter7,'yyyy-mm-dd hh24:mi:ss'),SYSDATE)
, req.batch_number

from kcrt_requests_v req,kcrt_fg_master_proj_ref prj,
kcrt_request_types rty
WHERE 1=1
AND req.status_name <>'Not Submitted'
AND(req.status_code NOT LIKE 'CLOSED%' AND req.status_code NOT LIKE 'CANCEL%')
AND req.request_id = prj.request_id
AND req.request_type_id = rty.request_type_id
AND rty.request_type_name =' Asset - Log'

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
//Add this to "OnDomLoad" event