CODEWITHSUNDEEP
if-statementteradata
Matt
Matt

Reputation: 111

If then statement change existing column

How can I do this. I tried a case statement but have issues getting it to work

if Tax_or_Flat = 'tax' then flat = 0

Tax_or_Flat is a column title
flata is a column title

maybe

,CASE WHEN Tax_or_Flat = 'tax' THEN Flata = 0 ELSE Tax_or_Flat END

but thats not working

select 
a.time_amt as Amt_Day
,a.rate_mix as AVG_Day_Rate
,a.psbdats as Poss_Days_Bil_Ins
,a.acct as Bill_to_Amount
,a.LGCY_AUTH_AMT_TXT as Auth_Amt
      ,case when Auth_Amt  LIKE '%TAX%'  then 'tax'
       when Auth_Amt  LIKE '%DAY%'  then 'flat'
            else 0
            end as Tax_or_Flat
     ,((Amt_Day - AVG_Day_Rate ) * Poss_Days_Bil_Ins) as Tax
       ,((Amt_Day * Poss_Days_Bil_Ins) - Bill_to_Amount) aS Flat


from JTable.Loan a

where a.rate_mix = 5

Upvotes: 0

Views: 154

Answers (2)

dnoeth
dnoeth

Reputation: 60472

Based on your SELECT you seems to want something like this:

select 
a.time_amt as Amt_Day
,a.rate_mix as AVG_Day_Rate
,a.psbdats as Poss_Days_Bil_Ins
,a.acct as Bill_to_Amount
,a.LGCY_AUTH_AMT_TXT as Auth_Amt
  ,case when Auth_Amt  LIKE '%TAX%'  then 'tax'
   when Auth_Amt  LIKE '%DAY%'  then 'flat'
        else '0'
        end as Tax_or_Flat
  ,case when Tax_or_Flat = 'tax' 
        then ((Amt_Day - AVG_Day_Rate ) * Poss_Days_Bil_Ins) 
        else 0 
   end as Tax
  ,case when Tax_or_Flat = 'flat' 
        THEN ((Amt_Day * Poss_Days_Bil_Ins) - Bill_to_Amount) 
        else 0 
   end AS Flat    

from JTable.Loan a

where a.rate_mix = 5

Upvotes: 0

Sam CD
Sam CD

Reputation: 2097

UPDATE table_name
SET flata = CASE WHEN Tax_or_Flat = 'tax' THEN 0 ELSE Tax_or_Flat END

This will change the value of flata to 0 when Tax_or_Flat = 'tax', and it will give it the value in the Tax_or_Flat column in all other cases.

Check the syntax for Teradata, but this is the basic idea of what you are attempting.

** EDIT **

If this is just a SELECT that you are trying to perform, and not change any actual table data, you can either do a nested subquery:

select *,case when Tax_or_Flat = 'tax' THEN 0 ELSE Tax_or_Flat END as flata
from (
select 
a.time_amt as Amt_Day
,a.rate_mix as AVG_Day_Rate
,a.psbdats as Poss_Days_Bil_Ins
,a.acct as Bill_to_Amount
,a.LGCY_AUTH_AMT_TXT as Auth_Amt
      ,case when Auth_Amt  LIKE '%TAX%'  then 'tax'
       when Auth_Amt  LIKE '%DAY%'  then 'flat'
            else 0
            end as Tax_or_Flat
     ,((Amt_Day - AVG_Day_Rate ) * Poss_Days_Bil_Ins) as Tax
       ,((Amt_Day * Poss_Days_Bil_Ins) - Bill_to_Amount) aS Flat


from JTable.Loan a

where a.rate_mix = 5
) a

or rewrite the logic within the query:

select 
a.time_amt as Amt_Day
,a.rate_mix as AVG_Day_Rate
,a.psbdats as Poss_Days_Bil_Ins
,a.acct as Bill_to_Amount
,a.LGCY_AUTH_AMT_TXT as Auth_Amt
,case when Auth_Amt  LIKE '%TAX%'  then 'tax'
      when Auth_Amt  LIKE '%DAY%'  then 'flat'
      else 0
end as Tax_or_Flat
,((Amt_Day - AVG_Day_Rate ) * Poss_Days_Bil_Ins) as Tax
,((Amt_Day * Poss_Days_Bil_Ins) - Bill_to_Amount) aS Flat
,case when (case when Auth_Amt  LIKE '%TAX%'  then 'tax'
            when Auth_Amt  LIKE '%DAY%'  then 'flat'
            else 0 end) = 'tax' 
      then 0 
      else (case when Auth_Amt LIKE 'TAX%'
                 then 'tax'
                when Auth_Amt  LIKE '%DAY%'     
                then 'flat'
                else 0
            end)
  end AS flata

from JTable.Loan a

where a.rate_mix = 5

Upvotes: 1

Related Questions