So I'm trying to combine values in each new column that I created in view with a case statement. How would I go about doing this?
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');
I need the new columns Error1 and Error2 to be concatenated as such:
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;
Any help would be greatly appreciated! Thanks in advance.
CodePudding user response:
In SQL Server, you need to use " " to splice characters.
SELECT Patient_UID,
FirstName,
LastName,
ApptStatus,
VisitPosted,
TotalDue,
Appointment_UID,
Error1,
Error2,
Error1 ';' Error2 AS ErrorsCombined
FROM
(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')) A
CodePudding user response:
Convert your query to a common table expression and do the concatenation in the select that references the 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 *,Error1 ';' Error2
FROM cte