Archives of the TeradataForum

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

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

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