Hi all ,
Can we update view by adding functionality of group_concat to a field .If yes, Can you please instruct me how to do?
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `ISR_v_MeetingProposals` AS SELECT `mjs`.`meetingId` AS `MeetingId`, `mjs`.`studyRequestId` AS `StudyRequestId`, `mjs`.`sortOrder` AS `SortOrder`, `sr`.`referenceNumber` AS `ReferenceNumber`, `sr`.`studyTitle` AS `StudyTitle`, (select group_concat(distinct `ta`.`name` separator ';') from `ISR_TherapeuticArea` `ta` where `ta`.`therapeuticAreaId` in (select `srt`.`taId` from `ISR_j_StudyRequest_TherapeuticArea` `srt` where `srt`.`studyRequestId` = `sr`.`studyRequestId`)) AS `TherapeuticArea`, `c`.`name` AS `countryName`, `c`.`code` AS `countryCode`, `cc`.`code` AS `currencyCode`, `ss`.`totalAmountRequested` AS `totalAmountRequested`, `ss`.`convertedTotalAmtRqsted` AS `convertedTotalAmtRqsted`, concat(`srp`.`firstName`,' ',`srp`.`lastName`) AS `PrimaryInvestigator`, `sr`.`medicalScientist` AS `medicalScientist`, `sr`.`researchType` AS `researchType`, `sr`.`mastSlideDocId` AS `mastSlideDocId`, `mp`.`primaryReviewer` AS `primaryReviewer`, `ma`.`adhocReviewer` AS `adhocReviewer` FROM ((((((((((`ISR_j_Meeting_StudyRequest` `mjs` left join `ISR_StudyRequest` `sr` on(`mjs`.`studyRequestId` = `sr`.`studyRequestId` and `mjs`.`isActive` = 1 and `sr`.`isActive` = 1 and `mjs`.`mastDecision` not in ('MASTNODIS','MASTTAB'))) left join `ISR_j_StudyRequest_TherapeuticArea` `srt` on(`srt`.`studyRequestId` = `sr`.`studyRequestId`)) left join `ISR_TherapeuticArea` `ta` on(`srt`.`taId` = `ta`.`therapeuticAreaId` and `srt`.`studyRequestId` = `sr`.`studyRequestId`)) left join `ISR_j_StudyRequest_Person` `srp` on(`srp`.`studyRequestId` = `sr`.`studyRequestId`)) left join `ISR_Address` `a` on(`a`.`typeId` = `srp`.`studyPnId` and `a`.`type` = 'PR' and `srp`.`studyRequestId` = `sr`.`studyRequestId`)) left join `CMN_Country` `c` on(`c`.`countryId` = `a`.`countryId`)) left join `ISR_StudySupport` `ss` on(`ss`.`studyRequestId` = `sr`.`studyRequestId`)) left join `CMN_Currency` `cc` on(`ss`.`currency` = `cc`.`currency`)) left join `ISR_j_Meeting_PrimaryReviewer` `mp` on(`mjs`.`studyRequestId` = `mp`.`studyRequestId` and `mp`.`isActive` = 1)) left join `ISR_j_Meeting_AdhocReviewer` `ma` on(`mjs`.`studyRequestId` = `ma`.`studyRequestId` and `ma`.`isActive` = 1)) GROUP BY `sr`.`studyRequestId`, `mjs`.`meetingId` ORDER BY `mjs`.`studyRequestId` DESC
Can you please help me?
Discussion posts and replies are publicly visible
Before doing a group concat in a view, are you certain you can't use a record type for the tables? This can be done with a customRecordField.
Otherwise, if you need help creating database views, I would suggest you take a look at ChatGPT or some other resource. Tons of help available online.