| Using IF-THEN-ELSE in an UPDATE Statement [message #8768] |
Wed, 24 September 2003 13:11  |
denise
Messages: 64
Registered: November 2000
|
Member |
|
|
I have the attached code that is based on a donor
having (at least) 1 gift record in the gift table.
We have since learned that we have donors who
have been imported to the donor table from outside
sources that do not have a gift record to date.
The attached script needs to evaluate both conditions.
(at this point it evaluates only 1 condition, that is
having a gift record)...what would be the most effective way to allow for "exists and not exists"
in the select statement regarding the gift table.
thanks,
Denise
******************************************************
update mailing m
set m.appealcode = '&hndApp'
where m.idnumber between Low and Curr
and m.maxgift is null and m.maxgiftdate is null
and exists(select 1 from prospect p
where p.idnumber=m.idnumber and p.usercode2='H' and p.usercode1='ACGA')
and exists(select g.idnumber from gift g
where g.idnumber=m.idnumber
nd g.giftamount = 0
group by g.idnumber
having max(g.giftdate) between add_months(sysdate,-8) and add_months(sysdate, -4));
hNd := hNd + SQL%ROWCOUNT;
UpdCnt := UpdCnt + SQL%ROWCOUNT;
|
|
|
|
| Re: Using IF-THEN-ELSE in an UPDATE Statement [message #8769 is a reply to message #8768] |
Wed, 24 September 2003 14:01  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
I'm not sure why the last part of your statement isn't just:
and exists (select g.idnumber
from gift g
where g.idnumber = m.idnumber
and g.giftamount = 0
and g.giftdate between add_months(sysdate, -8) and add_months(sysdate, -4));
But that said, it is hard to answer your question without knowing what you want to have happen when there is no existing gift with the specified criteria...
|
|
|
|
| Re: Using IF-THEN-ELSE in an UPDATE Statement [message #8770 is a reply to message #8769] |
Wed, 24 September 2003 14:38 |
denise
Messages: 64
Registered: November 2000
|
Member |
|
|
exactly Todd...the current "exists" statement uses
the last giftdate from the gift table if a record
exists.
If no gift record exists then we want to use the
inception date(userdate2) from the donor table
to then select:
ADD_Months between(userdate2,-8) and ADD_Months(userdate2,-4)
|
|
|
|
| Re: Using IF-THEN-ELSE in an UPDATE Statement [message #8771 is a reply to message #8770] |
Wed, 24 September 2003 15:54 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
OK, I understand your MAX usage now, but not your clarification.
You are updating the appealcode column in mailing, but only if here is a qualified prospect row and the last gift ($0?) is between 4 and 8 months ago.
Is the inception date a fallback to the gift check? Like, update the appealcode if:
1) qualified prospect row
and
2) qualified gift row (last gift in date range)
OR
3) qualified donor row
If you could tell the logic in pseudocode, it might help here.
|
|
|
|
| Re: Using IF-THEN-ELSE in an UPDATE Statement [message #8781 is a reply to message #8771] |
Thu, 25 September 2003 07:02 |
denise
Messages: 64
Registered: November 2000
|
Member |
|
|
thanks Todd....
here is the psuedo code:
update mailing
set appealcode from user input
if exists a gift record using max(giftdate) where
giftamount = 0 and max(giftdate) between 4 to 8 months.
If no gift record exists then
update mailing
set appealcode from user input
and use the inception
date(date donor was imported to donor table with no gift record)
where inception date(userdate2) between 4 to 8 months.
hope this provides some clarity
denise
|
|
|
|
| Using IF-THEN-ELSE in an UPDATE Statement Further Clarification [message #8782 is a reply to message #8781] |
Thu, 25 September 2003 07:09 |
denise
Messages: 64
Registered: November 2000
|
Member |
|
|
just so you understand the giftamount=0
we have NonDonors with gift records and NonDonors
with no gift records.
NonDonors may have gift records spanning years in which
at some point in time they were actively donating.
We are trying to determine which of those donors have
now become Non Donors in the past 4 to 8 months vs.
Non Donors who have never given a gift record(to-date)
because they were imported to the donor table within
the past 4 to 8 months.
hence the giftamount=0....
hth...denise
|
|
|
|
| Re: Using IF-THEN-ELSE in an UPDATE Statement [message #8786 is a reply to message #8781] |
Thu, 25 September 2003 11:36 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
So, the "donor" table is separate from any of the previously referenced tables (mailing, prospect, gift)?
Since you aren't changing the update value based on the conditions, it seems the only change is in the WHERE clause. From your description, it still sounds like an OR - update the mailing if there is a qualified gift OR a donor inception in the range:
update mailing m
set appealcode = :appealcode
where idnumber between low and curr
and maxgift is null
and maxgiftdate is null
and exists (select null
from prospect p
where p.idnumber = m.idnumber
and p.usercode2 = 'H'
and p.usercode1 = 'ACGA')
and ( exists (select null
from gift g
where g.idnumber = m.idnumber
and g.giftamount = 0
group by g.idnumber
having max(g.giftdate) between add_months(sysdate, -8) and add_months(sysdate, -4))
or exists (select null
from donor d
where d.idnumber = m.idnumber
and d.userdate2 between add_months(userdate2, -8) and add_months(userdate2, -4))); |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
