Home » SQL & PL/SQL » SQL & PL/SQL » need to find out column value.. (oracle 9i)
need to find out column value.. [message #439746] Tue, 19 January 2010 05:28 Go to next message
abhi_shakya
Messages: 50
Registered: September 2009
Location: Nepal
Member
Table 1

id code value
1 ab12 expire
2 ab12 expire
3 ab34 active
4 ab34 expire
5 ab35 expire



This is my table structure...i need too return those 'code'
which have corresponding all the 'Value' column data as 'expire'.
If any one is 'active' then the query shud not return that code....

please help me out soon...
Re: need to find out column value.. [message #439748 is a reply to message #439746] Tue, 19 January 2010 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's an example to find all department where all employees have no commission:
SQL> select deptno from emp group by deptno having count(decode(comm,null,1)) = count(*);
    DEPTNO
----------
        20
        10

2 rows selected.

If you want an example with your table and data you have to post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: need to find out column value.. [message #439749 is a reply to message #439746] Tue, 19 January 2010 05:32 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
put your where clause condition value='expire'
whats hurting you here?

sriram Smile
Re: need to find out column value.. [message #439750 is a reply to message #439746] Tue, 19 January 2010 05:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There are many ways to do it.

You could select all the codes where the min(value) = 'expire'

You could select all the codes with a value of 'expire' MINUS a select of all the codes with a value of 'active'

You could use a NOT EXISTS or a NOT IN sub query to reject codes with a 'active' value.
Re: need to find out column value.. [message #439751 is a reply to message #439749] Tue, 19 January 2010 05:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
put your where clause condition value='expire'

The OP is looking for codes where all the values of column VALUE = 'expire'.
Re: need to find out column value.. [message #439755 is a reply to message #439751] Tue, 19 January 2010 05:48 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Let me take coffee
i am totally miss read that

sriram Smile
Previous Topic: sql query
Next Topic: How to merge more columns into a single row in Oracle? (merged)
Goto Forum:
  


Current Time: Fri Dec 09 15:13:57 CST 2016

Total time taken to generate the page: 0.20566 seconds