Home » SQL & PL/SQL » SQL & PL/SQL » Column search based on column value (Oracle 10g OS:Windows XP)
Column search based on column value [message #431690] Thu, 19 November 2009 02:44 Go to next message
manirocks
Messages: 65
Registered: October 2007
Member
Hello

Do anybody know how to find a column name when column value is known.

For Example if in a table the column value is 'HOUSEKEEPING' then
i need to know what is the column name.

Is it possible to know????

-- Manikanth
Re: Column search based on column value [message #431692 is a reply to message #431690] Thu, 19 November 2009 02:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Short answer - no.

You could write a query to do a pivot on your data and return each row as a series of attribute/value pairs where one column is the column name, and the other is the value, and then you could look through that list to see the data you wanted, but in a table of any reasonable size, it would be slow.

What are you trying to achieve?
Re: Column search based on column value [message #431699 is a reply to message #431690] Thu, 19 November 2009 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "find_string" in AskTom site.

Regards
Michel
Re: Column search based on column value [message #431707 is a reply to message #431690] Thu, 19 November 2009 04:01 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
you can write your own procedure/function .

i.e.


SQL> exec SEARCH_COL('EMP','KING','VARCHAR2');

PL/SQL procedure successfully completed.

SQL> desc search_db_results;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RESULT                                             VARCHAR2(256)

SQL> select * from search_db_results;

RESULT
--------------------------------------------------------------------------------
EMP.ENAME

SQL> set line 180
SQL> select * from emp;

    SRIRAM ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    50
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

    SRIRAM ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> exec SEARCH_COL('EMP','7902','NUMBER');

PL/SQL procedure successfully completed.

SQL> select * from search_db_results;

RESULT
---------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------
EMP.ENAME
EMP.SRIRAM
EMP.MGR



Sriram Smile
Re: Column search based on column value [message #431721 is a reply to message #431707] Thu, 19 November 2009 04:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How on earth is this helpful?
Showing off? For all we can say, you just copied aforementioned code from Tom Kyte and renamed the procedure...
Re: Column search based on column value [message #431727 is a reply to message #431721] Thu, 19 November 2009 05:19 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Frank wrote on Thu, 19 November 2009 04:22
How on earth is this helpful?
Showing off? For all we can say, you just copied aforementioned code from Tom Kyte and renamed the procedure...


Frank,Thank for your valuable reply.....

Even you can go through this and especially for my reply...

http://www.orafaq.com/forum/m/394027/136607/#msg_394027

And here
SQL> select object_name||','|| to_char(CREATED,'dd-mon-yyyy-hh12:mi:ss:am')
  2  from obj
  3  where OBJECT_NAME like 'SEARCH_COL';

OBJECT_NAME||','||TO_CHAR(CREATED,'DD-MON-YYYY-HH12:MI:SS:AM')
-------------------------------------------------------------------------------------------
--
SEARCH_COL,22-oct-2009-11:57:41:am

SQL> select object_name||','|| to_char(CREATED,'dd-mon-yyyy-hh12:mi:ss:am')
  2  from obj
  3  where OBJECT_NAME like 'SEARCH_DB_RESULTS';

OBJECT_NAME||','||TO_CHAR(CREATED,'DD-MON-YYYY-HH12:MI:SS:AM')
-------------------------------------------------------------------------------------------
--
SEARCH_DB_RESULTS,22-oct-2009-11:57:35:am

SQL>
one more for you....


sriram Smile


Re: Column search based on column value [message #431729 is a reply to message #431727] Thu, 19 November 2009 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And then?
What does it change to the answer? You copy the code from another one (which may have copy R. Kyte's one).
Just the link to your previous post would be a correct and useful answer.

Regards
Michel
Re: Column search based on column value [message #431748 is a reply to message #431729] Thu, 19 November 2009 06:20 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
You copy the code from another one


Yes i agree...On my previous post i posted the link....Here Last month i modified that for my use,as the op asked for the same kind of solution i posted the results....
Yes i even copied (learned)
select * from employees;
from Oracle documents....

If my post like "COPYCAT" i cannot do anything...Just be calm Sad And to be frank i never serched for "find string" of asktom.And its not my intension to say that the code is mine....

And As you said
Quote:
Just the link to your previous post would be a correct and useful answer.


I will follow.

sriram

[Updated on: Thu, 19 November 2009 06:24]

Report message to a moderator

Re: Column search based on column value [message #431750 is a reply to message #431748] Thu, 19 November 2009 06:23 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If my post like "COPYCAT" i cannot do anything...Just be calm And to be frank i never serched for "find string" of asktom

The only problem with your post is that you just showed that you can do something without giving any help to OP.
As I said you could just give the link to your previous post; that would be enough and useful.

Regards
Michel
Previous Topic: Session Status
Next Topic: End of line & End of File
Goto Forum:
  


Current Time: Tue Dec 06 14:35:51 CST 2016

Total time taken to generate the page: 0.08882 seconds