CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schema
lalaland
lalaland

Reputation: 341

Numeric value in snowflake with different data type

I have a column that is NUMBER datatype. However, I need a case statement that says if a certain condition are met to make the value 'ND' rather than the actual number.

When I do this I keep getting an error that says: Numeric value 'ND' is not recognized

case 
  when AGE <> '0' then PERSON_ID
  when AGE = '0' AND COUNTRY_ID <> 'USA' then try_to_number('ND') 
end as PERSON_ID

--- THIS WORKS, however, I Don't want it to return NULL, I need it to return 'ND'

Upvotes: 1

Views: 845

Answers (2)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

So the rule in SQL is a column has a type and only one type, and here you are trying use a string AND a number in the same column, thus the error. And the error on your prior question

Your options are you use strings as a number can be held in a string, or use a VARIANT type as it allows any type. Thus the string version, as Lukasz has shown:

select 
    age
    ,person_id
    ,COUNTRY_ID
    ,case 
      when AGE <> '0' then PERSON_ID::text
      when AGE = '0' AND COUNTRY_ID <> 'USA' then 'ND'
    end as PERSON_ID
from values
    (1, 10, 'NOT_USA'), 
    (0, 11, 'NOT_USA'),
    (0, 12, 'USA')
    t(age, person_id, COUNTRY_ID )
order by 2;
AGE PERSON_ID COUNTRY_ID PERSON_ID_2
1 10 NOT_USA 10
0 11 NOT_USA ND
0 12 USA null

and the variant version:

select 
    age
    ,person_id
    ,COUNTRY_ID
    ,case 
      when AGE <> '0' then PERSON_ID::variant
      when AGE = '0' AND COUNTRY_ID <> 'USA' then 'ND'::variant
    end as PERSON_ID
from values
    (1, 10, 'NOT_USA'), 
    (0, 11, 'NOT_USA'),
    (0, 12, 'USA')
    t(age, person_id, COUNTRY_ID )
order by 2;
AGE PERSON_ID COUNTRY_ID PERSON_ID_2
1 10 NOT_USA 10
0 11 NOT_USA "ND"
0 12 USA null

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

The number column has to be casted to text in order to match ND data type:

CASE WHEN AGE <> 0 THEN PERSON_ID::TEXT
     WHEN AGE = 0 AND COUNTRY_ID <> 'USA' THEN 'ND' 
END AS PERSON_ID

Upvotes: 3

Related Questions