|
midrange.com
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-2025 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.
