Home » SQL & PL/SQL » SQL & PL/SQL » procedure/function to search the field (10.2.0.3.0)
procedure/function to search the field [message #433084] Sun, 29 November 2009 23:00 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I have a table and it contains fields like
FIRST_NAME,LAST_NAME,SAL,DEPTNO.
FIRST_NAME    LAST_NAME    SAL     DEPTNO
------------------------------------------
Thomas        Mathew         900   90
MAthew        robinson      980    80
Thomas        Marteen       890    80
Mathew        Thomas        780    45
...
...

requirement is , I want to "search name"
if I gave Search_Name="Thomas" it should display Thomas related records.
Search_Name="Mathew" it should display Mathew related fields.

Please let me know how to write a Procedure/Function for this req.

Thanks in Adv.
Re: procedure/function to search the field [message #433085 is a reply to message #433084] Sun, 29 November 2009 23:22 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>Messages: 579

WHERE 'Mathew' .... OR 'Thomas' .....
Re: procedure/function to search the field [message #433090 is a reply to message #433084] Sun, 29 November 2009 23:55 Go to previous messageGo to next message
pv_snp
Messages: 11
Registered: July 2007
Location: INDIA
Junior Member

Use pipeline function and using cursor return the record. You can use pipeline function as given below

select * from table (func_name('Mathew'))
Re: procedure/function to search the field [message #433093 is a reply to message #433090] Mon, 30 November 2009 00:20 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>You can use pipeline function as given below
Depending upon exactly who "you" is, this may be a true or false.
You make the call for OP. Wink
Re: procedure/function to search the field [message #433097 is a reply to message #433084] Mon, 30 November 2009 01:12 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Just to clarify. Do you mean that you want to search for the given name in both the first_name column and the last_name column? If yes, then there is absolutely no need for a PL/SQL function, this can easily be achieved with simple SQL.
Can you at least show us what you have tried? Please, do not simply repeat your question (as you tend to have a habit of doing) just post what you have attempted
Re: procedure/function to search the field [message #433116 is a reply to message #433084] Mon, 30 November 2009 03:53 Go to previous messageGo to next message
pv_snp
Messages: 11
Registered: July 2007
Location: INDIA
Junior Member

Try this

select case
when FIRST_NAME='Mathew'
then FIRST_NAME ||',' ||LAST_NAME||',' ||SAL||',' ||DEPTNO
when LAST_NAME='Mathew'
then FIRST_NAME ||',' ||LAST_NAME||',' ||SAL||',' ||DEPTNO
end
from table_name
Re: procedure/function to search the field [message #433118 is a reply to message #433116] Mon, 30 November 2009 04:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you think the original poster needs all data in a single field?
And what do you do with all the NULL values returned? (for all records where neither first_name nor last_name equals the search condition)

[Updated on: Mon, 30 November 2009 04:01]

Report message to a moderator

Re: procedure/function to search the field [message #433120 is a reply to message #433118] Mon, 30 November 2009 04:17 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I'm pretty sure it's only a matter of placing one "OR" in the WHERE clause.

So I fear it will be impossible to solve for the original poster.
Re: procedure/function to search the field [message #433164 is a reply to message #433120] Mon, 30 November 2009 08:02 Go to previous message
joy_division
Messages: 4615
Registered: February 2005
Location: East Coast USA
Senior Member
ThomasG wrote on Mon, 30 November 2009 05:17

So I fear it will be impossible to solve for the original poster.


Hahahahah! Right on!
Previous Topic: Need To Be Displayed NULL COLUMNS (merged 3)
Next Topic: Data Insertion Problem
Goto Forum:
  


Current Time: Sun Sep 25 21:22:54 CDT 2016

Total time taken to generate the page: 0.08707 seconds