Conditional statements using HANA SQL Script

Question

I have a table in HANA studio, which consists of 5 columns:

• item
• value
• cost
• Margin
• ABC_cat

How can I generate a 6th column as Margin Categorization, using an if-else condition?

IF ((([Margin])/[VALUE])*100<((SUM([VALUE])-SUM([COST]))/SUM([VALUE]))*100)
  THEN ('BELOW') 
  ELSE ('ABOVE')

Solution

You need two things: window functions and a case statement:

select
  item, value, cost, margin, abc_cat,
  case when 
    margin / value < (sum(value) over() - sum(cost) over()) / sum(value) over()
    then 'BELOW' 
    else 'ABOVE' 
  end as "Margin Categorization"
from my_schema.my_table;