Re: How to return records with mismatching criteria in the same column

On 6 Apr 2007 10:26:05 -0700, "D" <email0203_at_comcast.net> wrote:

>Hello everyone. I have result set that I am pulling that shows a
>customer may have a different end date on multiple records. Is there a
>way to compare the values in the column and say if account number and
>customer and end date match, do not return values - but if account
>number and customer match but end date does not match all records for
>that account number and customer return all values for that customer?
>here is what I have so far and what I'm looking to do...
>
>SELECT /*+ first_rows(10) */ c.ACCTCORP, c.HOUSE, c.CUST, c.lname,
>c.fname, hsa.SVC_AVL_TYPE, cp.promocode, cp.stopdate
>FROM IDST_CUSTOMER c, IDST_HOUSE_SERVICE_AVLBLTY hsa, IDST_CUST_PROMO
>cp
>WHERE c.acctcorp=hsa.acctcorp AND c.house=hsa.house AND
>c.acctcorp=cp.acctcorp AND c.house=cp.house AND c.cust=cp.cust
>AND c.acctcorp=9518 AND hsa.svc_avl_type=59 AND cp.promocode IN ('!
>X','<3','<8','<G','<N','<O','<P','<Q','<R','<S','>9','CM','CN','CS','CT','CY','H>','N<','O<','P<','U4','U5','U6','U7','U8','U9','UB','UC','UD','UE','UF','UG')
>ORDER BY c.acctcorp ASC, c.house ASC
>
>
>THE RESULTS I'M LOOKING TO RETURN...
>9518 100256 6 PEARSON JAMES 59 !X 1/25/2008
>9518 100256 6 PEARSON JAMES 59 UF 1/25/2008
>9518 100256 6 PEARSON JAMES 59 CS 2/27/2008
>
>The first two values match but the 3rd is different...I want to
>display all three records for this type of record set only.

Your approach seems to imply (can't verify the original results against the intended results as you left the former out) you want to suppress records already returned.
This is a violation of the second commandment: Thou shallst not do procedurally what can be done non-procedurally.

-- 
Sybrand Bakker
Senior Oracle DBA