Geek Answers Handbook

Error creating view using CTE

I think I'm stupid, but I get an error:

Msg 195, level 15, state 1, procedure VW_THIRDPARTY_SLA_REPORT_MONTHLY_GP_NONAGGREGATE, line 8 'partitioned' is not a recognized option.

when trying to execute the following view statement operator

CREATE VIEW [dbo].[VW_THIRDPARTY_SLA_REPORT_MONTHLY_GP_NONAGGREGATE] With partitioned AS (Select B.MSH7_DateTimeOfMessage, B.PID2x1_PatientIDExternal, B.PID3x1_PatientIDInternal, B.PID5x1_PatientName_FamilyName, B.PV3x2_AssignedPatientLocation_Room, A.OBR4x2_UniversalServiceID_Text, A.OBX3x2_ObservationIdentifier_Text, A.OBR24_DiagnosticServiceSectionID, A.OBR6_RequestDateTime, C.TestName, C.PriceBaseline, D.Contract, Row_NUMBER() OVER(Partition By [ORC3_FillerOrderNumber], [OBX3x2_ObservationIdentifier_Text] order by [ORC9_DateTimeOfTransaction]) as seq From [NWLHPathApp_DataWarehouse].[dbo].[PathologyHL7_Detail] A LEFT OUTER JOIN [NWLHPathApp_DataWarehouse].[dbo].[PathologyHL7_Header] B ON A.[DETAIL_ID] = B.[HEADER_ID] LEFT OUTER JOIN [NWLHPathApp_DataWarehouse].[dbo].[PathologyHL7_View_TFCData] C ON A.[OBR24_DiagnosticServiceSectionID] + A.[OBX3x1_ObservationIdentifier_Identifier] = C.[KEY] LEFT OUTER JOIN [NWLHPathApp_DataWarehouse].[dbo].[PathologyHL7_LocationDetail] D ON B.[PV3x1_AssignedPatientLocation_PointOfCare] = D.[PracticeCode] ) Select * from partitioned where seq =1

This is a query that works well in a stored procedure, so I'm satisfied with the query, I just can’t create it as a view.

Any help would be greatly appreciated

+7
source share
3 answers

You are missing the first AS after CREATE VIEW :

 CREATE VIEW [dbo].[VW_THIRDPARTY_SLA_REPORT_MONTHLY_GP_NONAGGREGATE] AS --- this is missing With partitioned AS ( Select B.MSH7_DateTimeOfMessage, B.PID2x1_PatientIDExternal, B.PID3x1_PatientIDInternal, B.PID5x1_PatientName_FamilyName, B.PV3x2_AssignedPatientLocation_Room, A.OBR4x2_UniversalServiceID_Text, A.OBX3x2_ObservationIdentifier_Text, A.OBR24_DiagnosticServiceSectionID, A.OBR6_RequestDateTime, C.TestName, C.PriceBaseline, D.Contract, Row_NUMBER() OVER(Partition By [ORC3_FillerOrderNumber], [OBX3x2_ObservationIdentifier_Text] order by [ORC9_DateTimeOfTransaction]) as seq From [NWLHPathApp_DataWarehouse].[dbo].[PathologyHL7_Detail] A LEFT OUTER JOIN [NWLHPathApp_DataWarehouse].[dbo].[PathologyHL7_Header] B ON A.[DETAIL_ID] = B.[HEADER_ID] LEFT OUTER JOIN [NWLHPathApp_DataWarehouse].[dbo].[PathologyHL7_View_TFCData] C ON A.[OBR24_DiagnosticServiceSectionID] + A.[OBX3x1_ObservationIdentifier_Identifier] = C.[KEY] LEFT OUTER JOIN [NWLHPathApp_DataWarehouse].[dbo].[PathologyHL7_LocationDetail] D ON B.[PV3x1_AssignedPatientLocation_PointOfCare] = D.[PracticeCode] ) Select * from partitioned where seq =1
+11
source

You need AS after CREATE VIEW :

 CREATE VIEW [dbo].[VW_THIRDPARTY_SLA_REPORT_MONTHLY_GP_NONAGGREGATE] AS With partitioned ...
+5
source

It may be useful to point out that you would seemingly not be able to add view column headers while using CTE. (it took a few minutes of my time)

 create or replace force editionable view "MYVIEW" --("Field1", "Field2") as with results as ( select cal.field1,cal.field2 from mytable cal ) select field1,field2 from results ;

if you uncomment the column declaration ("Field1", "Field2") above, this will fail.

0
source

More articles:


All Articles