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

On Apr 6, 1:26 pm, "D" <email0..._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.

ORA10GR2> select * from cust;

    ACCTNO CUSTNO ENDDATE

---------- ---------- ------------------------
         1        100 06-APR-07 00:00:00
         1        100 06-APR-07 00:00:00
         2        200 06-APR-07 00:00:00
         2        200 06-APR-07 00:00:00
         2        200 07-APR-07 00:00:00

ORA10GR2> select * from cust where (acctno,custno) in (select ACCTNO,CUSTNO from cust group by ACCTNO,custno having min(enddate) != max(enddate));

    ACCTNO CUSTNO ENDDATE

---------- ---------- ------------------------
         2        200 06-APR-07 00:00:00
         2        200 06-APR-07 00:00:00
         2        200 07-APR-07 00:00:00

Beware of null enddates though .. i.e. if you have nulls in them

Anurag Received on Fri Apr 06 2007 - 21:07:27 CDT