0

I have a table containing faults for different products, each row is a fault. The table has the following three columns: ID, Category1, Date.

The column Category1 type is VARCHAR(1300) and it contains either number from 0 to 999 or string.

I need to select the columns ID, and Date and, then, I need to create a conditional column based on the Category1 values.

When I run the following CASE Statement AND the field Category1 is a text ( i.e. "YES" ) then I got the error below.**

CASE 
    WHEN Category1 between 0 and 99 THEN 'CompanyA'
    WHEN Category1 between 100 and 155 THEN 'CompanyB'
    WHEN Category1 between 100 and 155 THEN 'CompanyC'
    WHEN Category1 between 100 and 155 THEN 'CompanyD'
    ELSE 'NULL' 
END AS X1

ERROR:

Executed as single statement. Failed[2620 : HY000] The format or data contains a bad character.

Indeed, when I select a date interval where there is no character/text in the field Category1 ( so only numbers from 0 to 999) then the CASE statement functions.

Does anyone know how I could deal with this problem? Why does the CASE WHEN stops functioning with text even if there is the ELSE statement?

Thanks in advance!

tc222_cz
  • 3
  • 4
  • 1
    You compare a VarChar to a numeric value, forcing a typecast which fails for 'YES'. Explicitly cast using TO_NUMBER which simple returns NULL instead of failing: `WHEN TO_NUMBER(Category1) between 0 and 99 THEN 'CompanyA'`. Btw, a data model storing integers and text like 'YES' in a VarChar(1300) is a quite bad. – dnoeth Sep 21 '22 at 17:23
  • Implicit casts are evil... – Andrew Sep 21 '22 at 19:36

0 Answers0