asked on

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

cfEngineers

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

slightwv (䄆 Netminder)

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.

POracle

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

slightwv (䄆 Netminder)

ummm.... didn't I already suggest 'or' instead of 'and'?

POracle

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.....

slightwv (䄆 Netminder)

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

sentsg

ASKER

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'

slightwv (䄆 Netminder)

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?

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sentsg

ASKER

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...

slightwv (䄆 Netminder)

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.

sentsg

ASKER

Excellent ....

sentsg

ASKER

Tested your code , i was wrong intially with my data ... now its fine .... thanks