Where clause - knowing what part results matched on - possible?

[David Smithz]

Hi,

If you run a query which has a WHERE statement in which has a few
possibilities separated OR statements, e.g.

Select * from table where (Afield = 2) OR (Bfield = 2) OR (Cfield = 2)

In the returned results is it possible to know what particular part of the
WHERE clause a field matched.

For example, if the above query returned 6 rows, I would want to be able to
tell know if the first result matched on Afield and Cfield but not Bfield.
Then see that row 2 matched on Bfield only etc.

Is this possible?

Thanks in advance.

David

[Gordon Burditt]

>If you run a query which has a WHERE statement in which has a few

possibilities separated OR statements, e.g.

Select * from table where (Afield = 2) OR (Bfield = 2) OR (Cfield = 2)

In the returned results is it possible to know what particular part of the
WHERE clause a field matched.
Yes. Look at the results returned and evaluate the conditions of
the WHERE clause in a programming language.
For example, if the above query returned 6 rows, I would want to be able to
tell know if the first result matched on Afield and Cfield but not Bfield.
Then see that row 2 matched on Bfield only etc.

You can also select additional data to be returned with the record
using an arbitrary expression, like:

if(afield = 2, 1, 0) as matchedona,
if(bfield = 2, 1, 0) as matchedonb,
if(cfield = 2, 1, 0) as matchedonc

Gordon L. Burditt

[onedbguru]

Now for the ANSI standard version of the query.

select Afield,Bfield,Cfield, -- get the values of these fields
case when Afield=2 then 1 else 0 end as matchedonA,
case when Bfield=2 then 1 else 0 end as matchedonB,
case when Cfield=2 then 1 else 0 end as matchedonC,
other,
fields
from .....
where.....

1 and 0 can be 'A' and NULL or 'A' and 'Z' or. or or.....

It is a bit more wordy, but will work on MySQL, Oracle RDBMS(8,9,10),
Oracle Rdb, DB2, Informix, Sybase, and yes, even SQLServer (I think???)