Project and Portfolio Management Practitioners Forum
cancel

Issue in identifying Validation values

Highlighted
saiskt
Respected Contributor.

Issue in identifying Validation values

In actual time summary report I added a project name filter which is a multiselect value.

Based on the projects selected in the project name filter, the resource field should display the respective project's resources name and to meet this condition I have changed the validation accordinly.

If I select a single project, the validation in the resource field is working perfect but for the multiselect project values the resource validation is giving me the below error.

I have attached the doc which has the SQL of the Resource validation and error description.


The validation associated with this component produced an error. Please see details for more information. (KNTA-10199)
The validation associated with this component produced an error. Please see details for more information. (KNTA-10199)
nested detail:sql exception:
ORA-01722: invalid number
component or validation id: null

11 REPLIES
shygirl
Super Contributor.

Re: Issue in identifying Validation values

when multiselect we are passing the project_id value separated by #@# and not a comma, you can modify how you are passing the multiple project_ids by using character string functions such as replace

e.g.

SELECT replace('30000#@#30001','#@#',',') FROM dual
Mahen M
Acclaimed Contributor.

Re: Issue in identifying Validation values

Can you show the filter field's SQL?

Instead of taken enter a parameter in the SQL

Regards,
Mahendran M
saiskt
Respected Contributor.

Re: Issue in identifying Validation values

Below is the SQL for the Project Name filter which I added as a one of the reports filter.

Based on the projects selected in this filter the associated resources should be displayed in the resource field with multiselect option.

SELECT project_id, project_name
FROM PM_PROJECTS
WHERE upper(project_name) like upper('?%')
AND (project_name like upper(substr('?',1,1)) || '%'
OR project_name like lower(substr('?',1,1)) || '%')
AND (SELECT PM_UTILS.Can_User_Access_Project([SYS.USER_ID], project_id) FROM DUAL) = 'Y'
and status not in (2, 3)
order by upper(project_name)
Mahen M
Acclaimed Contributor.

Re: Issue in identifying Validation values

Yes, I also think #@# seperator is the problem, Even after using the replace function, if the problem persist. Please hardcode a Project ID your Query and see the results.

Let me know if you still face any issues.

Regards,
Mahendran M
Sascha Mohr
Acclaimed Contributor.

Re: Issue in identifying Validation values

Hi,
even after replacing the separator the result is one string, so you cannot use the systax from your example. You need to search for substrings with the LIKE operator or sth like this:
AND InStr( '#' || '?' ||'#', '#' || pm.project ||'#') > 0
With this you don't need to replace anything of course.
hth
saiskt
Respected Contributor.

Re: Issue in identifying Validation values

Hi, as suggested I have changed the SQL to following

AND pm.project_id in replace (( [P_PROJECT_TS]),'#@#',',')

but no use and when i harcoded the project_id's it did worked.

Hi Sascha,could you please elaborate more on what I need to change in the query. would be great if you can provide me the logic based the above SQL.
Sascha Mohr
Acclaimed Contributor.

Re: Issue in identifying Validation values

Sure, when you just replace the separator the result would be something like:
...("123456,234567")...
what you need for the IN operator would be:
...("123456","234567")
So best would be to compare strings. What I usually do is adding "#" to the beginning of both strings to compare. This way you get it even if it is at the beginning or the end of the string.
AND InStr('#' || '[P_PROJECT_TS]' || '#', '#' || pm.project || '#') != 0
Makes also sure only exact matches are found.
Vibhor Dwivedi
Super Contributor.

Re: Issue in identifying Validation values

First make sure your rest of sql query is fine and later
In your query in place of
AND pm.project_id in ( [P_PROJECT_TS])
try this
AND INSTR('[P_PROJECT_TS]',PM.PROJECT_ID)>0
or this
AND INSTR([P_PROJECT_TS],PM.PROJECT_ID)>0;
saiskt
Respected Contributor.

Re: Issue in identifying Validation values

Hi

I understood why you were using the instr function but in my case the project_id is a numeric field so my doubt is how will it work for this particular case.

I tried making the changes but it didnt worked.

SELECT distinct user_id, full_name, username, department_meaning, email_address
FROM KNTA_USERS_V users,pm_projects pm,
pm_work_plans pwp,
wp_tasks wt,
wp_work_units wwu
WHERE ((UPPER(first_name) like UPPER('?' || '%')
AND (first_name like upper(substr('?',1,2)) || '%'
OR first_name like lower(substr('?',1,1)) || upper(substr('?',2,1)) || '%'
OR first_name like upper(substr('?',1,1)) || lower(substr('?',2,1)) || '%'
OR first_name like lower(substr('?',1,2)) || '%'))
OR (UPPER(last_name) like UPPER('?' || '%')
AND (last_name like upper(substr('?',1,2)) || '%'
OR last_name like lower(substr('?',1,1)) || upper(substr('?',2,1)) || '%'
OR last_name like upper(substr('?',1,1)) || lower(substr('?',2,1)) || '%'
OR last_name like lower(substr('?',1,2)) || '%'))
OR (UPPER(full_name) like UPPER('?' || '%')
AND (full_name like upper(substr('?',1,2)) || '%'
OR full_name like lower(substr('?',1,1)) || upper(substr('?',2,1)) || '%'
OR full_name like upper(substr('?',1,1)) || lower(substr('?',2,1)) || '%'
OR full_name like lower(substr('?',1,2)) || '%'))
OR (UPPER(username) like UPPER('?' || '%')
AND (username like upper(substr('?',1,2)) || '%'
OR username like lower(substr('?',1,1)) || upper(substr('?',2,1)) || '%'
OR username like upper(substr('?',1,1)) || lower(substr('?',2,1)) || '%'
OR username like lower(substr('?',1,2)) || '%')))
AND NVL(start_date, CURRENT_DATE) <= CURRENT_DATE
AND NVL(end_date, CURRENT_DATE + 1) > CURRENT_DATE
AND pwp.project_id = pm.project_id
AND pwp.work_plan_id = wt.work_plan_id
AND wwu.task_id = wt.task_id
AND users.user_id = wwu.resource_id
AND InStr('#' || '[P_PROJECT_TS]' || '#', '# || pm.project || #') != 0
AND pm.project_id in replace (( [P_PROJECT_TS]),'#@#',',')
[FILTER_FIELD_SQL]
ORDER BY full_name
saiskt
Respected Contributor.

Re: Issue in identifying Validation values

Hi Vibhor,

I made the changes and it did perfectly worked. As suggested by you I replaced the project_id in ([P_PROJECT_TS]) with AND INSTR([P_PROJECT_TS],PM.PROJECT_ID)>0

Thanks once again to all of you who supported me in resolving the issue
Sascha Mohr
Acclaimed Contributor.

Re: Issue in identifying Validation values

Using this line:
AND InStr('#' || '177732' || '#', '#' || pm.project_id || '#') != 0
I get a list of the resources in a project of mine. You could put a ToChar() around pm.project_id, but should not be necessary.