My client requirements are such that I m going to generate query runtime in asp.net.
At some time the query reaches to very much length as below
Expand|Select|Wrap|Line Numbers
- SELECT tblJobPost.JobTitle,tblJobPost.JobDesc,Scraped,tblSchools.logoimage,tblJobPost.JobPostID,tblJobPost.SchoolID,tblSchools.web,cast(tblJobPost.MemType as nvarchar(255)) as MemType,tblSchools.InstitutionName,tblSchools.InstitutionName as Empname,tblJobPost.PayScale,cast(tblLEA.LEA as nvarchar(255)) as LEA,tblContract.ContractType,tblHoursAbl.HoursAbl,cast(tblJobPost.StartDate as nvarchar(255)) as StartDate, cast(tblJobPost.ClosingDate as nvarchar(255)) as ClosingDate,cast(tblJobPost.asap as nvarchar(50)) as asap, cast(tblJobPost.postdate as nvarchar(50)) as postdate FROM tblJobPost LEFT Outer JOIN tblpositions ON tblJobPost.position_id = tblpositions.position_id LEFT OUTER JOIN tblMainsubjects ON tblJobPost.SubjectID = tblMainsubjects.MSubjectID LEFT OUTER JOIN tblSchoolPhase ON tblJobPost.SchoolPhaseID = tblSchoolPhase.SchoolPhaseID LEFT OUTER JOIN tblLEA ON tblJobPost.LeaID = tblLEA.LeaID LEFT OUTER JOIN tblHoursAbl ON tblJobPost.HoursID = tblHoursAbl.HoursAblID LEFT OUTER JOIN tblContract ON tblJobPost.ContractID = tblContract.ContractID LEFT OUTER JOIN tblregion ON tblJobPost.Region_id = tblregion.RegionID RIGHT OUTER JOIN tblSchools ON tblJobPost.SchoolID = tblSchools.SchoolID Where (tblregion.region = 'London') and (tblSchoolPhase.SchoolPhase = 'Secondary Education') and (tblpositions.Position_title = 'Classroom Teacher')and (tblMainSubjects.MSubject = 'Science')and (tblLEA.LEA = 'Croydon') and (tblJobPost.ClosingDate >= '3/30/2009 11:14:49 PM')
- Union
- Select tblJobScrap.JobTitle,tblJobScrap.JobDesc,tblJobScrap.Scraped,tblSchools.logoimage,tblJobScrap.JobPostID,tblJobScrap.SchoolID,tblSchools.web,tblSchools.MemType,tblSchools.InstitutionName,tblJobScrap.InstitutionName as Empname,tblJobScrap.PayScale,tblJobScrap.LEA,tblJobScrap.Contract as ContractType,tblJobScrap.HoursAbl,tblJobScrap.StartDate,tblJobScrap.ClosingDate,tblJobScrap.asap,tblJobScrap.postdate from tblJobScrap LEFT OUTER JOIN tblSchools ON tblJobScrap.SchoolID = tblSchools.SchoolID Where (((Region like '%London%' OR Region like 'London %' OR Region like '% London'))) AND NOT (((JobTitle like '%Primary%' OR JobTitle like 'Primary %' OR JobTitle like '% Primary') OR (JobTitle like '%Infant%' OR JobTitle like 'Infant %' OR JobTitle like '% Infant') OR (JobTitle like '%Junior%' OR JobTitle like 'Junior %' OR JobTitle like '% Junior') OR (JobTitle like '%Early%' OR JobTitle like 'Early %' OR JobTitle like '% Early') OR (JobTitle like '%Elementary%' OR JobTitle like 'Elementary %' OR JobTitle like '% Elementary') OR (JobTitle like '%Special%' OR JobTitle like 'Special %' OR JobTitle like '% Special') OR (JobTitle like '%University%' OR JobTitle like 'University %' OR JobTitle like '% University') OR (JobTitle like '%Lecturer%' OR JobTitle like 'Lecturer %' OR JobTitle like '% Lecturer') OR (JobTitle like '%tutor%' OR JobTitle like 'tutor %' OR JobTitle like '% tutor') OR (JobTitle like '%Adult%' OR JobTitle like 'Adult %' OR JobTitle like '% Adult') OR (JobTitle like '%Nursery%' OR JobTitle like 'Nursery %' OR JobTitle like '% Nursery') OR (JobTitle like '%Middle%' OR JobTitle like 'Middle %' OR JobTitle like '% Middle') OR (JobTitle like '%Multicultural%' OR JobTitle like 'Multicultural %' OR JobTitle like '% Multicultural') OR (JobTitle like '%Key Stage 1%' OR JobTitle like 'Key Stage 1 %' OR JobTitle like '% Key Stage 1') OR (JobTitle like '%Key Stage 2%' OR JobTitle like 'Key Stage 2 %' OR JobTitle like '% Key Stage 2') OR (JobTitle like '%KS1%' OR JobTitle like 'KS1 %' OR JobTitle like '% KS1') OR (JobTitle like '%KS2%' OR JobTitle like 'KS2 %' OR JobTitle like '% KS2') OR (JobTitle like '%KS 1%' OR JobTitle like 'KS 1 %' OR JobTitle like '% KS 1') OR (JobTitle like '%KS 2%' OR JobTitle like 'KS 2 %' OR JobTitle like '% KS 2') OR (JobTitle like '%Year 1%' OR JobTitle like 'Year 1 %' OR JobTitle like '% Year 1') OR (JobTitle like '%Year 2%' OR JobTitle like 'Year 2 %' OR JobTitle like '% Year 2') OR (JobTitle like '%Year 3%' OR JobTitle like 'Year 3 %' OR JobTitle like '% Year 3') OR (JobTitle like '%Year 4%' OR JobTitle like 'Year 4 %' OR JobTitle like '% Year 4') OR (JobTitle like '%Year 5%' OR JobTitle like 'Year 5 %' OR JobTitle like '% Year 5') OR (JobTitle like '%Year 6%' OR JobTitle like 'Year 6 %' OR JobTitle like '% Year 6') OR (JobTitle like '%social worker%' OR JobTitle like 'social worker %' OR JobTitle like '% social worker')) OR ((tblJobScrap.institutionName like '%Primary%' OR tblJobScrap.institutionName like 'Primary %' OR tblJobScrap.institutionName like '% Primary') OR (tblJobScrap.institutionName like '%Infant%' OR tblJobScrap.institutionName like 'Infant %' OR tblJobScrap.institutionName like '% Infant') OR (tblJobScrap.institutionName like '%Junior%' OR tblJobScrap.institutionName like 'Junior %' OR tblJobScrap.institutionName like '% Junior') OR (tblJobScrap.institutionName like '%Early%' OR tblJobScrap.institutionName like 'Early %' OR tblJobScrap.institutionName like '% Early') OR (tblJobScrap.institutionName like '%Elementary%' OR tblJobScrap.institutionName like 'Elementary %' OR tblJobScrap.institutionName like '% Elementary') OR (tblJobScrap.institutionName like '%Special%' OR tblJobScrap.institutionName like 'Special %' OR tblJobScrap.institutionName like '% Special') OR (tblJobScrap.institutionName like '%University%' OR tblJobScrap.institutionName like 'University %' OR tblJobScrap.institutionName like '% University') OR (tblJobScrap.institutionName like '%Lecturer%' OR tblJobScrap.institutionName like 'Lecturer %' OR tblJobScrap.institutionName like '% Lecturer') OR (tblJobScrap.institutionName like '%tutor%' OR tblJobScrap.institutionName like 'tutor %' OR tblJobScrap.institutionName like '% tutor') OR (tblJobScrap.institutionName like '%Adult%' OR tblJobScrap.institutionName like 'Adult %' OR tblJobScrap.institutionName like '% Adult') OR (tblJobScrap.institutionName like '%Nursery%' OR tblJobScrap.institutionName like 'Nursery %' OR tblJobScrap.institutionName like '% Nursery') OR (tblJobScrap.institutionName like '%Middle%' OR tblJobScrap.institutionName like 'Middle %' OR tblJobScrap.institutionName like '% Middle') OR (tblJobScrap.institutionName like '%Multicultural%' OR tblJobScrap.institutionName like 'Multicultural %' OR tblJobScrap.institutionName like '% Multicultural') OR (tblJobScrap.institutionName like '%Key Stage 1%' OR tblJobScrap.institutionName like 'Key Stage 1 %' OR tblJobScrap.institutionName like '% Key Stage 1') OR (tblJobScrap.institutionName like '%Key Stage 2%' OR tblJobScrap.institutionName like 'Key Stage 2 %' OR tblJobScrap.institutionName like '% Key Stage 2') OR (tblJobScrap.institutionName like '%KS1%' OR tblJobScrap.institutionName like 'KS1 %' OR tblJobScrap.institutionName like '% KS1') OR (tblJobScrap.institutionName like '%KS2%' OR tblJobScrap.institutionName like 'KS2 %' OR tblJobScrap.institutionName like '% KS2') OR (tblJobScrap.institutionName like '%KS 1%' OR tblJobScrap.institutionName like 'KS 1 %' OR tblJobScrap.institutionName like '% KS 1') OR (tblJobScrap.institutionName like '%KS 2%' OR tblJobScrap.institutionName like 'KS 2 %' OR tblJobScrap.institutionName like '% KS 2') OR (tblJobScrap.institutionName like '%Year 1%' OR tblJobScrap.institutionName like 'Year 1 %' OR tblJobScrap.institutionName like '% Year 1') OR (tblJobScrap.institutionName like '%Year 2%' OR tblJobScrap.institutionName like 'Year 2 %' OR tblJobScrap.institutionName like '% Year 2') OR (tblJobScrap.institutionName like '%Year 3%' OR tblJobScrap.institutionName like 'Year 3 %' OR tblJobScrap.institutionName like '% Year 3') OR (tblJobScrap.institutionName like '%Year 4%' OR tblJobScrap.institutionName like 'Year 4 %' OR tblJobScrap.institutionName like '% Year 4') OR (tblJobScrap.institutionName like '%Year 5%' OR tblJobScrap.institutionName like 'Year 5 %' OR tblJobScrap.institutionName like '% Year 5') OR (tblJobScrap.institutionName like '%Year 6%' OR tblJobScrap.institutionName like 'Year 6 %' OR tblJobScrap.institutionName like '% Year 6') OR (tblJobScrap.institutionName like '%social worker%' OR tblJobScrap.institutionName like 'social worker %' OR tblJobScrap.institutionName like '% social worker')))and (((JobTitle like '%Classroom Teacher%' OR JobTitle like 'Classroom Teacher %' OR JobTitle like '% Classroom Teacher') OR (JobTitle like '%teacher%' OR JobTitle like 'teacher %' OR JobTitle like '% teacher') OR (JobTitle like '%ordinator%' OR JobTitle like 'ordinator %' OR JobTitle like '% ordinator') OR (JobTitle like '%position%' OR JobTitle like 'position %' OR JobTitle like '% position'))) AND NOT (((JobTitle like '%Headteacher%' OR JobTitle like 'Headteacher %' OR JobTitle like '% Headteacher') OR (JobTitle like '%Head teacher%' OR JobTitle like 'Head teacher %' OR JobTitle like '% Head teacher') OR (JobTitle like '%Head%' OR JobTitle like 'Head %' OR JobTitle like '% Head') OR (JobTitle like '%lecturer%' OR JobTitle like 'lecturer %' OR JobTitle like '% lecturer') OR (JobTitle like '%assistant%' OR JobTitle like 'assistant %' OR JobTitle like '% assistant') OR (JobTitle like '%support%' OR JobTitle like 'support %' OR JobTitle like '% support')))and (((JobTitle like '%Science%' OR JobTitle like 'Science %' OR JobTitle like '% Science') OR (JobTitle like '%Chemistry%' OR JobTitle like 'Chemistry %' OR JobTitle like '% Chemistry') OR (JobTitle like '%Physics%' OR JobTitle like 'Physics %' OR JobTitle like '% Physics') OR (JobTitle like '%Biology%' OR JobTitle like 'Biology %' OR JobTitle like '% Biology')))AND (((LEA like '%Croydon%' OR LEA like 'Croydon %' OR LEA like '% Croydon')))
Very very Thanks for sugessions and recomondations.