SELECT CP.Id, CP.NameFROM {ProjectWorkspaceOACompetentPersonMapping} PWCPMINNER JOIN {CompetentPerson} CP ON CP.Id = PWCPM.CompetentPersonIdINNER JOIN {CompetentPersonCompetencyMapping} CPCM ON CPCM.CompetentPersonId = CP.IdWHERE PWCPM.ProjectWorkspaceId = @In_ProjectWorkspaceId AND CPCM.CompetenciesSourcesId IN (@In_SelectedCompetency)GROUP BY CP.Id, CP.NameHAVING COUNT(DISTINCT CPCM.CompetenciesSourcesId) = @In_SelectedCompetencyCountORDER BY CP.Name;
The above query work in Db Fiddle but when tried in advanced mysql query, it doesnt work. I am using BuildSafe_InClauseTextList to create the expand inline text.
Are you able to share a sample OML or the structure of your entities? Also if CPCM.CompetenciesSourcesId is not text, have you tried using BuildSafe_InClauseIntegerList instead of BuildSafe_InClauseTextList ?
Hello @Wee Yaw Pui,
I think the only suspect in the query is @In_SelectedCompetency.
Did you make sure that the input parameter of the Advanced SQL widget has its Expand Inline property enabled? double check on that.
Yes, sir. Expand inline has been enabled
Hi @Wee Yaw Pui ,
I see you have CompetenciesSourcesId, have you tried BuildSafe_InClauseIntegerList ?
Also what exactly doesn't work?
Hi Sir,
Are you able to understand from reading my query ?
Basically, i have 4 tables,
ProjectWorkspace Table
ProjectWorkspaceCompetentPerson Table
CompetentPerson Table
CompetenciesTable
So now, user will need to create a job where users will need to select from a set of predefine competencies. When user submit the job, system should prompt user who are the competent persons that matched the selection of predefine competencies, it could be 1/2/3 competencies selected and only competent person who matches all of the competencies should be displayed.