How to use Aliases or Variables to compare values in SQL

Question

I'm setting up SQLs Aliases as variables to replace the excel formula below

=+IF((O2+Z2)=0,0,+IF(AA2=0,(O2*-1),+IF(AND(AA2>0,(((R2*O2)/(O2+Z2))+H2)<= (O2*-1)),(O2*-1),(((R2*O2)/(O2+Z2))+H2))))

The above is the formula for 'Borough Tax Paid' but I need to check in SQL if column AA (Balance) is zero, then make 0

i.e. Borough Tax a negative number i.e. this bit of the formula(IF(AA2=0,(O2*-1)) . If AA (Balance) is not 0 the formula works well but if it is a zero , it does not calculate properly

The 'Borough Tax paid' formula is below

(Nett Remittance * Borough Tax)/(Borough Tax + Net_W_and_S_1) + (a.amt_benefit)

(((a.amt_remit) + (a.amt_refunds)) * ((a.amt_debit) + (a.amt_costs) + (a.amt_penalties) + (a.amt_write_off) + (a.amt_transitional) + (a.amt_lump_disc))/NULLIF((( (a.amt_debit) + (a.amt_costs) + (a.amt_penalties) + (a.amt_write_off) + (a.amt_transitional) + (a.amt_lump_disc)) + ((a.amt_water)+(a.amt_water_disab_redn)+(a.amt_water_disc)+(a.amt_sewerage)+(a.amt_sewerage_disab_redn)+(a.amt_sewerage_disc)+(a.amt_sewerage_transit)) ) , 0)) + (a.amt_benefit) AS Borough_Tax_Paid,


(a.amt_debit) + (a.amt_costs) + (a.amt_penalties) + (a.amt_write_off) + (a.amt_transitional) + (a.amt_lump_disc) AS Borough_Tax

(a.amt_remit) + (a.amt_refunds) AS Net_Remittance

(a.amt_water)+(a.amt_water_disab_redn)+(a.amt_water_disc)+(a.amt_sewerage)+(a.amt_sewerage_disab_redn)+(a.amt_sewerage_disc)+(a.amt_sewerage_transit) AS Net_W_and_S_1

INGRES. This is by a company called ACTIAN Software – Ben Apr 12 '19 at 13:54 — Ben, Apr 12 '19 at 13:54

score 1 · Answer 1 · answered Apr 11 '19 at 16:36

Use a CASE statement, like:

CASE 
  WHEN Balance = 0 THEN -- some value, or a formula
  WHEN Balance < 0 THEN -- maybe some other value, or a formula  
  ELSE -- another value, or formula
END AS Something

If you want to apply this on a result of some complicated formula, you can use a subquery:

SELECT CASE
         WHEN Balance = 0 THEN 0
         ELSE Something / Balance
       END AS SomethingDividedByBalance
  FROM (SELECT -- huge formula ending with AS Balance
             , -- another formula AS Something
          FROM yourTables
     ) subquery

How to use Aliases or Variables to compare values in SQL

1 Answers1