Here's my idea:
donortbl (name char(30), bloodtype char(2), tissue smallint, cross
smallint);
reciptbl (name char(30), bloodtype char(2), tissue smallint, cross
smallint);
/*The tissue and cross fields would have a series of bits turned on for
each attribute desired*/
With level_1_match
(select d.name donor, r.name recip
from donrotbl d join reciptbl r using(bloodtype) ),
level_2_match
(select donor, recip
from level_1_match l1 join donortbl d on d.name = donor join reciptbl on
r.name = recip
where BITAND(d.tissue, r.tissue) = 65535 ),
level_3_match
(select donor, recip
from level_2_match l2 join donortbl d on d.name = donor join reciptbl on
r.name = recip
where BITAND(d.cross, r.cross) = 65535 )
select * from level_3_match;
I think I have seen this approach done before for similar type problems,
but I have never had a cause to use it and I'm not sure if I got it
right.
Also and variable could be used to mask the result of the BITAND
operation with a BITOR operation so that you could selectively turn on
and off certain criteria:
level_3_match
(select donor, recip
from level_2_match l2 join donortbl d on d.name = donor join reciptbl on
r.name = recip
where BITOR(BITAND(d.cross, r.cross),:criteriaselection) = 65535 )
-Tom Stieger
IT Manager
California Fine Wire
PS. thanks for a great brain challenge in the morning!
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jerry C. Adams
Sent: Tuesday, April 10, 2012 4:56 AM
To: Midrange-L
Subject: Kidney Donor Problem - SQL Solution?
A friend of mine recently had kidney cancer. As a result one kidney was
removed. The other one seems to be holding its own. The possibility
always exists, though, that the cancer may resurface meaning that he
would lose the remaining kidney and require a kidney transplant sooner
than later.
With that background I remember a news story a few years ago about seven
recipients and seven donors in the largest group transplant in history
at the time (Johns Hopkins, I think). Each recipient had a prospective
donor but were not a match. There are three basic matching criteria:
Bllod-type, tissue matching, and crossmatching. There are six factors
in the tissue matching and ten to fifteen factors in the crossmatching.
That led me to wondering how the matches were (and could be) made. I
will admit to being a lightweight when it comes to SQL, but, I think,
I'm pretty good at RPG. I couldn't come close using either to working
up a process (except brute force) that would match sets of
recipient-donor. For example, if only five factors are considered (for
illustration purposes), each set might look like:
Recipient-1 Donor-1
========== =====
F1=A F1=A
F2=B F2=A
F3=A F3=B
F4=A F4=A
F5=C F5=C
Where Fx = Factor and the right side is the result of the factor (result
A for Factor 1 is not the same thing as result A for Factor 2, by the
way).
Donor-1 might eventually be found to be compatible for Recipient-7,
Donor-5 might be compaticle with Recipient-1, etc. But one does not
know in advance how many Recipient:Donor sets will be necessary to
satisfy the situation in which all recipients are matched with
compatible donors.
So, how would one go about solving this problem (for five factors only)
in SQL? Would another approach (RPG, Cobol, etc.) work better, faster?
Jerry C. Adams
IBM i Programmer/Analyst
You don't need to be "straight" to fight and die for your country. You
just need to shoot straight. -Barry Goldwater, author of "Conscience of
a Conservative" in a letter to the Washington Post on 10 June 1993.
--
A&K Wholesale
Murfreesboro, TN
615-867-5070
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
