CODEWITHSUNDEEP
stored-proceduresdb2runtimelocal-variables
Kapil Patel
Kapil Patel

Reputation: 11

Value can't be assigned to a host variable because it's not in range data type

CREATE PROCEDURE ADMINIST.STUDENT_CUSTOM_PROCEDURE1 (
  IN p_id INTEGER,
  IN p_maths INTEGER, 
  IN p_science INTEGER, 
  Out p_obtain_marks INTEGER, 
  out p_percentage decimal(3,2), 
  out p_status char(4)
)

P1: BEGIN

        DECLARE p_total INTEGER;
        SET p_total = 200;
        SET p_obtain_marks = p_maths + p_science;
        SET p_percentage = ((p_obtain_marks * 100)/p_total);

        IF (p_percentage > 35) THEN
            SET p_status = 'PASS';
        ELSE
            SET p_status = 'FAIL';  
        END IF;

        insert into ADMINIST.STUDENT_RESULT values(p_id, p_maths, p_science, p_obtain_marks, p_percentage, p_status);

END P1

I got Error code:

SQLCODE=-304, SQLSTATE=22003

Upvotes: 1

Views: 2320

Answers (2)

Kapil Patel
Kapil Patel

Reputation: 11

I got this answer by casting value into dacimal.

SET p_percentage = DECIMAL(((p_obtain_marks * 100)/p_total),3,2);

Thanks Henrik Loeser and Alex.

Upvotes: 0

data_henrik
data_henrik

Reputation: 17118

The DEC/DECIMAL data type is different than assumed. The data type information can be found in the DB2 manual under CREATE TABLE:

"DECIMAL(precision-integer, scale-integer) or DEC(precision-integer, scale-integer) For a decimal number. The first integer is the precision of the number; that is, the total number of digits; it may range from 1 to 31. The second integer is the scale of the number; that is, the number of digits to the right of the decimal point; it may range from 0 to the precision of the number. If precision and scale are not specified, the default values of 5,0 are used. The words NUMERIC and NUM can be used as synonyms for DECIMAL and DEC."

So in your case, to hold percentages, change the variable declaration:

  out p_percentage decimal(5,2),

Upvotes: 1

Related Questions