Repeater with Custom Query where condition lenght limited

mohammad ali jodeiri asked on November 3, 2019 10:18

I have a Repeater with Custom Query. Currently, there's a WHERE Condition like this PracticeAreas = (Custom_BusinessUnit.BusinessUnitId IN (SELECT BusinessUnitId FROM Custom_BusinessUnit AS Custom_BusinessUnit_3 WHERE (ParentBusinessUnitId IN (SELECT BusinessUnitId FROM Custom_BusinessUnit AS Custom_BusinessUnit_2 WHERE (ParentBusinessUnitId IN (SELECT BusinessUnitId FROM Custom_BusinessUnit AS Custom_BusinessUnit_1 WHERE (ParentBusinessUnitId = @bu) OR (BusinessUnitId = @bu))) OR (ParentBusinessUnitId = @bu) OR (BusinessUnitId = @bu))) OR (ParentBusinessUnitId = @bu) OR (BusinessUnitId = @bu))) AND (CMS_User.UserEnabled = '1') AND (CMS_User.UserType = 1) AND (custom_certificate.DevelopManagerFlag = 1) AND (custom_certificate.AssistanceDevelopFlag = 1)

WHERE condition item , Cannot insert due to field length limit. please help me

repeater with custom quer

Correct Answer

Juraj Ondrus answered on November 4, 2019 11:51

Or, you can extend the field length in the repeater's properties as shown on this screen shot.

1 votesVote for this answer Unmark Correct answer

Recent Answers

Peter Mogilnitski answered on November 3, 2019 16:08

You have to refactor your query and use inner joins instead of nested queries. Try to use shorter aliases as well for ex instead of AS Custom_BusinessUnit_2 use AS cbu2.

1 votesVote for this answer Mark as a Correct answer

mohammad ali jodeiri answered on November 4, 2019 09:20

Declare @Bu nvarchar(36); SET @Bu='84b7a65c-3e43-e511-80ca-005056af12ea' SELECT CertificateID, CertDate, asia_personalcode AS prscode, FullName AS prsname, custom_CertificateCourses.CourseName, Courselength, custom_certificateTrainingCenter.TrainingName FROM Custom_BusinessUnit RIGHT OUTER JOIN CMS_User ON Custom_BusinessUnit.BusinessUnitId = CMS_User.BusinessUnitId RIGHT OUTER JOIN custom_certificateTrainingCenter RIGHT OUTER JOIN custom_certificate ON custom_certificateTrainingCenter.CertificateTrainingCenterID = custom_certificate.Trainingname LEFT OUTER JOIN custom_CertificateCourses ON custom_certificate.CourseName = custom_CertificateCourses.CertificateCoursesID ON CMS_User.UserID = custom_certificate.prscode WHERE (Custom_BusinessUnit.BusinessUnitId IN (SELECT BusinessUnitId FROM Custom_BusinessUnit AS Custom_BusinessUnit_3 WHERE (ParentBusinessUnitId IN (SELECT BusinessUnitId FROM Custom_BusinessUnit AS Custom_BusinessUnit_2 WHERE (ParentBusinessUnitId IN (SELECT BusinessUnitId FROM Custom_BusinessUnit AS Custom_BusinessUnit_1 WHERE (ParentBusinessUnitId = @bu) OR (BusinessUnitId = @bu))) OR (ParentBusinessUnitId = @bu) OR (BusinessUnitId = @bu))) OR (ParentBusinessUnitId = @bu) OR (BusinessUnitId = @bu))) AND(UserEnabled = '1') AND (UserType = 1) AND (DevelopManagerFlag = 1) AND (AssistanceDevelopFlag = 1) ORDER BY prscode this is my query pls help me

0 votesVote for this answer Mark as a Correct answer