The project manager column in the database is not a number field so that it can be a multi-select field. If you look at a project with multiple project managers, that field would have 1#@#30345#@#130345 or something very similar based on the user IDs of the project managers. When trying to filter on multi-select fields, I generally use something like this
The requires that your filter is a single select filter field but also guarantees that if you had users with user IDs that were a subset of another (130345 and 30345) would only get the one you really wanted.
thanks guys.i struggled to get it working with both your suggestions.I've found another way which is using trustee ids.The query is shown below and its working well.
and pfm.prj_project_id IN ( SELECT P.PROJECT_ID FROM PM_PROJECTS P, ITG_TRUSTEE T, ITG_GROUP_PARTICIPANT_USERS PU, KNTA_USERS U WHERE T.TRUSTEE_TYPE_CODE = 2 AND T.PROJECT_TYPE_ID = P.PROJECT_TYPE_ID AND PU.TRUSTEE_ID = T.TRUSTEE_ID AND U.USER_ID = PU.USER_ID AND U.USER_ID IN ([P.PRJ_MNG]))