|
midrange.com
Walden,
Regarding the coalesce(), perhaps in your join criteria, you could simply
explode the logic to specify the specific join conditions....
>From file1 join file2 on ( (fld1 is null and fld2 = fld3) or
(fld2 is null and fld1 = fld3) or
(fld1 = fld3) )
In this construct, short-circuit logic will only test the first two
conditions if there are nulls involved. The third term is invoked if
neither fld1 or fld2 is null, and specifies the default join terms....
It's a little verbose, but easily understood...
hth,
Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-297-2863 or ext. 1863
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Walden H. Leverich
Sent: Thursday, October 20, 2005 5:02 PM
To: Midrange Systems Technical Discussion
Subject: Join vs. Subselect with COALESCE for join value
I'm looking for opinions/options/fact on joins vs. subselects --
especially when coalesce is involved.
I needed to change a view I have yesterday. Historically we took a value
from one file and joined to another file to get a description, simple
enough (ok, the view did more, but it's not relevant to this problem).
However, I had to enhance the view to take the value from one of two
spots depending on which was null. However, while I can "select
coalesce(fld1, fld2) from..." I don't seem to be able to "join file2 on
coalesce(fld1,fld2) = fld3". Is that correct that I can't join w/a
coalesce in the join predicate?
Second, this forced me to move to a subselect instead of a join since in
the subselect I can use the coalesce() in the where clause of the
subselect.
I guess my question is, does anyone know a way to do what I wanted with
a join, as I'm assuming the join would be more efficient, or does it not
matter?
FWIW, the complete view is below, if anyone wants to see the whole
thing.
-Walden
CREATE VIEW WALDENL/CVSVIEW01X
(
PET_MEDICAL_RECORD_NO,
CVS_CLINIC_VISIT_DATE,
CVS_CLINIC_VISIT_DATE_ISO,
CVS_CLINIC_VISIT_TIME,
CVS_CLINIC_VISIT_TIME_ISO,
RSV_CODE,
RSV_DESCRIPTION,
SUP_USER_NAME,
SUP_FULL_NAME,
SUP_DOCTOR_YESNO,
REF_VET_ETT_CODE,
REF_VET_ETM_CODE,
REF_VET_FULL_NAME,
REF_PRACT_ETT_CODE,
REF_PRACT_ETM_CODE,
REF_PRACT_NAME,
PET_NAME,
PET_OWNER_NAME,
PET_SPECIES_BREED,
RSV_CODE_ON_VISIT
)
AS
SELECT CVS.BRAZCD,
CVS.BRATD8,
CASE
WHEN CVS.BRATD8 = 0
THEN DATE(VARCHAR('0001-01-01',10))
WHEN LEFT(DIGITS(CVS.BRATD8),1) = '0'
THEN
DATE(VARCHAR('19' CONCAT SUBSTR(DIGITS(CVS.BRATD8),2,2)
CONCAT '-' CONCAT SUBSTR(DIGITS(CVS.BRATD8),4,2)
CONCAT '-' CONCAT SUBSTR(DIGITS(CVS.BRATD8),6,2),10))
ELSE
DATE(VARCHAR('20' CONCAT SUBSTR(DIGITS(CVS.BRATD8),2,2)
CONCAT '-' CONCAT SUBSTR(DIGITS(CVS.BRATD8),4,2)
CONCAT '-' CONCAT SUBSTR(DIGITS(CVS.BRATD8),6,2),10))
END,
CVS.BRAGTM,
CASE
WHEN CVS.BRAGTM = 0
THEN TIME(VARCHAR('00.00.00',8))
ELSE
TIME(VARCHAR(LEFT(DIGITS(CVS.BRAGTM),2) CONCAT '.'
CONCAT SUBSTR(DIGITS(CVS.BRAGTM),3,2) CONCAT '.'
CONCAT SUBSTR(DIGITS(CVS.BRAGTM),5,2),8))
END,
--BRBYCD,
COALESCE(DZBYCD, BRBYCD),
--B4CATX,
(SELECT B4CATX FROM RSVPHY01 WHERE
B4BYCD = COALESCE(DZBYCD, BRBYCD)),
CVS.BRAJVN,
RTRIM(SUP.ADAJTX) CONCAT ', ' CONCAT RTRIM(SUP.ADAKTX)
CONCAT ' ' CONCAT SUP.ADAMTX,
SUP.ADB1ST,
CVS.BRC3CD,
CVS.BRC4CD,
CASE
WHEN RFV.AYA6TX IS NULL
THEN ' '
ELSE (RTRIM(RFV.AYA6TX) CONCAT ', ' CONCAT RTRIM(RFV.AYA8TX)
CONCAT ' ' CONCAT RFV.AYA7TX)
END,
CVS.BRC5CD,
CVS.BRC6CD,
CASE
WHEN RFP.AZAJNA IS NULL
THEN ' '
ELSE RFP.AZAJNA
END,
PET.AWA1TX,
RTRIM(CLI.A8BFTX) CONCAT ', ' CONCAT RTRIM(CLI.A8BGTX)
CONCAT ' ' CONCAT RTRIM(CLI.A8BHTX),
RTRIM(SPS.AIAQTX) CONCAT '-' CONCAT RTRIM(BRD.AJARTX),
BRBYCD
FROM CVSPHY01 as CVS
INNER JOIN CLIPHY01 AS CLI ON CLI.A8AOCD = CVS.BRAOCD
AND CLI.A8APCD = CVS.BRAPCD
INNER JOIN PETPHY01 AS PET ON CVS.BRAZCD = PET.AWAZCD
INNER JOIN SPSPHY01 AS SPS ON PET.AWAJCD = SPS.AIAJCD
INNER JOIN BRDPHY01 AS BRD ON PET.AWAJCD = BRD.AJAJCD
AND PET.AWC1CD = BRD.AJC1CD
INNER JOIN SUPPHY01 AS SUP ON CVS.BRAJVN = SUP.ADAJVN
LEFT OUTER JOIN RFVPHY01 AS RFV ON CVS.BRC3CD = RFV.AYAOCD
AND CVS.BRC4CD = RFV.AYAPCD
LEFT OUTER JOIN RFPPHY01 AS RFP ON CVS.BRC5CD = RFP.AZAOCD
AND CVS.BRC6CD = RFP.AZAPCD
LEFT OUTER JOIN ATSPHY01 ATS
ON CVS.BRAZCD = ATS.DZAZCD
AND CVS.BRATD8 = ATS.DZATD8
AND CVS.BRAGTM = ATS.DZAGTM
AND DZATDT * 1000000 + DZANTM=(
SELECT MAX(DZATDT * 1000000 + DZANTM)
FROM ATSPHY01 B
WHERE ATS.DZAZCD=B.DZAZCD AND
ATS.DZATD8=B.DZATD8 AND
ATS.DZAGTM=B.DZAGTM)
--INNER JOIN RSVPHY01 as RSV on
-- COALESCE(ATS.DZBYCD, CVS.BRBYCD) = RSV.B4BYCD
WHERE RFV.AYAPCD IS NOT NULL OR RFP.AZAPCD IS NOT NULL
------------
Walden H Leverich III
Tech Software
(516) 627-3800 x11
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com <blocked::http://www.techsoftinc.com/>
Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
