select
p.firstname || ' ' || DECODE(p.middlename,NULL,'', p.middlename || ' ') || p.lastname || DECODE(p.namesuffix,NULL,'',' ' || p.namesuffix) as fullname,
p.uppersearchname,
p.salutation,
p.nameprefix,
p.firstname,
p.middlename,
p.lastname,
p.namesuffix,
pl.pledgeamount,
pl.pledgedate,
pt.pledgetypedesc,
pt.pledgecategory,
sn.displayname as sponsorname,
sn.uppersearchname as sponsorupper,
ap.appealdesc,
yr.yeardesc,
yr.yearsort,
p.deceased,
pn.excludefrommailings,
ad.addressline1,
ad.addressline2,
ad.city,
ad.state,
ad.zip,
ph.phonenumber,
ph.phoneext,
em.emailaddress,
p.userchar1,
p.userchar2,
p.userchar3,
p.userchar4,
p.userchar5
from
campeople p,
campledges pl,
campledgetypes pt,
camsponsors sp,
camnamesv sn,
camappeals ap,
camyears yr,
camnames pn,
camnameaddresses na,
camaddresses ad,
camphones ph,
camemails em
where
p.nameid = pl.nameid
and pl.pledgetypeid = pt.pledgetypeid
and pl.sponsorid = sp.sponsorid (+)
and NVL(sp.nameid,0) = sn.nameid (+)
and pl.appealid = ap.appealid
and ap.yearid = yr.yearid
and p.nameid = pn.nameid
and p.nameid = na.nameid (+)
and NVL(na.primaryaddress,'Y') = 'Y'
and NVL(na.addressid,0) = ad.addressid (+)
and p.nameid = ph.nameid (+)
and NVL(ph.primaryphone,'Y') = 'Y'
and p.nameid = em.nameid (+)
and NVL(em.primaryemail,'Y') = 'Y'
and p.nameid IN
(Select camGroupMembers.NameID
From camGroupMembers
Where camGroupMembers.GroupID In
(Select camGroups.GroupID
From camGroups
Start With camGroups.GroupDesc = 'Anonymous'
Connect By Prior camGroups.GroupID = camGroups.ParentGroupID))
and pl.pledgedate between '01-JAN-2005' and '31-DEC-2005'