连接 SQL 案例表达式

一尘不染

连接 SQL 案例表达式

sql

因此，我试图将我在视图中创建的每个新列中的值与 case 语句结合起来。我该怎么做呢？

SELECT
    PI.Patient_UID, PI.FirstName, PI.LastName, 
    AP.ApptStatus, AP.VisitPosted, CD.TotalDue,
    AP.Appointment_UID,
    CASE 
        WHEN AP.VisitPosted = 0 
            THEN 'Visit Posted Error' 
    END Error1,
    CASE 
        WHEN (CD.TotalDue IS NULL OR CD.TotalDue = '')  
            THEN 'Gross Charge Error' 
    END Error2
FROM
    vw_ODBC_pt_PatientInfo AS PI
INNER JOIN
    vw_ODBC_appts_Appointments AS AP ON AP.PatientFID = PI.Patient_UID
INNER JOIN
    vw_ODBC_actv_ChargeDetail AS CD ON CD.PatientFID = PI.Patient_UID 
WHERE
    AP.ApptStatus NOT IN ('10', '11', '12');

我需要新列Error1并Error2像这样连接：

Error1                Error2               ErrorsCombined
Visit Posted Error    Gross Charge Error    Visit Posted Error;Gross Charge Error
Visit Posted Error    NULL                  Visit Posted Error;
Visit Posted Error    NULL                  Visit Posted Error;
Visit Posted Error    NULL                  Visit Posted Error;

任何帮助将不胜感激！提前致谢。

阅读 71

2022-07-21

一尘不染

对and表达式使用APPLY运算符。利用concat_ws()执行带有分隔符的字符串连接并处理值。Error1``Error2``NULL

select PI.Patient_UID, 
       PI.FirstName, 
       PI.LastName, 
       AP.ApptStatus, 
       AP.VisitPosted, 
       CD.TotalDue,
       AP.Appointment_UID,
       e.Error1,
       e.Error2,
       concat_ws(';', e.Error1, e.Error2) as ErrorsCombined
from  vw_ODBC_pt_PatientInfo as PI
inner join vw_ODBC_appts_Appointments as AP on AP.PatientFID = PI.Patient_UID
inner join vw_ODBC_actv_ChargeDetail  as CD on CD.PatientFID = PI.Patient_UID 
cross apply
(
    select case when AP.VisitPosted = 0 
                then 'Visit Posted Error' 
                end as Error1,
           case when (CD.TotalDue is null or CD.TotalDue = '') 
                then 'Gross Charge Error' 
                end as Error2
) e
where AP.ApptStatus not in ('10','11','12');

2022-07-21

一尘不染

将您的查询转换为公用表表达式，并在引用 cte 的选择中进行连接。

;with cte AS (
select  PI.Patient_UID, PI.FirstName, PI.LastName, AP.ApptStatus, AP.VisitPosted, CD.TotalDue, AP.Appointment_UID,
case when AP.VisitPosted = 0 then 'Visit Posted Error' end Error1,
case when (CD.TotalDue is null or CD.TotalDue = '') then 'Gross Charge Error' end Error2
from vw_ODBC_pt_PatientInfo as PI
inner join vw_ODBC_appts_Appointments as AP on AP.PatientFID = PI.Patient_UID
inner join vw_ODBC_actv_ChargeDetail as CD on CD.PatientFID = PI.Patient_UID 
where AP.ApptStatus not in ('10','11','12')
)

SELECT *,COALESCE(Error1+';','')+COALESCE(Error2,'') as ErrorsCombined
FROM cte

2022-07-21

连接 SQL 案例表达式

共2个答案