STRING_AGG给出字符串值的错误输出

Question

我正在执行一个查询，它将获取医生的姓名，访问该医生的患者总数，他去过的诊所的数量，基于他去过的诊所的数量列出诊所的名称

查询方式如下:

SELECT DOCTOR_WORK.user_label AS "USER_LABEL",
-- COUNT(DOCTOR_WORK.WEEKEND_WORKING),
COUNT(DOCTOR_WORK.CONSULT_ID) AS "PATIENT_COUNT",
count(DISTINCT(DOCTOR_WORK.OFFICE_ID)) AS "OFFICES_DOCTOR_WORKED",
STRING_AGG(CASE 
WHEN (DOCTOR_WORK.OFFICE_ID)=4 THEN 'HQ' 
WHEN (DOCTOR_WORK.OFFICE_ID)=5 THEN 'TCM'
WHEN (DOCTOR_WORK.OFFICE_ID)=6 THEN 'TCD'
WHEN (DOCTOR_WORK.OFFICE_ID)=7 THEN 'SCM'
WHEN (DOCTOR_WORK.OFFICE_ID)=8 THEN 'SSAAC'   
ELSE 'No branch assigned. Check with Admin'
END,',') AS "OFFICE_DOCTOR_WORKED_LIST"   
FROM 
(
SELECT
a.CONSULT_DATE,
YEAR(a.CONSULT_DATE),
MONTHNAME(a.CONSULT_DATE) AS "Month",
DAYNAME(a.entered_date)AS "Day",
CASE 
WHEN a.OFFICE_ID=7 THEN 'WORKING'
WHEN a.OFFICE_ID!=7 THEN 
CASE when DAYNAME(a.consult_date) in ('FRIDAY','SATURDAY') THEN 'WEEKEND' ELSE 'WORKING' END
END AS "WEEKEND_WORKING",
a.CONSULT_ID,
a.PATIENT_NAME,
a.DOCTORS_ID,
b.USER_LABEL,
a.DEPARTMENT_ID,
c.DEPARTMENT_NAME,
a.OFFICE_ID,
d.OFFICE_NAME
FROM
DOCTOR_CONSULT a
INNER JOIN VIEW_USER_SETUP b
ON (a.DOCTORS_ID=b.USER_ID)
INNER JOIN DEPARTMENT_SETUP c
ON (a.DEPARTMENT_ID=c.DEPARTMENT_ID)
INNER JOIN OFFICE_DETAILS d
ON (a.OFFICE_ID=d.OFFICE_ID)
WHERE
a.CONSULT_DATE
BETWEEN '2022-12-30' AND '2023-01-10'
AND
b.ACTIVE_STATUS='Y'
AND 
d.ACTIVE_STATUS='Y'
AND
b.USER_LABEL NOT IN('Emergency Room','General Doctor','General Doctor Oph')
AND b.USER_ID=318  
ORDER BY a.consult_date
) AS "DOCTOR_WORK"
WHERE 
DOCTOR_WORK.WEEKEND_WORKING LIKE 'WORKING'
GROUP BY (DOCTOR_WORK.user_label);

我得到的输出是

USER_LABEL      PATIENT _COUNT      OFFICE_DOCTOR_WORKED      OFFICE_DOCTOR_WORKED_LIST
---------------------------------------------------------------------------------------
Doc A               98                       4                   SCM,SCM,TCM,TCM,.....

上面，第四列将显示基于患者计数的数据。我需要的输出是

USER_LABEL      PATIENT _COUNT      OFFICE_DOCTOR_WORKED      OFFICE_DOCTOR_WORKED_LIST
---------------------------------------------------------------------------------------
Doc A               98                       4                   SCM,TCM,SSAC,HQ

我有问题的sql代码的一部分是

STRING_AGG(CASE 
WHEN (DOCTOR_WORK.OFFICE_ID)=4 THEN 'HQ' 
WHEN (DOCTOR_WORK.OFFICE_ID)=5 THEN 'TCM'
WHEN (DOCTOR_WORK.OFFICE_ID)=6 THEN 'TCD'
WHEN (DOCTOR_WORK.OFFICE_ID)=7 THEN 'SCM'
WHEN (DOCTOR_WORK.OFFICE_ID)=8 THEN 'SSAAC'   
ELSE 'No branch assigned. Check with Admin'
END,',') AS "OFFICE_DOCTOR_WORKED_LIST"   

需要在SQL语句中进行哪些更改。是必须的。如果是这样，它必须如何在上述代码中输入。这是因为我得到错误。错误类型为"语法错误">

Answer 1

我可以用下面的代码解决这个问题:

SELECT DOCTOR_WORK_LIST.USER_LABEL AS "USER_LABEL",
SUM(DOCTOR_WORK_LIST.PATIENT_COUNT) AS "PATIENT_COUNT",
SUM(DOCTOR_WORK_LIST.OFFICES_DOCTOR_WORKED) AS "COUNT_OFFICES_DOCTOR_WORKED",
STRING_AGG(CASE 
WHEN DOCTOR_WORK_LIST.OFFICE_DOCTOR_WORKED_LIST LIKE 'HQ'  THEN 'HeadQuarters-MultiSpeciality' 
WHEN DOCTOR_WORK_LIST.OFFICE_DOCTOR_WORKED_LIST LIKE 'TCM' THEN 'Tahreer Camp-MultiSpeciality'
WHEN DOCTOR_WORK_LIST.OFFICE_DOCTOR_WORKED_LIST LIKE 'TCD' THEN 'Tahreer Camp-Dental'
WHEN DOCTOR_WORK_LIST.OFFICE_DOCTOR_WORKED_LIST LIKE 'SCM' THEN 'Summod Camp-MultiSpeciality'
WHEN DOCTOR_WORK_LIST.OFFICE_DOCTOR_WORKED_LIST LIKE 'SSAAC'THEN 'Sheikh Salem Al Ali Camp'   
ELSE 'No branch assigned. Check with Admin'
END,',  ') AS "OFFICES_DOCTOR_WORKED_LIST"
FROM 
(
SELECT DOCTOR_WORK.user_label AS "USER_LABEL",
COUNT(DOCTOR_WORK.CONSULT_ID) AS "PATIENT_COUNT",
count(DISTINCT(DOCTOR_WORK.OFFICE_ID)) AS "OFFICES_DOCTOR_WORKED",
CASE 
WHEN (DOCTOR_WORK.OFFICE_ID)=4 THEN 'HQ'
WHEN (DOCTOR_WORK.OFFICE_ID)=5 THEN 'TCM'
WHEN (DOCTOR_WORK.OFFICE_ID)=6 THEN 'TCD'
WHEN (DOCTOR_WORK.OFFICE_ID)=7 THEN 'SCM'
WHEN (DOCTOR_WORK.OFFICE_ID)=8 THEN 'SSAAC'   
ELSE 'No branch assigned. Check with Admin'
END AS "OFFICE_DOCTOR_WORKED_LIST"          
FROM 
(
SELECT
a.CONSULT_DATE,
YEAR(a.CONSULT_DATE),
MONTHNAME(a.CONSULT_DATE) AS "Month",
DAYNAME(a.entered_date)AS "Day",
CASE 
WHEN a.OFFICE_ID=7 THEN 'WORKING'
WHEN a.OFFICE_ID!=7 THEN 
CASE when DAYNAME(a.consult_date) in ('FRIDAY','SATURDAY') THEN 'WEEKEND' ELSE 'WORKING' END
END AS "WEEKEND_WORKING",
a.CONSULT_ID,
a.PATIENT_NAME,
a.DOCTORS_ID,
b.USER_LABEL,
a.DEPARTMENT_ID,
c.DEPARTMENT_NAME,
a.OFFICE_ID,
d.OFFICE_NAME
FROM
DOCTOR_CONSULT a
INNER JOIN VIEW_USER_SETUP b
ON (a.DOCTORS_ID=b.USER_ID)
INNER JOIN DEPARTMENT_SETUP c
ON (a.DEPARTMENT_ID=c.DEPARTMENT_ID)
INNER JOIN OFFICE_DETAILS d
ON (a.OFFICE_ID=d.OFFICE_ID)
WHERE
a.CONSULT_DATE
BETWEEN CURRENT_DATE AND CURRENT_DATE
AND
b.ACTIVE_STATUS='Y'
AND 
d.ACTIVE_STATUS='Y'
AND
b.USER_LABEL NOT IN('Emergency Room','General Doctor','General Doctor Oph')
-- AND b.USER_ID=318  
ORDER BY a.consult_date
) AS "DOCTOR_WORK"
WHERE 
DOCTOR_WORK.WEEKEND_WORKING LIKE 'WORKING'
GROUP BY 
DOCTOR_WORK.user_label,
DOCTOR_WORK.OFFICE_ID
) AS "DOCTOR_WORK_LIST"
GROUP BY(DOCTOR_WORK_LIST.USER_LABEl);