Dynamic Select query issue
-
Hi , I had some problem with my select query.Let me explain you whole. I had created dynamic query in my select query as i required performance. My Select query is as below: Select firstname,lastname,dob,ssn from person where ( firstname like 'abc%' and lastname like 'xyz%' OR firstname like 'ypx%' and lastname like 'por%' OR firstname like 'stup%' and lastname like 'rob%' ) Now here i required one more field which is not available in database table (person table). Now that field is MatchType.Matchtype is used for If user input of firstname and lastname is exactly matching with database record then i have to write matchtype ='Exact Match' if user input of first 3 character of firstname and lastname is matching then i have to write Matchtype ='Potiential Match' Now problem is that : If i will write subquery like Select firstname,lastname,dob,ssn, ( Select //some login which return Match type ) as Matchtype from person where ( firstname like 'abc%' and lastname like 'xyz%' OR firstname like 'ypx%' and lastname like 'por%' OR firstname like 'stup%' and lastname like 'rob%' ) If i can't write query like that becoz subquery should return only one value. so can somebody help me out here.?? If you have any question then let me know without hesitation. Thankx a lot in advance. regd, montu3377
-
Hi , I had some problem with my select query.Let me explain you whole. I had created dynamic query in my select query as i required performance. My Select query is as below: Select firstname,lastname,dob,ssn from person where ( firstname like 'abc%' and lastname like 'xyz%' OR firstname like 'ypx%' and lastname like 'por%' OR firstname like 'stup%' and lastname like 'rob%' ) Now here i required one more field which is not available in database table (person table). Now that field is MatchType.Matchtype is used for If user input of firstname and lastname is exactly matching with database record then i have to write matchtype ='Exact Match' if user input of first 3 character of firstname and lastname is matching then i have to write Matchtype ='Potiential Match' Now problem is that : If i will write subquery like Select firstname,lastname,dob,ssn, ( Select //some login which return Match type ) as Matchtype from person where ( firstname like 'abc%' and lastname like 'xyz%' OR firstname like 'ypx%' and lastname like 'por%' OR firstname like 'stup%' and lastname like 'rob%' ) If i can't write query like that becoz subquery should return only one value. so can somebody help me out here.?? If you have any question then let me know without hesitation. Thankx a lot in advance. regd, montu3377
I'm not sure if I totally understand your requirements but would the following work. Select firstname ,lastname ,dob ,ssn ,case when (firstName = 'abc' and lastname ='xyz') or (firstname ='ypx' and lastname ='por') or (firstname ='stup' and lastname ='rob') then 'Exact Match' else 'Potential Match' end as [MatchType] from person where ( firstname like 'abc%' and lastname like 'xyz%' OR firstname like 'ypx%' and lastname like 'por%' OR firstname like 'stup%' and lastname like 'rob%' )