× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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 thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.