Project and Portfolio Management Practitioners Forum
cancel

The validation associated with this component

Highlighted
shijub
Super Collector

The validation associated with this component

Hi,

 

We have upgraded PPM from 8.04 to 9.14.

After upgrading the instance we are recieving

"The validation associated with this component" on clicking field with Auto Complete list.

We were not recieving the same when we were running on PPM 8.0 version.

 

Also on reviewing the logs found the below error

"ORA-01460: unimplemented or unreasonable conversion requested".

 

Attached is the query and the logs captured.

 

Thanks in advance.

 

 

5 REPLIES
jsalass
Frequent Visitor

Re: The validation associated with this component

Hi Shijub,

 

I checked and this part of your SQL validation is working fine:

 

Select request_id,Marketing_Intiative,Created_Date,End_Date
from
(select krd.request_id,kcrt.parameter41 Marketing_Intiative,
SUBSTR(kcrt.parameter1, 1, 10) Created_Date,SUBSTR(kcrt.parameter2, 1, 10) End_Date
from kcrt_request_details krd, kcrt_requests kr,kcrt_table_entries kcrt
where kr.request_id= krd.request_id
and krd.request_id=kcrt.request_id
     and kr.status_id in (select status_id from kcrt_statuses
    where status_name in ('Not Submitted','Closed'))
     and kr.request_type_id = (select request_type_id from KCRT_REQUEST_TYPES_NLS
    where mdl_view_name_root = 'MARKETING_INI')
    and krd.batch_number=1
    union
select request_id,parameter41 Marketing_intiative,parameter1 Created_Date,parameter2 End_Date
from kcrt_request_details kr where kr.request_type_id =
(select request_type_id from KCRT_REQUEST_TYPES_NLS
    where mdl_view_name_root = 'MARKETING_INI')
and upper(kr.parameter1)=upper('Not Specific')
)

 

and fails when you call this where clause:

 

 where (to_date(Created_Date,'yyyy-mm-dd') < sysdate) and
 (End_Date is null  or to_date(End_Date,'yyyy-mm-dd') > sysdate)

 

So the problem is with values being store in the created_date and end_date columsn. I noticed those columns are formed by these instructions:

 

SUBSTR(kcrt.parameter1, 1, 10) Created_Date and SUBSTR(kcrt.parameter2, 1, 10) End_Date

 

so just check this part

Select krd.request_id,kcrt.parameter41 Marketing_Intiative,
SUBSTR(kcrt.parameter1, 1, 10) Created_Date,SUBSTR(kcrt.parameter2, 1, 10) End_Date
from kcrt_request_details krd, kcrt_requests kr,kcrt_table_entries kcrt
where kr.request_id= krd.request_id
and krd.request_id=kcrt.request_id

 

and apply the to_date instruction to the SUBSTR function directly. Probably you will need to increase the range of characters you are using.

 

Check these links too:

 

http://www.dba-oracle.com/t_ora_01840_input_value_not_long_enough_for_date_format.htm
http://www.dba-oracle.com/t_ora_01460_unimplemented_or_unreasonable_conversion_requested.htm

 

Hope this helps!

 

Regards,

Jonathan

shijub
Super Collector

Re: The validation associated with this component

Thanks Jonathan,

 

But the same code used to work for PPM 8.04 version, so is there any changes to be done for this to be get working.

 

Thanks in advance

 

Regards,

jsalass
Frequent Visitor

Re: The validation associated with this component

Sure Shijub,

 

yes, I run into the same issue in my instance.

 

To resolve it I make sure that the value being stored in the PARAMETER1 columns has a complete datetime value.

 

I think you can open a ticket so HP PPM support can contact the labs to see whats changed that is affecting your validation. They should be able to provide the root of this change.

 

Thanks,

Jonathan

Mohit_Agrawal
Frequent Visitor

Re: The validation associated with this component

Sure Shijub,

 

Just Check with below clause.

 

where (to_date(to_char(Created_Date,'yyyy-mm-dd')) < sysdate) and
 (End_Date is null  or to_date(to_char(End_Date,'yyyy-mm-dd')) > sysdate)

 

Thanks!!

Mohit

AlexSavencu
Honored Contributor

Re: The validation associated with this component

Hi,

 

the problem is the way you convert varchar2 fields to date values:

 

SUBSTR(kcrt.parameter1, 1, 10) Created_Date

 

you should use the following format : 'YYYY-MM-DD HH24:MI:SS'.

 

Second, since you get "ORA-01840: input value not long enough for date format", it is obvious that the field you are trying to query does not have proper format / data.

 

cheers

alex


--remember to kudos people who helped solve your problem
//Add this to "OnDomLoad" event