How to search exact value in sql

i would like to return the success or failure based on the following search ..
in parameters are fname,lname,email,phone ..  i m fetching the customer_Table like this to find the exact results

i would like to return success if any of three matches

as of now i have split the query and written like this

select count(1) from customer_table where f_name =fname and l_name=lname
and phone_no = phone and e_mail = email;  

select count(1) from cstomer_table where f_name=fname and l_name=lname and phone=no

select  count(1) from cstomer_table where f_name=fname and l_name=lname and e_mail=email .

.....
like this i have writttern five querys to achieve the requirements,..


is there any otherway to get the results
sentsgAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Here's both examples.

Notice the >= 3.  It will also match if all 4 are the same.
drop table tab1 purge;
create table tab1( fname char(1), lname char(1), phone char(1), email char(1));

insert into tab1 values('a','b','c','d');
insert into tab1 values('e','f','g','h');
insert into tab1 values('a','b','g','d');
insert into tab1 values('1','1','1','1');
commit;


create or replace PROCEDURE myproc (
              p_FirstName      IN      VARCHAR2
             ,p_LAstName      IN      VARCHAR2
             ,p_phone      IN      VARCHAR2
             ,P_Email      IN      VARCHAR2
             ,p_Return      OUT      VARCHAR2
      )
is
begin
	select case when count(1) = 0 then 'N' else 'Y' end into p_Return from
	(
	select 
		case when fname=p_FirstName then 1 else 0 end +
		case when lname=p_LastName then 1 else 0 end +
		case when phone=p_phone then 1 else 0 end +
		case when email=p_email then 1 else 0 end
		match_counts
	from tab1
	) where match_counts>=3;
end;
/

show errors

var myval varchar2(1)

exec myproc('a','b','z','d',:myval)
print myval

exec myproc('2','2','2','2',:myval)
print myval



create or replace FUNCTION myfunc (
              p_FirstName      IN      VARCHAR2
             ,p_LAstName      IN      VARCHAR2
             ,p_phone      IN      VARCHAR2
             ,P_Email      IN      VARCHAR2
      ) return varchar2
is
	retval char(1);
begin
	select case when count(1) = 0 then 'N' else 'Y' end into retval from
	(
	select 
		case when fname=p_FirstName then 1 else 0 end +
		case when lname=p_LastName then 1 else 0 end +
		case when phone=p_phone then 1 else 0 end +
		case when email=p_email then 1 else 0 end
		match_counts
	from tab1
	) where match_counts>=3;

	return retval;
end;
/

show errors

var myval varchar2(1)

select myfunc('a','b','z','d') from dual;
select myfunc('2','2','2','2') from dual;

Open in new window

0
 
cfEngineersCommented:

This is a concept, not tested but to give you the general idea

select 
(select count(1) from customer_table where f_name =fname) as fname_match 
,(select count(1) from customer_table where l_name =lname) as lname_match 
,(select count(1) from customer_table where phone_no =phone) as phone_match
,(select count(1) from customer_table where e_mail =email) as email_match

from customer_table


now just check for three matches 
with a query of a query

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Please provide more about the requirements.

I'm thinking you really want OR in the select not AND but I need more information.

sample data and expected results would help a lot.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
POracleCommented:
select count(1) from customer_table where f_name =fname or l_name=lname
or phone_no = phone or e_mail = email;  
0
 
slightwv (䄆 Netminder) Commented:
ummm.... didn't I already suggest 'or' instead of 'and'?
0
 
POracleCommented:

Slightwv:
your message is not clear about which select statement you are talking....
So I just give clear idea to the auther.....

Auther:
Give all the point to Slighwv....if my query satisfy your needs.....
0
 
slightwv (䄆 Netminder) Commented:
I just reread the question.  Both our posts are wrong:  "if any of three matches "

Need to match on any combo on 3 out of the 4 possible?

I would still like more about the parameters and where they come from but try this.

drop table tab1 purge;
create table tab1( fname char(1), lname char(1), phone char(1), email char(1));

insert into tab1 values('a','b','c','d');
insert into tab1 values('e','f','g','h');
insert into tab1 values('a','b','g','d');
insert into tab1 values('z','f','g','h');
commit;


select count(1) from
(
select 
	case when fname='a' then 1 else 0 end +
	case when lname='b' then 1 else 0 end +
	case when phone='c' then 1 else 0 end +
	case when email='d' then 1 else 0 end
	match_counts
from tab1
) where match_counts=3
-- at least three but can include all 4:
-- ) where match_counts>3

/

Open in new window

0
 
sentsgAuthor Commented:
this is the one i have writted to get the resutls

PROCEDURE PRC001      (
              p_FirstName      IN      VARCHAR2
             ,p_LAstName      IN      VARCHAR2
             ,p_phone      IN      VARCHAR2
             ,P_Email      IN      VARCHAR2
             ,p_Return      OUT      VARCHAR2
      )
      As
               ncount            number:=0;
      Begin


            begin
                  Select count(1) into nCount from customer                  
                  where  upper(first_name) = p_FirstName
                  and    upper(last_name) = p_LAstName
                  and    upper(email) = P_Email
                  and    phone1  = p_phone;      
            end;

            if ncount = 0 then
                  begin
                        Select count(1) into nCount from customer
                        where  upper(first_name) = p_FirstName
                        and    upper(last_name) = p_LAstName
                        and    upper(email) = P_Email;
                  end;
            end if;

            if ncount = 0 then
                  begin
                        Select count(1) into nCount from customer
                        where  upper(first_name) = p_FirstName
                        and    upper(last_name) = p_LAstName
                        and    phone1  = p_phone;
                  end;
            end if;      

            if ncount = 0 then
                  begin
                        Select count(1) into nCount from customer
                        where  upper(first_name) = p_FirstName
                        and    upper(email) = P_Email
                        and    phone1  = p_phone;      
                  end;
            end if;

            if ncount = 0 then
                  begin
                        Select count(1) into nCount from customer
                        where  upper(last_name) = p_LAstName
                        and    upper(email) = P_Email
                        and    phone1  = p_phone;      
                  end;
            end if;


            if ncount > 0  then
                  p_Return := 'Y' ;
            else
                  p_Return := 'N' ;
            end if;

end;


in short i should fetch the table and to findout for the same row if any 3 is matching , is so i need to send the return values as 'Y'
0
 
slightwv (䄆 Netminder) Commented:
Can you not add the last code I posted to your procedure?  Just change the hard-coded values in my code with your proc parameters.

Also, ever think of a function instead of a procedure?
0
 
sentsgAuthor Commented:
i execute your previouse code , but it searching the entire table for the one column and returining the results.

ex. case 1 return 100 , case 2 return 34 , case 3 return 0 , case 4 return 23 , but it should be for the same row , table might be having the same value in different rows also .. my criteria is ANY THREE SHOULD MATCH FOR THE SAME ROW ... still im working on with your code...

0
 
slightwv (䄆 Netminder) Commented:
If the example I posted doesn't work, please add to the data to show where it fails and explain why.

This way we can work on the same code to provide a solution.
0
 
sentsgAuthor Commented:
Excellent ....
0
 
sentsgAuthor Commented:
Tested your code , i was wrong intially with my data  ... now its fine .... thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.