DISTINCT and Case Statements

[bdragoo]

I'm working on a report that has a distinct clause, but also a Case statement. The Case statement splits a field into two separate responses, basically making it appear twice, instead of once.

Is there any way to define the ELSE in the Case statement so the ELSE just doesn't show up? I've tried ELSE <NULL>, but that just makes the field appear blank, rather than taking it out of the results as a whole.

Or

Is there any way to match DISTINCT to a specific table/field result, rather than all the results as a whole?

I'm stumped!

 

[PHV]

Why not posting some input samples, your actual SQL code and result vs expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bdragoo]

here's what I have so far
SELECT DISTINCT
A.COMPANY
A.NOTES
B.PHONE
CASE WHEN C.TYPE = 'T' AND C.PRIMARY <> 'I' THEN (C.POLICY#) ELSE (NULL) END AS [POLICY]

Then I have some Joins, Where Clauses, and some Order by statements.

The problem is this: I want the distinct to return only distinct records, but the Case statement returns a clients results with either the C.Policy# OR as a Null value. Naturally, the DISTINCT clause sees this as two different clients (one with a policy# attached, one with a null value). I'm trying to get it so the clients with the NULL value simply dont show up.

Does that help?

 

[PHV]

Why not simply this ?
SELECT DISTINCT A.COMPANY, A.NOTES, B.PHONE, (C.POLICY#) AS [POLICY]
FROM ...
WHERE C.TYPE = 'T' AND C.PRIMARY <> 'I'
...


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886