Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: query needed very urg.
peter wrote:
> Hi all,
> Please tell me query for following conditions:
>
> Conditions are:
>
> 1) a=12, b=3, n=7
> 2) a=12, b=3, n=NULL
> 3) a=12, b=NULL, n=NULL
>
> if 1 is true, I don't need any record of 2 or 3. But if it is false
> then
> I need all record of 2, but if 1 and 2 both are wrong then only I need
> 3.
>
I did something similar with UNION (I had five selects). The query would be something like
SELECT attrs FROM tables
WHERE a=12 AND b=3 AND n=7
UNION
SELECT attrs FROM tables
WHERE a=12 AND b=3 AND n IS NULL
UNION
SELECT attrs FROM tables
a=12 AND b IS NULL AND n IS NULL
You might like to check that the query works by adding and extra field that tells from which select the rows come
SELECT 1 AS casenumber, attrs FROM tables
WHERE a=12 AND b=3 AND n=7
UNION
SELECT 2 AS casenumber, attrs FROM tables
WHERE a=12 AND b=3 AND n IS NULL
UNION
SELECT 3 AS casenumber, attrs FROM tables
a=12 AND b IS NULL AND n IS NULL
So if you get a row in casenumber 2 and it should be in 1, fix the query.
-- Arto Viitanen, CSC ltd Espoo, FinlandReceived on Fri Dec 15 2006 - 00:30:25 CST