Cannot convert a varchar value of 100 into number under a grade column in Oracle SQL-CodePudding

I have a column entitled "Grade" and defined as VARCHAR2(3); however when I have a grade value of '100' the system under Oracle SQL cannot read this value as integer and I am not able to set up the below case condition:

WHEN CAST(GRADE as varchar(3)) between '93' and '100' then 4.3

Update:

I am getting a 0 score for the 100 value instead of 4.3

Select CRSE_NUMB,
       Grade,
       CASE 
       WHEN CAST(GRADE as varchar(3)) between '93' and '100' then 4.3 
       WHEN Grade < '60' then 0.0
       WHEN Grade = '60' then 1.0
       WHEN Grade between '61' and '62' then 1.3
       WHEN Grade between '79' and '82' then 3.3
       WHEN Grade between '83' and '86' then 3.7
       WHEN Grade between '87' and '92' then 4.0
       WHEN TO_NUMBER(Grade DEFAULT NULL ON CONVERSION error) between '93' and '100' then 4.3 
       else null
       end as Score
from   SWBGRDE
group by CRSE_NUMB, Grade

CodePudding user response：

Use TO_NUMBER():

WHEN TO_NUMBER(grade) BETWEEN 93 AND 100 THEN 4.3

If you have non-numeric data in your grade column, and are on Oracle 12 or later, you can use:

WHEN TO_NUMBER(grade DEFAULT NULL ON CONVERSION ERROR) BETWEEN 93 AND 100 THEN 4.3

If you compare using strings then the comparison is done on substrings of successively increasing lengths so it will compare if the first character of GRADE is between a minimum of '9' and a maximum of '1' which will never be true.

For example, from Oracle 12 you can use:

SELECT grade,
       CASE
       WHEN TO_NUMBER(grade DEFAULT NULL ON CONVERSION ERROR) < 93 THEN 1
       WHEN TO_NUMBER(grade DEFAULT NULL ON CONVERSION ERROR) BETWEEN 93 AND 100 THEN 4.3
       END AS score
FROM   table_name

Or, in earlier versions, you can look for a non-numeric character:

SELECT grade,
       CASE
       WHEN REGEXP_LIKE(grade, '\D') THEN NULL
       WHEN TO_NUMBER(grade) < 93 THEN 1
       WHEN TO_NUMBER(grade) BETWEEN 93 AND 100 THEN 4.3
       END AS score
FROM   table_name

Then, for the sample data:

CREATE TABLE table_name (grade VARCHAR2(3));

INSERT INTO table_name (grade)
SELECT TO_CHAR(90   LEVEL) FROM DUAL CONNECT BY LEVEL <= 10 UNION ALL
SELECT 'AAA' FROM DUAL;

Outputs:

GRADE SCORE

91 1

92 1

93 4.3

94 4.3

95 4.3

96 4.3

97 4.3

98 4.3

99 4.3

100 4.3

AAA null

GRADE	SCORE
91	1
92	1
93	4.3
94	4.3
95	4.3
96	4.3
97	4.3
98	4.3
99	4.3
100	4.3
AAA	null

db<>fiddle here

Regarding your update:

Do not compare numbers as strings - compare them as numbers and use TO_NUMBER everywhere:

Select CRSE_NUMB,
       Grade,
       CASE 
       WHEN TO_NUMBER(Grade DEFAULT NULL ON CONVERSION ERROR) < 60 then 0.0
       WHEN TO_NUMBER(Grade DEFAULT NULL ON CONVERSION ERROR) = 60 then 1.0
       WHEN TO_NUMBER(Grade DEFAULT NULL ON CONVERSION ERROR) <= 62 then 1.3
       WHEN TO_NUMBER(Grade DEFAULT NULL ON CONVERSION ERROR) <= 82 then 3.3
       WHEN TO_NUMBER(Grade DEFAULT NULL ON CONVERSION ERROR) <= 86 then 3.7
       WHEN TO_NUMBER(Grade DEFAULT NULL ON CONVERSION ERROR) <= 92 then 4.0
       WHEN TO_NUMBER(Grade DEFAULT NULL ON CONVERSION ERROR) <= 100 then 4.3 
       else null
       end as Score
from   SWBGRDE
group by CRSE_NUMB, Grade

If appropriate, you can just specify the top end of the range and let the CASE expression short-circuit to the first match.

CodePudding user response：

Don't use quote around 93 and 100:

SELECT CASE WHEN CAST(100 as varchar(3)) between 93 and 100 then 4.3 END FROM DUAL

But since you are using number I am not sure why you are casting it to varchar. Any particular reason?