Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 3 June 2016

Cannot update the view or function 'xxxxxx' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator also check VIEW WITH Check option and schema binding Methods

Cannot update the view or function 'xxxxxx' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator  &

Also check VIEW WITH Check option and schema binding Methods

let see sample table


let create two view


CREATE VIEW PatientViewOptionA
as
SELECT PatientName,PatientAddress FROM Patients
WHERE (EXISTS (select PatientID from Prescriptions
WHERE (Patients.PatientID=PatientID)) AND EXISTS
(select PatientID from Visits WHERE (Patients.PatientID=PatientID)))

Create view patientviewOptionB
AS
select PatientName,PatientAddress
FROM (select Distinct PatientID,PatientName,PatientAddress
FROM Patients
WHERE (EXISTS(SELECT PrescriptionID,PrescriptionText,PatientID
FROM Prescriptions
where (Patients.PatientID=PatientID))
OR EXISTS (select VisitID,VisitReason,PatientID FROM Visits
where (Patients.PatientID=PatientID))))
PatientData


 If we update using OPTION B of view you get error due to it uses Distinct keyword in code

Update patientviewOptionB set PatientAddress='TUP' WHERE PatientName='Patient A'

Msg 4403, Level 16, State 1, Line 1
Cannot update the view or function 'patientviewOptionB' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.


but if you use view option A You can able to update bcos it did nt use aggregates,pivot/unpivot,distinct keyword in code

Update PatientViewOptionA set PatientAddress='TUP' WHERE PatientName='Patient T'

VIEW WITH CHECK OPTION and WITH SCHEMABINDING  Methods

Another Example to need to update only particular records in table using view see below

You need to create view that allow users to modify only the customers records of customers in Maine



CREATE TABLE ViewTestCustomer(CustomerID int Primary Key,FirstName Varchar(50),LastName Varchar(50),Address1 Varchar(100),City Varchar(50),
State Varchar(25),ZipCode Char(10))


Insert into ViewTestCustomer
SELECT 7894,'Besant','Nagar','Chennai Beach','Chennai','TAMIL NADU','600025'
union all
SELECT 8695,'Maine','Falls','London falls','Maine','UK','100025'
union all
SELECT 4178,'SAINT','KULS','Australia street','Sydney','AUS','760025'

SELECT * FROM ViewTestCustomer





When you try to create view as below code you will get following error

Msg 1054, Level 15, State 6, Procedure vwMaine, Line 7
Syntax '*' is not allowed in schema-bound objects.

CREATE VIEW vwMaine

WITH SCHEMABINDING

AS

select * from ViewTestCustomer
where State='Maine'

WITH CHECK OPTION


Create unique CLUSTERED INDEX ix_CITY
ON vwMaine(City)


So you have to mention all column name write it separtely also you have to follow four part names
like dbo.schema name other wise you will get error as see below'

Msg 4512, Level 16, State 3, Procedure vwMaine, Line 7
Cannot schema bind view 'vwMaine' because name 'ViewTestCustomer' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.



So after follow schema name rule kindly see below


 After Created Indexed view you can able to create unique clustered index on view as see below


 Thats it Thanks