[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to search exact value in sql

Posted on 2011-04-27
13
Medium Priority
?
500 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:sentsg
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 6

Expert Comment

by:cfEngineers
ID: 35475731

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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35475821
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
 
LVL 8

Expert Comment

by:POracle
ID: 35476348
select count(1) from customer_table where f_name =fname or l_name=lname
or phone_no = phone or e_mail = email;  
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35476358
ummm.... didn't I already suggest 'or' instead of 'and'?
0
 
LVL 8

Expert Comment

by:POracle
ID: 35476428

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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35476512
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
 

Author Comment

by:sentsg
ID: 35480344
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35480385
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 35480421
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
 

Author Comment

by:sentsg
ID: 35480468
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35480511
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
 

Author Closing Comment

by:sentsg
ID: 35480534
Excellent ....
0
 

Author Comment

by:sentsg
ID: 35480537
Tested your code , i was wrong intially with my data  ... now its fine .... thanks
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month18 days, 2 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question