Sorry to bother everyone with what should be a trivial question, but I need to import an SQL query into SSRS's Report builder, and then turn it into an expression (so that I can use its concatenation operator to join values of a parameter into search strings).
As a first stage, I copied and pasted my query (which I know works) into SSRS, and it ran smoothly. However, as soon as I put the expression in quotes, and prefixed the whole thing with "=", I get some very unhelpful syntax error messages.
This is my (correctly functioning) query before importing into SSRS:
DECLARE @From Date='1/1/2015'
DECLARE @To Date='1/3/2020'
DECLARE @Doctors varchar(5)='ABC'
DECLARE @Intents varchar(20)='RoutineRad-Cat2'
DECLARE @DiagnosisTypes varchar(20)='ICD-10'
SET transaction isolation level read uncommitted;
SELECT
x.Expression1 as 'Intent',
x.IntentCategory,
x.StartDateTime as 'Ready To Start Treatment Date',
x.LastName as 'Surname',
x.PatientId as 'Patient ID',
x.DoctorId as 'Primary Oncologist',
x.TreatmentStartTime as 'First RT Treatment Date',
datediff(dd, x.StartDateTime, x.TreatmentStartTime) as 'Wait in days',
x.TargetMet,
x.Delayed,
x.TxStartSortDate
FROM (VALUES('C50%'),('D05%'),('C61%'))
AS v (pattern)
CROSS APPLY
(
SELECT
lut.Expression1,
p.PatientId,
p.LastName,
c.StartDateTime,
d.DoctorId,
rh.TreatmentStartTime,
CASE
WHEN Expression1 like '%Rad%' THEN 'Radical'
WHEN Expression1 like '%Pall%' THEN 'Palliative'
WHEN Expression1 = 'Emergency' THEN 'Emergency'
ELSE 'Other'
END
as 'IntentCategory',
CASE WHEN Expression1 like '%Delay%' THEN 1 ELSE 0 END as 'Delayed',
CASE WHEN
(
(Expression1 like '%Rad%' AND datediff(dd, c.StartDateTime, FirstRadiationHstry.TreatmentStartTime) <= 28)
OR (Expression1 like '%Palliative%' AND datediff(dd, c.StartDateTime, FirstRadiationHstry.TreatmentStartTime) <= 14)
OR (Expression1 = 'Emergency' AND datediff(dd, c.StartDateTime, FirstRadiationHstry.TreatmentStartTime) <= 1)
--catch any exceptions
OR ((Expression1 not like '%Rad%') AND (Expression1 not like '%Palliative%') AND (Expression1!='Emergency'))
) THEN 1 ELSE 0 END as 'TargetMet',
FORMAT(rh.TreatmentStartTime,'yyyyMM') as 'TxStartSortDate',
FirstRadiationHstry.TreatmentStartTime as 'MinTreatStart',
datediff(dd, c.StartDateTime, rh.TreatmentStartTime) as 'TreatDelay'
FROM
dbo.Patient p
inner join Course c on (c.PatientSer = p.PatientSer)
inner join CourseDiagnosis cd on (cd.CourseSer=c.CourseSer)
inner join Diagnosis diag on (diag.DiagnosisSer=cd.DiagnosisSer)
inner join PatientDoctor pd on (pd.PatientSer = p.PatientSer)
inner join Doctor d on (d.ResourceSer = pd.ResourceSer)
inner join dbo.PhysicianIntent phi on (phi.CourseSer = c.CourseSer)
inner join dbo.LookupTable lut on (lut.LookupValue = phi.TreatmentIntentType and lut.ListSelector = 'COURSE_INTENT')
inner join dbo.PlanSetup ps on (ps.CourseSer = c.CourseSer)
inner join dbo.Radiation r on (r.PlanSetupSer = ps.PlanSetupSer)
inner join dbo.RadiationHstry rh on (rh.RadiationSer = r.RadiationSer)
inner join (
select distinct
min(rh_sub.TreatmentStartTime) over (partition by ps_sub.CourseSer) as 'TreatmentStartTime',
ps_sub.CourseSer
from
RadiationHstry rh_sub
inner join dbo.Radiation r_sub on (r_sub.RadiationSer = rh_sub.RadiationSer)
inner join dbo.PlanSetup ps_sub on (r_sub.PlanSetupSer = ps_sub.PlanSetupSer)
where
rh_sub.TreatmentDeliveryType like 'TREATMENT'
) as FirstRadiationHstry on (FirstRadiationHstry.CourseSer = c.CourseSer)
WHERE
datediff(dd, @From, rh.TreatmentStartTime) >= 0
and datediff(dd, rh.TreatmentStartTime, @To)>=0
and diag.DiagnosisCode like v.pattern
and d.OncologistFlag=1
and d.DoctorId in (@Doctors)
and rh.TreatmentStartTime = FirstRadiationHstry.TreatmentStartTime
and lut.Expression1 in (@Intents)
and diag.DiagnosisTableName in(@DiagnosisTypes)
and lut.Expression1 not like 'Dummy Course'
) AS x
;
...and this is it after:
="SET transaction isolation level read uncommitted;
SELECT
x.Expression1 as 'Intent',
x.IntentCategory,
x.StartDateTime as 'Ready To Start Treatment Date',
x.LastName as 'Surname',
x.PatientId as 'Patient ID',
x.DoctorId as 'Primary Oncologist',
x.TreatmentStartTime as 'First RT Treatment Date',
datediff(dd, x.StartDateTime, x.TreatmentStartTime) as 'Wait in days',
x.TargetMet,
x.Delayed,
x.TxStartSortDate
FROM (VALUES('C50%'),('D05%'),('C61%'))
AS v (pattern)
CROSS APPLY
(
SELECT
lut.Expression1,
p.PatientId,
p.LastName,
c.StartDateTime,
d.DoctorId,
rh.TreatmentStartTime,
CASE
WHEN Expression1 like '%Rad%' THEN 'Radical'
WHEN Expression1 like '%Pall%' THEN 'Palliative'
WHEN Expression1 = 'Emergency' THEN 'Emergency'
ELSE 'Other'
END
as 'IntentCategory',
CASE WHEN Expression1 like '%Delay%' THEN 1 ELSE 0 END as 'Delayed',
CASE WHEN
(
(Expression1 like '%Rad%' AND datediff(dd, c.StartDateTime, FirstRadiationHstry.TreatmentStartTime) <= 28)
OR (Expression1 like '%Palliative%' AND datediff(dd, c.StartDateTime, FirstRadiationHstry.TreatmentStartTime) <= 14)
OR (Expression1 = 'Emergency' AND datediff(dd, c.StartDateTime, FirstRadiationHstry.TreatmentStartTime) <= 1)
--catch any exceptions
OR ((Expression1 not like '%Rad%') AND (Expression1 not like '%Palliative%') AND (Expression1!='Emergency'))
) THEN 1 ELSE 0 END as 'TargetMet',
FORMAT(rh.TreatmentStartTime,'yyyyMM') as 'TxStartSortDate',
FirstRadiationHstry.TreatmentStartTime as 'MinTreatStart',
datediff(dd, c.StartDateTime, rh.TreatmentStartTime) as 'TreatDelay'
FROM
dbo.Patient p
inner join Course c on (c.PatientSer = p.PatientSer)
inner join CourseDiagnosis cd on (cd.CourseSer=c.CourseSer)
inner join Diagnosis diag on (diag.DiagnosisSer=cd.DiagnosisSer)
inner join PatientDoctor pd on (pd.PatientSer = p.PatientSer)
inner join Doctor d on (d.ResourceSer = pd.ResourceSer)
inner join dbo.PhysicianIntent phi on (phi.CourseSer = c.CourseSer)
inner join dbo.LookupTable lut on (lut.LookupValue = phi.TreatmentIntentType and lut.ListSelector = 'COURSE_INTENT')
inner join dbo.PlanSetup ps on (ps.CourseSer = c.CourseSer)
inner join dbo.Radiation r on (r.PlanSetupSer = ps.PlanSetupSer)
inner join dbo.RadiationHstry rh on (rh.RadiationSer = r.RadiationSer)
inner join (
select distinct
min(rh_sub.TreatmentStartTime) over (partition by ps_sub.CourseSer) as 'TreatmentStartTime',
ps_sub.CourseSer
from
RadiationHstry rh_sub
inner join dbo.Radiation r_sub on (r_sub.RadiationSer = rh_sub.RadiationSer)
inner join dbo.PlanSetup ps_sub on (r_sub.PlanSetupSer = ps_sub.PlanSetupSer)
where
rh_sub.TreatmentDeliveryType like 'TREATMENT'
) as FirstRadiationHstry on (FirstRadiationHstry.CourseSer = c.CourseSer)
WHERE
datediff(dd, @From, rh.TreatmentStartTime) >= 0
and datediff(dd, rh.TreatmentStartTime, @To)>=0
and diag.DiagnosisCode like v.pattern
and d.OncologistFlag=1
and d.DoctorId in (@Doctors)
and rh.TreatmentStartTime = FirstRadiationHstry.TreatmentStartTime
and lut.Expression1 in (@Intents)
and diag.DiagnosisTableName in(@DiagnosisTypes)
and lut.Expression1 not like 'Dummy Course'
) AS x
;"
...but I just get the error message "Incorrect syntax near the keyword 'VALUES'. Incorrect syntax near ')'."
I have no idea how to go about trying to iron this out, as I know my SQL is fine. I've tried importing the query into Scite and looked at the line endings / special characters, but this looks OK as well.
Can someone please help me?
Best wishes
C J
