DB2 database varchar field to int field problem

Paste my code first:

SELECT BELONG_INSTN_COD AS DEPT_ID, SUM(BALANCE) AS BAL, CASE WHEN length(CERT_ID) = 15 THEN '19' || SUBSTR(CERT_ID, 7, 6) ELSE SUBSTR(CERT_ID, 7, 8) END AS BIRTH_DATE, 
  CASE WHEN length(CERT_ID) = 15 THEN SUBSTR(CERT_ID, 9, 2) ELSE SUBSTR(CERT_ID, 11, 2) END AS MM, 
  CASE WHEN length(CERT_ID) = 15 THEN SUBSTR(CERT_ID, 11, 2) ELSE SUBSTR(CERT_ID, 13, 2) END AS DD,
    CASE WHEN length(CERT_ID) = 15 THEN '19'||SUBSTR(CERT_ID, 7, 2) ELSE SUBSTR(CERT_ID, 7, 4) END AS YY
		FROM B_SA_ACCINFO
		WHERE length(CERT_ID) IN (15, 18)
			AND LEFT(CERT_ID, 1) NOT IN ('0', '9')
			AND LEFT(subject_no, 4) IN ('2003')
			OR subject_no IN ('20050505', '20051505', '20052505')
		GROUP BY BELONG_INSTN_COD, CERT_ID
		) T
	WHERE LEFT(T.BIRTH_DATE, 2) IN ('19', '20')
		AND BIRTH_DATE NOT LIKE '%/%
The main function of this SQL is to filter the ID number field of a record and merge the records of the same ID number and merge the balance. The INT() function in the query statement here can convert the corresponding information of birth year, month and day into INT type for filtering purposes. This statement can be executed without problem.

SELECT '2017-12-11' AS ETLDT, DEPT_ID, '1' AS TYPE, FLOOR((days('2017-12-11') - days(TO_DATE(A.BIRTH_DATE, 'YYYYMMDD'))) / 365.25) AS AGE, BAL
FROM (SELECT DEPT_ID, BAL, BIRTH_DATE, INT(MM) AS MM, INT(DD) AS DD,INT(YY) AS YY
	FROM (SELECT BELONG_INSTN_COD AS DEPT_ID, SUM(BALANCE) AS BAL, CASE WHEN length(CERT_ID) = 15 THEN '19' || SUBSTR(CERT_ID, 7, 6) ELSE SUBSTR(CERT_ID, 7, 8) END AS BIRTH_DATE, 
  CASE WHEN length(CERT_ID) = 15 THEN SUBSTR(CERT_ID, 9, 2) ELSE SUBSTR(CERT_ID, 11, 2) END AS MM, 
  CASE WHEN length(CERT_ID) = 15 THEN SUBSTR(CERT_ID, 11, 2) ELSE SUBSTR(CERT_ID, 13, 2) END AS DD,
    CASE WHEN length(CERT_ID) = 15 THEN '19'||SUBSTR(CERT_ID, 7, 2) ELSE SUBSTR(CERT_ID, 7, 4) END AS YY
		FROM B_SA_ACCINFO
		WHERE length(CERT_ID) IN (15, 18)
			AND LEFT(CERT_ID, 1) NOT IN ('0', '9')
			AND LEFT(subject_no, 4) IN ('2003')
			OR subject_no IN ('20050505', '20051505', '20052505')
		GROUP BY BELONG_INSTN_COD, CERT_ID
		) T
	WHERE LEFT(T.BIRTH_DATE, 2) IN ('19', '20')
		AND BIRTH_DATE NOT LIKE '%/%'
	)A
WHERE (A.MM BETWEEN 0 AND 13)
	AND  (A.DD BETWEEN 0 AND 32)
	AND (CASE WHEN MOD(A.YY, 4) <> 0
	AND MM = 2
	AND DD > 28 THEN 1 ELSE 0 END) = 0


After another filtering, plus an outer query, the problem arises:

Error:2017/12/11 14:39:59 0:00:00.000: DB2 Database Error: ERROR [22018] [IBM][DB2/NT64] SQL0420N  Invalid character found in a character string argument of the function "INTEGER".  SQLSTATE=22018

The query tells you that the description of the problem is that the parameters of the INT() function are not canonical, causing the conversion to fail.

Later, when I asked my colleague, I learned that the INT() function can be used for conversion in a select statement, but the condition in where must be a specific field already in the table, not a virtual table like A or T.

It is recommended that table A data be inserted into the table before querying.

Keywords: SQL Database

Added by sharpnova on Sun, 21 Jun 2020 19:12:14 +0300