select distinct (A.CARDID),

                A.CARDNO,

                A.MEMBERID,

                C.MEMBERNO,

                C.MemberName,

                C.IDNO,

                D.cardName,

                A.KEEPAMT,

                C.Point,

                C.STATUS,

                A.AMT,

                A.TOTAMT,

                A.CONSUMAMT,

                A.TICKETAMT,

                A.CREATEDATE,

                A.LASTMODIDATE,

                A.VALIDDATE,

                A.CARDPROP

  from BasCardInfo A

  left join BasMemberInfo C

    on A.MEMBERID = C.MEMBERID, CardType D,

 (select BranCode, cardid, trunc(saledate) as etrunc

          from PosItemH

         where saledate between to_date('2004-12-01', 'yyyy-mm-dd') and

               to_date('2012-12-04', 'yyyy-mm-dd')

           and abs(amt) >= 1) E,

 (select trunc(saledate) as ftrunc, BranCode, cardid, count(1)

          from PosItemH

         where trunc(saledate) between to_date('2004-12-01', 'yyyy-mm-dd') and

               to_date('2012-12-04', 'yyyy-mm-dd')

         group by trunc(saledate), cardid, brancode

        having 1 = 1 and count(1) > 1 and brancode in (select BranCode

                                                      &nbs