|
Nice! Didn't think of that. Thanks. Actually made a _big_ difference in how the records were accessed, and I didn't expect that. With the join it seems to use an index that it's not using with the subselect. Maybe the fact that one has a subselect and one doesn't allows the query optimizer to take different code paths. -Walden ------------ Walden H Leverich III Tech Software (516) 627-3800 x11 WaldenL@xxxxxxxxxxxxxxx http://www.TechSoftInc.com Quiquid latine dictum sit altum viditur. (Whatever is said in Latin seems profound.) -----Original Message----- From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of DeLong, Eric Sent: Thursday, October 20, 2005 6:31 PM To: 'Midrange Systems Technical Discussion' Subject: RE: Join vs. Subselect with COALESCE for join value 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.
midrange.com
