Invalid character found in a character string argument of the function "INTEGER".. SQLCODE=-420, SQLSTATE=22018, DRIVER=3.69.71

Ricky Cuen

Invalid character found in a character string argument of the function "INTEGER".. SQLCODE=-420, SQLSTATE=22018, DRIVER=3.69.71

Can you help me to figure out why am I getting -420 error in this function:

CREATE
FUNCTION MULTIPLECHOICE_BD_METRIC_VALUES(
aRoID INTEGER
) RETURNS TABLE
(
BDCATEGORIES_ID INTEGER,
REPORTING_OUTCOME_ID INTEGER,
SPI_ID INTEGER,
VALUE DOUBLE
) NO
EXTERNAL ACTION F1:BEGIN ATOMIC RETURN SELECT
BDC.BDCATEGORIES_ID,
BD.REPORTING_OUTCOME_ID,
SPI.SPI_ID,
DOUBLE(BER.VALUE) AS VALUE
FROM IGB.SPIS SPI,
IGB.BER_ANSWER_OPTIONS BER,
IGB.REPORTING_OUTCOMES_TO_QUESTION ROQ,
IGB.BD_CATEGORY_RULES BDC,
IGB.BUSINESSDRIVERS BD
WHERE SPI.SPI_ID = BER.SPI_ID
AND ROQ.DATASOURCES_ID = BDC.DATASOURCES_ID
AND ROQ.REPORTING_OUTCOME_ID = BDC.REPORTING_OUTCOME_ID
AND ROQ.QUESTION_ID = BER.QUESTION_ID
AND BD.REPORTING_OUTCOME_ID = ROQ.REPORTING_OUTCOME_ID
AND BER.VALUE = INTEGER(BDC.FORMULA)
AND SPI.SPI_STATUS_ID IN(
2,
4
)
AND BD.BD_TYPE_ID IN(
1,
2
)
AND BD.REPORTING_OUTCOME_ID = aRoID;
--
END

It works when I execute the function in an older version of db2, but not in 10.5.

 

Edited By:
Ricky Cuen[Organization Members] @ Feb 21, 2018 - 04:59 PM (America/Mexico_City)

James Campbell

Invalid character found in a character string argument of the function "INTEGER".. SQLCODE
(in response to Ricky Cuen)
Not a Db2 LUW person, but ..

Have the rules for casting IGB.BD_CATEGORY_RULES.FORMULA (I presume this is the
column with the problem) to an Integer changed?

You will probably need to find an actual example of a failing value.

James Campbell


On 21 Feb 2018 at 15:45, Ricky Cuen wrote:

>
> Can you help me to figure out why am I getting -420 error in this function:
>
> CREATE
> FUNCTION MULTIPLECHOICE_BD_METRIC_VALUES(
> aRoID INTEGER
> ) RETURNS TABLE
> (
> BDCATEGORIES_ID INTEGER,
> REPORTING_OUTCOME_ID INTEGER,
> SPI_ID INTEGER,
> VALUE DOUBLE
> ) NO
> EXTERNAL ACTION F1:BEGIN
> ATOMIC RETURN SELECT
> BDC.BDCATEGORIES_ID,
> BD.REPORTING_OUTCOME_ID,
> SPI.SPI_ID,
> DOUBLE(BER.VALUE) AS VALUE
> FROM IGB.SPIS SPI,
> IGB.BER_ANSWER_OPTIONS BER,
> IGB.REPORTING_OUTCOMES_TO_QUESTION ROQ,
> IGB.BD_CATEGORY_RULES
> BDC,
> IGB.BUSINESSDRIVERS BD
> WHERE SPI.SPI_ID = BER.SPI_ID
> AND ROQ.DATASOURCES_ID = BDC.DATASOURCES_ID
> AND ROQ.REPORTING_OUTCOME_ID = BDC.REPORTING_OUTCOME_ID
> AND ROQ.QUESTION_ID = BER.QUESTION_ID
> AND BD.REPORTING_OUTCOME_ID
> = ROQ.REPORTING_OUTCOME_ID
> AND BER.VALUE = INTEGER(BDC.FORMULA)
> AND SPI.SPI_STATUS_ID IN(
> 2,
> 4
> )
> AND BD.BD_TYPE_ID IN(
> 1,
> 2
> )
> AND BD.REPORTING_OUTCOME_ID = aRoID;
>
>
> --
> END
>
> It works in an older version of db2, but not in 10.5
>


---
This email has been checked for viruses by AVG.
http://www.avg.com

Greg Palgrave

RE: Invalid character found in a character string argument of the function "INTEGER".. SQLCODE=-420, SQLSTATE=22018, DRIVER=3.69.71
(in response to Ricky Cuen)

Hi Ricky,

Most likely the access path has changed between versions, and the order of processing the WHERE clause means it is evaluating the predicates in a different order, so the INTEGER function is hitting non-numeric values in the BDC.FORMULA column, and -420 is the result.

Compare the access path between the old version and new version, and then you may see where it has changed. You may be able to then manipulate the order of the WHERE clause to influence the optimizer or perhaps use an optimizer hint to get around it.

Regards,

Greg



In Reply to Ricky Cuen:

Can you help me to figure out why am I getting -420 error in this function:

 AND BER.VALUE = INTEGER(BDC.FORMULA) 

It works when I execute the function in an older version of db2, but not in 10.5.