Archives of the TeradataForum

Message Posted: Tue, 02 Sep 2008 @ 14:04:17 GMT

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

I am the original poster and again I thank all of the responders.

The point was not to save exec time, but rather to have cleaner code. For a really long expression, this would be much more important than in this example.

The best solution, from Andy Baker, names the expression at first use, then uses the name thereafter. The code below shows this, but with Andy's extra pair of (...) removed:

     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 (100 * (curr_bal - cl_amt) / cl_amt)* (named clpct)*
                             <= 10 then '(2)  <= 10%'
                  when clpct <= 15 then '(3)  <= 15%'
                  when clpct <= 20 then '(4)  <= 20%'
                  when clpct <= 25 then '(5)  <= 25%'
                  else                  '(6)  > 25%'
                end
         end as OL_Pct,
         count(1) Number
     from  Table
     where ...
     group by 1,2,3,4

_Other responses_:

1. This method:

     ...
     else case  100 * (curr_bal - cl_amt) / cl_amt
            when <= 10 then '(2)  <= 10%'
     ...

elicits: Error 3706: Syntax error: expected something between the when' keyword and '"<="'.

2. Including the expression in the SELECT list does not work, as it is not one of the GROUP vars, nor is it an aggregate:

     sel substr(date,1,7) Yr_Month,
         Svc_Ownr_Cd,
         Bucket,
         100 * (curr_bal - cl_amt) / cl_amt (Named mycalc),
         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 mycalc <= 10 then '(2)  <= 10%'
           ...

3. I did not try a derived table, as the "named" solution was more elegant.

Phil Pollack

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