| Returning two rows [message #370815] |
Thu, 10 February 2000 15:10  |
Rajeah patil
Messages: 1
Registered: February 2000
|
Junior Member |
|
|
I have a table 'cust' with customer_id,ship_id
having 2 rows foe e.g.
customer_id ship_id
----------- -------
3 null
3 5
3 6
If my sql is
select customer_id,ship_id
from cust
where customer_id=3
and ship_id=6
If ship_id=6 row does't exist then i should get
customer_id with 3 and ship_id with null;
If i try to use
select customer_id,ship_id
from cust
where (customer_id=3 and ship_id is null)
or (customer_id=3 and ship_id=6);
it return 2 rows. I want 1 row if bith cust/ship
matches else match ship_id with null cust_id with value
|
|
|
|
|
|
| Re: Returning two rows [message #370822 is a reply to message #370815] |
Fri, 11 February 2000 04:37  |
Thierry Van der Auwera
Messages: 44
Registered: January 2000
|
Member |
|
|
Hallo,
You can do this on following way :
SELECT a.cust,a.ship
FROM tab_cust a
WHERE a.cust = 3
AND NVL(a.ship,-1) = (SELECT MAX(NVL(b.ship,-1))
FROM tab_cust b
WHERE a.cust = b.cust);
This will return : 3 6
When 6 not exists : 3 5
When 5 not exists : 3
If only want for ship to allow the 6 and the NULL then execute following sql:
SELECT a.cust,a.ship
FROM temp_bbb a
WHERE a.cust = 3
AND NVL(a.ship,-1) = (SELECT MAX(NVL(b.ship,-1))
FROM temp_bbb b
WHERE a.cust = b.cust
AND NVL(b.ship,-1) IN (6,-1));
Hope this solves your problem.
(Let me know if it does)
Greetings,
Thierry.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
