DB2: Create Function - Scale

Question

i have a table popo:

No|Id|Values.

1|X|321225.4775 -> Scale:4.

2|Y|321235.2115 -> Scale:4.

3|Z|12123.12321 -> Scale:5.

4|A| -> NULL.

5|B|12321 -> Scale:0.

i want to Flaging when column "Values" have a data where scale > 4. below is my current function script.

CREATE OR REPLACE
FUNCTION "SCHEME"."VALIDATION01" (A VARCHAR(500)) RETURNS INT
LANGUAGE SQL
BEGIN
DECLARE ASD DEC;
DECLARE A DEC;
SET ASD = CAST(A AS DEC(20,4));
IF A = ASD THEN RETURN NULL;
ELSEIF A IS NULL THEN RETURN NULL;
ELSE RETURN 1;
END IF;
END

Output:

[null]
[null]
[null]
[null]
[null]

i want output is:

[null]
[null]
1
[null]
[null]

can you help me?

Answer 1

Try the following:

CREATE OR REPLACE
FUNCTION "SCHEME"."VALIDATION01" (A VARCHAR(500)) RETURNS INT
LANGUAGE SQL
RETURN CASE WHEN A <> DEC(A, 20, 4) THEN 1 END;

The CASE statement returns 1, if A IS NOT NULL AND the original value is not equal to this value casted to the DECIMAL(20, 4) data type. It returns NULL otherwise.
It's the user's responsibility to call this function with proper string parameter to avoid string to number data type conversion errors.