CTE query and OpenQuery mix

_{Wednesday, March 6, 2013 4:42 AM}

Hi,

I am running the following query, works fine.

I have tried to run this as a OPENQUERY, i am now thinking that you cannot run a CTE through the OPENQUERY, if someone can offer advise or maybe i need to look at this a different way

thanks

:)

I get lots of errors.

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'END'.
Msg 102, Level 15, State 1, Line 48
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 70
Incorrect syntax near ''.
Msg 105, Level 15, State 1, Line 75
Unclosed quotation mark after the character string ')'.

ET @SQLString = 'SELECT * FROM OPENQUERY
([HCPW], '' With CTE AS
(
Select     DimLocation.HospitalCode, DimPatient.URNumber, FactWaitingList.WaitingListId, FactWaitingList.ReferralId, FactWaitingList.AppointmentByDateTime, 
                      FactWaitingList.OperatingHospitalName, FactWaitingList.TotalWaitingDays,
                      CASE WHEN DimReference.ReferenceDescription = ''''Did Not Attend'''' THEN DATEDIFF(day, FactWaitingList.WaitingStartDateTime, 
                      FactOutpatientsAppointment.AppointmentStartDateTime) ELSE '''''' END AS ''''DNAnumberDay'''',
                      FactWaitingList.PreviousSuspTotalDays, FactWaitingList.CurrentSuspTotalDays, 
                      FactWaitingList.WaitingStartDateTime, FactOutpatientsAppointment.AppointmentStartDateTime, DimReference.ReferenceDescription AS AppointmentOutcome, 
                      DimLocation.LocationDescription,ROW_NUMBER() OVER(Partition by DimPatient.URnumber ORDER BY AppointmentStartDateTime ASC) as RN,
                      FactOutpatientsAppointment.CalculatedAppointmentOutcome, Cby.ReferenceDescription AS PatCancel
FROM         FactWaitingList INNER JOIN
                      DimLocation ON FactWaitingList.AppointmentClinicLocationId = DimLocation.LocationId INNER JOIN
                      DimPatient ON FactWaitingList.PatientId = DimPatient.PatientId LEFT OUTER JOIN
                      FactOutpatientsAppointment ON FactWaitingList.PatientId = FactOutpatientsAppointment.PatientId AND 
                      FactWaitingList.AppointmentClinicLocationId = FactOutpatientsAppointment.AppointmentClinicLocationId AND 
                      FactOutpatientsAppointment.AppointmentStartDateTime >= FactWaitingList.WaitingStartDateTime LEFT OUTER JOIN
                      DimReference ON FactOutpatientsAppointment.AppointmentOutcomeRefId = DimReference.ReferenceId 
                      LEFT OUTER JOIN DimReference as Cby ON FactOutpatientsAppointment.AppointmentCancelReasonRefId = Cby.ReferenceId
                      INNER JOIN  PIC.view_WaitingList_All AS WLA ON FactWaitingList.WaitingListId = WLA.WaitingListId
WHERE     (FactWaitingList.WaitingListType = ''''OutpatientService'''') AND (FactWaitingList.RemovalDateTime IS NULL) AND (DimLocation.HospitalCode = ''''2101'''') AND 
                      (FactWaitingList.ArchiveFlag = 0)  AND 
                      (FactWaitingList.WaitingStartDateTime >= ''''2007-07-01 00:00:00.000'''')
),
DidNotAttend AS
(
Select [HospitalCode]
      ,[URNumber]
      ,[WaitingListId]
      ,[ReferralId]
      ,[AppointmentByDateTime]
      ,[OperatingHospitalName]
      ,[TotalWaitingDays]
      ,[DNAnumberDay]
      ,[PreviousSuspTotalDays]
      ,[CurrentSuspTotalDays]
      ,[WaitingStartDateTime]
      ,[AppointmentStartDateTime]
      ,[AppointmentOutcome] 
      ,[LocationDescription]
      ,CalculatedAppointmentOutcome
    ,PatCancel
MAX(Case When RN = 1 then AppointmentStartDateTime END) AS FirstAppDidNotAttend
From CTE
Where RN IN(1) 
Group By [HospitalCode]
      ,[URNumber]
      ,[WaitingListId]
      ,[ReferralId]
      ,[AppointmentByDateTime]
      ,[OperatingHospitalName]
      ,[TotalWaitingDays]
      ,[DNAnumberDay]
      ,[PreviousSuspTotalDays]
      ,[CurrentSuspTotalDays]
      ,[WaitingStartDateTime]
      ,[AppointmentStartDateTime]
      ,[AppointmentOutcome] 
      ,[LocationDescription]
      ,CalculatedAppointmentOutcome
      ,PatCancel
)
Select *,
''''WaitingDayTotal'''' = CASE WHEN DNANumberDay < TotalWaitingDays THEN DNANumberDay END
FROM         DidNotAttend 
'')';

David

_{Wednesday, March 6, 2013 5:51 AM ✅Answered}

try to print your SQL String before actually executing it to debug it...which i did and then see what it the real problem.

Note the below SQL I got it after Print (@SQLString) and I haven't corrected it...

SELECT * FROM OPENQUERY
([HCPW], ' With CTE AS
(
Select     DimLocation.HospitalCode, DimPatient.URNumber, FactWaitingList.WaitingListId, FactWaitingList.ReferralId, FactWaitingList.AppointmentByDateTime, 
                      FactWaitingList.OperatingHospitalName, FactWaitingList.TotalWaitingDays,
                      CASE WHEN DimReference.ReferenceDescription = ''Did Not Attend'' THEN DATEDIFF(day, FactWaitingList.WaitingStartDateTime, 
                      FactOutpatientsAppointment.AppointmentStartDateTime) ELSE ''' END AS ''DNAnumberDay'',
                      FactWaitingList.PreviousSuspTotalDays, FactWaitingList.CurrentSuspTotalDays, 
                      FactWaitingList.WaitingStartDateTime, FactOutpatientsAppointment.AppointmentStartDateTime, DimReference.ReferenceDescription AS AppointmentOutcome, 
                      DimLocation.LocationDescription,ROW_NUMBER() OVER(Partition by DimPatient.URnumber ORDER BY AppointmentStartDateTime ASC) as RN,
                      FactOutpatientsAppointment.CalculatedAppointmentOutcome, Cby.ReferenceDescription AS PatCancel
FROM         FactWaitingList INNER JOIN
                      DimLocation ON FactWaitingList.AppointmentClinicLocationId = DimLocation.LocationId INNER JOIN
                      DimPatient ON FactWaitingList.PatientId = DimPatient.PatientId LEFT OUTER JOIN
                      FactOutpatientsAppointment ON FactWaitingList.PatientId = FactOutpatientsAppointment.PatientId AND 
                      FactWaitingList.AppointmentClinicLocationId = FactOutpatientsAppointment.AppointmentClinicLocationId AND 
                      FactOutpatientsAppointment.AppointmentStartDateTime >= FactWaitingList.WaitingStartDateTime LEFT OUTER JOIN
                      DimReference ON FactOutpatientsAppointment.AppointmentOutcomeRefId = DimReference.ReferenceId 
                      LEFT OUTER JOIN DimReference as Cby ON FactOutpatientsAppointment.AppointmentCancelReasonRefId = Cby.ReferenceId
                      INNER JOIN  PIC.view_WaitingList_All AS WLA ON FactWaitingList.WaitingListId = WLA.WaitingListId
WHERE     (FactWaitingList.WaitingListType = ''OutpatientService'') AND (FactWaitingList.RemovalDateTime IS NULL) AND (DimLocation.HospitalCode = ''2101'') AND 
                      (FactWaitingList.ArchiveFlag = 0)  AND 
                      (FactWaitingList.WaitingStartDateTime >= ''2007-07-01 00:00:00.000'')
),
DidNotAttend AS
(
Select [HospitalCode]
      ,[URNumber]
      ,[WaitingListId]
      ,[ReferralId]
      ,[AppointmentByDateTime]
      ,[OperatingHospitalName]
      ,[TotalWaitingDays]
      ,[DNAnumberDay]
      ,[PreviousSuspTotalDays]
      ,[CurrentSuspTotalDays]
      ,[WaitingStartDateTime]
      ,[AppointmentStartDateTime]
      ,[AppointmentOutcome] 
      ,[LocationDescription]
      ,CalculatedAppointmentOutcome
    ,PatCancel
MAX(Case When RN = 1 then AppointmentStartDateTime END) AS FirstAppDidNotAttend
From CTE
Where RN IN(1) 
Group By [HospitalCode]
      ,[URNumber]
      ,[WaitingListId]
      ,[ReferralId]
      ,[AppointmentByDateTime]
      ,[OperatingHospitalName]
      ,[TotalWaitingDays]
      ,[DNAnumberDay]
      ,[PreviousSuspTotalDays]
      ,[CurrentSuspTotalDays]
      ,[WaitingStartDateTime]
      ,[AppointmentStartDateTime]
      ,[AppointmentOutcome] 
      ,[LocationDescription]
      ,CalculatedAppointmentOutcome
      ,PatCancel
)
Select *,
''WaitingDayTotal'' = CASE WHEN DNANumberDay < TotalWaitingDays THEN DNANumberDay END
FROM DidNotAttend 
')

Thanks and regards, Rishabh K

_{Thursday, March 7, 2013 1:02 AM ✅Answered}

As Rishabh K suggests print @SQLString and past the result into a new window - you should then be able to see you are missing a ' after the ELSE

_{Thursday, March 7, 2013 12:01 AM}

Hi Rishabh K

Sorry i am not sure what has happend,

I had 'SELECT * FROM OPENQUERY
([HCPW], '' With CTE AS

Where ''With has 2 ''

The script you have sent back only has 1 '

Thanks

David