Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 22 Aug 2008 @ 13:32:22 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: How Can I Avoid Repeating an Expression?
 
From:   Geoffrey Rommel

Welcome! This may not be a big deal; the parser will probably detect the common expression and only compute it once. Couldn't swear to that, though. But, to answer your question, there are two ways.

1. Create an extra column with that expression, and refer to it later in the query. This is an especially handy feature that I haven't seen in other databases.

     sel substr(date,1,7) Yr_Month,
         Svc_Ownr_Cd,
         Bucket,
         100 * (curr_bal - cl_amt) / cl_amt as pct_increase,
         case
           when cl_amt = 0 and curr_bal > cl_amt then '(0)  CL=0 & Bal>CL'
           when curr_bal <= cl_amt               then '(1)  not OL'
           else case
                  when pct_increase <= 10 then   '(2)  <= 10%'
                  when pct_increase <= 15 then   '(3)  <= 15%'
                  when pct_increase <= 20 then   '(4)  <= 20%'
                  when pct_increase <= 25 then   '(5)  <= 25%'
                  else      '(6)  > 25%'
                end
         end as OL_Pct, ...

2. If you don't want that extra column showing up in the results, use a derived table.

     sel substr(date,1,7) Yr_Month,
         Svc_Ownr_Cd,
         Bucket,
         case
           when cl_amt = 0 and curr_bal > cl_amt then '(0)  CL=0 & Bal>CL'
           when curr_bal <= cl_amt               then '(1)  not OL'
           else case
                  when pct_increase <= 10 then   '(2)  <= 10%'
                  when pct_increase <= 15 then   '(3)  <= 15%'
                  when pct_increase <= 20 then   '(4)  <= 20%'
                  when pct_increase <= 25 then   '(5)  <= 25%'
                  else      '(6)  > 25%'
                end
         end as OL_Pct,
         count(1) Number
     from  (select a.*,             /*** DERIVED TABLE ***/
         100 * (curr_bal - cl_amt) / cl_amt as pct_increase
        from Table a )
     where ...
     group by 1,2,3,4


     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019