Search code examples
sqlcomparisoncaseteradata

How to do numerical value comparison in Teradata SQL?

The goal is to use case when to create multiple data ranges in Teradata SQL

my code in Teradata SQL is:

select 
case when a <= 5 then b 
     when (5 < a < 6) then c 
     else 0 end 
from datadate

column a contains numbers such as 1,2,3

The error sign shows at the first < as SQL is expecting one of: 'at'...'day', 'hour'

How to execute numerical comparison in Teradata SQL? when I try to use comparison symbols such as <, >, <=, Teradata SQL always assumes I am comparing time/date.

Solution

  • You can only compare two operands:

    case when a <= 5 then b 
     when 5 < a AND a < 6 then c 
     else 0 
end

    But as CASE returns the first WHEN calculated TRUE:

    case when a <= 5 then b 
     when a < 6 then c -- all values <= 5 are already returned by the 1st when
     else 0 
end