Home » SQL & PL/SQL » SQL & PL/SQL » general query required
general query required [message #227323] Wed, 28 March 2007 00:42 Go to next message
deepak samal
Messages: 29
Registered: February 2005
Junior Member
I have table called Customer
Customer

Cust_ID, Cust_Type
------- ---------
1 AGENT
2 DIRECT
3 LENDER


I have Customer Details

Customer_Detaail

ID Cust_ID Status
-- ------- ------
1 1 ACTIVE
2 1 PENDING
3 1 DECLINED
4 2 PENDING
5 2 DECLINED
6 2 DECLINED
7 3 DECLINED
8 3 DECLINED
9 3 DECLINED

ACTIVE > PENDING > DECLINED
Join customer with customer Details, if find row for "ACTIVE" then status is active
it is "PENDING" if it is Not ACTIVE
it is "DECLINED" if it is not ACTIVE or PENDING
if row not found ignore it

Please send me a query to return below result..
Don't use Co-related sub queries

OUTPUT
cust_ID status
------- --------
1 ACTIVE
2 PENDING
3 DECLINED
Re: general query required [message #227326 is a reply to message #227323] Wed, 28 March 2007 00:54 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Please send me a query to return below result..
>Don't use Co-related sub queries
Please send me $500 dollars USD
Don't send in bills greater than $20.

Re: general query required [message #227341 is a reply to message #227323] Wed, 28 March 2007 01:27 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
You can add a lookup table status_code with 2 columns id, value and insert the following rows

id value
1 ACTIVE
2 PENDING
3 DECLINED

and the query is :>>

select p.cust_id, q.value
from
(
select a.cust_id, min(b.id) id
from Customer_Detaail a, status_code b
where a.status = b.value
group by a.cust_id
) p, status_code q
where p.id = q.id
Re: general query required [message #227356 is a reply to message #227323] Wed, 28 March 2007 01:42 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As "ACTIVE > PENDING > DECLINED"

Use "decode(status,'DECLINED',1,'PENDING',2,'ACTIVE',3)"

and group by this.

Regards
Michel
Previous Topic: ORA-00936: missing expression
Next Topic: limited input choices for fields
Goto Forum:
  


Current Time: Fri Dec 02 14:01:33 CST 2016

Total time taken to generate the page: 0.13056 seconds