ERXExistsQualifier and compound keys in t0
Larry Mills-Gahl <[email protected]> Wed, 4 Mar 2015 21:04:21 -0500
| Newsgroups | gmane.comp.web.webobjects.wonder-disc |
|---|---|
| Message-ID | <[email protected]> |
--===============1365118638252234872==
Content-Type: multipart/signed;
boundary="Apple-Mail=_EB1792DF-1A22-4732-B6E5-A67E7F2F7EF9";
protocol="application/pgp-signature"; micalg=pgp-sha1
--Apple-Mail=_EB1792DF-1A22-4732-B6E5-A67E7F2F7EF9
Content-Type: multipart/alternative;
boundary="Apple-Mail=_E52870AD-62EC-42FD-B334-E3C68917790A"
--Apple-Mail=_E52870AD-62EC-42FD-B334-E3C68917790A
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=utf-8
Hello,
I=E2=80=99m having a bit of difficulty with an ERXExistsQualifier and =
I=E2=80=99m wondering if it isn=E2=80=99t related to a compound key in =
the first table. The goal is to find unique rows in patientrole that =
have related rows in the encounter table. That=E2=80=99s a job for the =
Exists qualifier=E2=80=A6 (right Aaron?)
The problem is that the last portion of the exists clause (where the =
docs say: ...=E2=80=9C and t1.FIRST_TABLE_ID =3D t0.ID)=E2=80=9D is =
selecting the wrong fields to make that join.
The two tables in question are:
patientrole (centerkey,patientkey,mrn)
encounter(centerkey, patientkey, etc=E2=80=A6)
The qualifier code is:
EOQualifier centerQ =3D =
DialysisTreatment.CENTERKEY.is(c.centerkey());
EOQualifier rangeQ =3D new =
ERXAndQualifier(DialysisTreatment.START_TIME.after(start),
DialysisTreatment.START_TIME.before(end));
EOQualifier roleQ =3D new ERXExistsQualifier(new =
ERXAndQualifier(centerQ,
rangeQ), PatientRole.DIALYSIS_TREATMENTS_KEY);
The SQL output from this is:
SELECT t0.centerKey, RTRIM(t0.mrn), t0.personKey
FROM patientrole t0
WHERE EXISTS (
SELECT exists0.encounterKey
FROM encounter exists0
WHERE (exists0.centerKey =3D ?
AND (exists0.startTime > ? AND exists0.startTime < ?)
)
AND exists0.centerKey =3D t0.personKey # THIS IS THE PROBLEM =
LINE
)
The SQL I=E2=80=99m looking for ends with :
AND exists0.centerKey =3D t0.centerKey
Am I on the right track or should I accomplish this through other means?
Larry Mills-Gahl
[email protected]
--Apple-Mail=_E52870AD-62EC-42FD-B334-E3C68917790A
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
charset=utf-8
<html><head><meta http-equiv=3D"Content-Type" content=3D"text/html =
charset=3Dutf-8"></head><body style=3D"word-wrap: break-word; =
-webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" =
class=3D""><div class=3D"">Hello,</div><div class=3D""><br =
class=3D""></div><div class=3D"">I=E2=80=99m having a bit of difficulty =
with an ERXExistsQualifier and I=E2=80=99m wondering if it isn=E2=80=99t =
related to a compound key in the first table. The goal is to find unique =
rows in patientrole that have related rows in the encounter table. =
That=E2=80=99s a job for the Exists qualifier=E2=80=A6 (right =
Aaron?)</div><div class=3D""><br class=3D""></div><div style=3D"widows: =
1;" class=3D"">The problem is that the last portion of the exists clause =
(where the docs say: ...=E2=80=9C <span style=3D"widows: 1; =
background-color: rgb(255, 255, 255);" class=3D""><font face=3D"monospace"=
size=3D"3" class=3D"">and t1.FIRST_TABLE_ID =3D t0.ID)=E2=80=9D is =
selecting the wrong fields to make that join.</font></span></div><div =
style=3D"widows: 1;" class=3D""><span style=3D"widows: 1; =
background-color: rgb(255, 255, 255);" class=3D""><font face=3D"monospace"=
size=3D"3" class=3D""><br class=3D""></font></span></div><div =
style=3D"widows: 1;" class=3D""><font face=3D"monospace" size=3D"3" =
class=3D""><span style=3D"background-color: rgb(255, 255, 255);" =
class=3D"">The two tables in question are:</span></font></div><div =
style=3D"widows: 1;" class=3D""><font face=3D"monospace" size=3D"3" =
class=3D""><span style=3D"background-color: rgb(255, 255, 255);" =
class=3D""><span class=3D"Apple-tab-span" style=3D"white-space:pre"> =
</span>patientrole (centerkey,patientkey,mrn)</span></font></div><div =
style=3D"widows: 1;" class=3D""><font face=3D"monospace" size=3D"3" =
class=3D""><span style=3D"background-color: rgb(255, 255, 255);" =
class=3D""><span class=3D"Apple-tab-span" style=3D"white-space:pre"> =
</span>encounter(</span>centerkey, patientkey, =
etc=E2=80=A6)</font></div><div style=3D"widows: 1;" class=3D""><font =
face=3D"monospace" size=3D"3" class=3D""><br class=3D""></font></div><div =
style=3D"widows: 1;" class=3D""><font face=3D"monospace" size=3D"3" =
class=3D"">The qualifier code is:</font></div><div style=3D"widows: =
1;" class=3D""><font face=3D"monospace" size=3D"3" class=3D""><br =
class=3D""></font></div><div style=3D"widows: 1;" class=3D""><div =
style=3D"margin: 0px; font-size: 11px; font-family: Monaco; color: =
rgb(120, 141, 147);" class=3D""> =
EOQualifier <span style=3D"color: #e5493d" =
class=3D"">centerQ</span> =3D DialysisTreatment.CENTERKEY.<span =
style=3D"color: #2c9edb" class=3D"">is</span>(<span style=3D"color: =
#7f87cf" class=3D"">c</span>.centerkey());</div><div style=3D"margin: =
0px; font-size: 11px; font-family: Monaco; color: rgb(120, 141, 147);" =
class=3D""> EOQualifier <span style=3D"color: =
#e5493d" class=3D"">rangeQ</span> =3D <span style=3D"color: #788e95" =
class=3D"">new</span> <span style=3D"color: #2c9edb" =
class=3D"">ERXAndQualifier</span>(DialysisTreatment.START_TIME.<span =
style=3D"color: #2c9edb" class=3D"">after</span>(<span style=3D"color: =
#7f87cf" class=3D"">start</span>),</div><div style=3D"margin: 0px; =
font-size: 11px; font-family: Monaco; color: rgb(120, 141, 147);" =
class=3D""> =
DialysisTreatment.START_TIME.<span style=3D"color: #2c9edb" =
class=3D"">before</span>(<span style=3D"color: #7f87cf" =
class=3D"">end</span>));</div><div style=3D"margin: 0px; font-size: =
11px; font-family: Monaco; min-height: 15px;" class=3D""><br =
class=3D""></div><div style=3D"margin: 0px; font-size: 11px; =
font-family: Monaco; color: rgb(44, 158, 219);" class=3D""><span =
style=3D"color: #788d93" class=3D""> =
EOQualifier </span><span style=3D"color: #e5493d" =
class=3D"">roleQ</span><span style=3D"color: #788d93" class=3D""> =3D =
</span><span style=3D"color: #788e95" class=3D"">new</span><span =
style=3D"color: #788d93" class=3D""> </span>ERXExistsQualifier<span =
style=3D"color: #788d93" class=3D"">(</span><span style=3D"color: =
#788e95" class=3D"">new</span><span style=3D"color: #788d93" class=3D""> =
</span>ERXAndQualifier<span style=3D"color: #788d93" =
class=3D"">(</span><span style=3D"color: #e5493d" =
class=3D"">centerQ</span><span style=3D"color: #788d93" =
class=3D"">,</span></div><div style=3D"margin: 0px; font-size: 11px; =
font-family: Monaco; color: rgb(120, 141, 147);" class=3D""> =
<span style=3D"color: =
#e5493d" class=3D"">rangeQ</span>), =
PatientRole.DIALYSIS_TREATMENTS_KEY);</div></div><div style=3D"widows: =
1;" class=3D""><span style=3D"widows: 1; background-color: rgb(255, 255, =
255);" class=3D""><font face=3D"monospace" size=3D"3" class=3D""><br =
class=3D""></font></span></div><div class=3D"">The SQL output from this =
is:</div><div class=3D""><br class=3D""></div><div class=3D""><div =
class=3D""><font face=3D"Monaco" class=3D"">SELECT t0.centerKey, =
RTRIM(t0.mrn), t0.personKey </font></div><div class=3D""><font =
face=3D"Monaco" class=3D"">FROM patientrole t0 </font></div><div =
class=3D""><font face=3D"Monaco" class=3D"">WHERE EXISTS =
( </font></div><div class=3D""><font face=3D"Monaco" class=3D""><span=
class=3D"Apple-tab-span" style=3D"white-space:pre"> </span>SELECT =
exists0.encounterKey </font></div><div class=3D""><font =
face=3D"Monaco" class=3D""><span class=3D"Apple-tab-span" =
style=3D"white-space:pre"> </span>FROM encounter =
exists0 </font></div><div class=3D""><font face=3D"Monaco" =
class=3D""><span class=3D"Apple-tab-span" style=3D"white-space:pre"> =
</span>WHERE (exists0.centerKey =3D ? </font></div><div =
class=3D""><font face=3D"Monaco" class=3D""><span class=3D"Apple-tab-span"=
style=3D"white-space:pre"> </span>AND (exists0.startTime =
> ? AND exists0.startTime < ?)</font></div><div class=3D""><font =
face=3D"Monaco" class=3D""><span class=3D"Apple-tab-span" =
style=3D"white-space:pre"> </span>) </font></div><div =
class=3D""><font face=3D"Monaco" class=3D""><span class=3D"Apple-tab-span"=
style=3D"white-space:pre"> </span>AND exists0.centerKey =3D =
t0.personKey # THIS IS THE PROBLEM LINE</font></div><div class=3D""><font =
face=3D"Monaco" class=3D""><span class=3D"Apple-tab-span" =
style=3D"white-space:pre"> </span>) </font></div></div><div =
class=3D""><br class=3D""></div><div class=3D"">The SQL I=E2=80=99m =
looking for ends with :</div><div class=3D""><span style=3D"font-family: =
Monaco;" class=3D""><span class=3D"Apple-tab-span" =
style=3D"white-space:pre"> </span>AND exists0.centerKey =3D =
t0.centerKey</span></div><br class=3D""><div class=3D""><br =
class=3D"webkit-block-placeholder"></div><div class=3D"">Am I on the =
right track or should I accomplish this through other means?</div><div =
class=3D""><br class=3D"webkit-block-placeholder"></div><div =
class=3D""><br class=3D""></div><div class=3D""><br =
class=3D"webkit-block-placeholder"></div><div =
apple-content-edited=3D"true" class=3D"">
<div style=3D"color: rgb(0, 0, 0); font-family: Helvetica; font-style: =
normal; font-variant: normal; font-weight: normal; letter-spacing: =
normal; line-height: normal; orphans: 2; text-align: -webkit-auto; =
text-indent: 0px; text-transform: none; white-space: normal; widows: 2; =
word-spacing: 0px; -webkit-text-size-adjust: auto; =
-webkit-text-stroke-width: 0px; word-wrap: break-word; =
-webkit-nbsp-mode: space; -webkit-line-break: after-white-space; " =
class=3D""><div style=3D"color: rgb(0, 0, 0); font-family: Helvetica; =
font-style: normal; font-variant: normal; font-weight: normal; =
letter-spacing: normal; line-height: normal; orphans: 2; text-align: =
-webkit-auto; text-indent: 0px; text-transform: none; white-space: =
normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; =
-webkit-text-stroke-width: 0px; word-wrap: break-word; =
-webkit-nbsp-mode: space; -webkit-line-break: after-white-space; " =
class=3D""><div class=3D"">Larry Mills-Gahl</div><div class=3D""><a =
href=3D"mailto:[email protected]" =
class=3D"">[email protected]</a></div><div class=3D""><br =
class=3D""></div><div class=3D""><br class=3D""></div></div><br =
class=3D"Apple-interchange-newline"></div><br =
class=3D"Apple-interchange-newline"><br =
class=3D"Apple-interchange-newline">
</div>
<br class=3D""></body></html>=
--Apple-Mail=_E52870AD-62EC-42FD-B334-E3C68917790A--
--Apple-Mail=_EB1792DF-1A22-4732-B6E5-A67E7F2F7EF9
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename=signature.asc
Content-Type: application/pgp-signature;
name=signature.asc
Content-Description: Message signed with OpenPGP using GPGMail
-----BEGIN PGP SIGNATURE-----
Comment: GPGTools - http://gpgtools.org
iQEcBAEBAgAGBQJU97mlAAoJEH6+KDQxPmwvF7EH/1NlT05KPANesXh9dhE9FvpP
RZAM9VlqJIdgQj31Lh1Wab4YLSCYU8MntabWB6spVBwTpKiW2EFwSkqSJRhqP9CV
5crWmoz4o5L38vX01YCDIRgQop9umna+s2Z0Dqenlbn0FYe/jgnmee+KC5v5iYeh
svapBjOi27cQoIkeAUP8pz8jxPuuz15Scr33cPDV8kSKp+fzmd7I0hxRQIJILvBW
FLTNaGkOYrdwEWi5p8rR2TfIhE9sJaUdulKYNnAnL2TIMl53ou5lhCuF903DIgsc
wO20zjqwxzYarMIPO6gg6QQGqV3wXqdDx/zi3JvGUFsjtBtvQVlyUBNrtHGSSQA=
=3g2l
-----END PGP SIGNATURE-----
--Apple-Mail=_EB1792DF-1A22-4732-B6E5-A67E7F2F7EF9--
--===============1365118638252234872==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
--===============1365118638252234872==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
_______________________________________________
Wonder-disc mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/wonder-disc
--===============1365118638252234872==--