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
Did you consider using the data fabric feature in Appian instead of a view?
This is what ChatGPT thinks:
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`, GROUP_CONCAT(DISTINCT CONCAT(`srp`.`firstName`, ' ', `srp`.`lastName`) SEPARATOR ';') 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_
Hi stefen,thanks for the update .where to update this code in view.could you please help me ?
I am not sure how to read you question ...
Do you know what database views are and how to develop one? In case you have some very generic questions, I suggest to first learn that skills. Database views are NOT a feature in Appian but the MariaDB database.
I want to apply the above functionality to view in MariaDB database of appian
Here you can find the documentation for the included database
https://docs.appian.com/suite/help/23.2/appian-cloud-database-administration.html
I am still not sure what your actual question is. If you are not familiar with database views, learn that first and then return.
Hi sfefan,thanks for the information.Actually my question is to add group_concate functionality to view ,currently one of the field in my view fetching a single value instead of fetching all the values that are linked to it.
I already tried to answer this question.