ORA-01722: invalid number when a specific number 112 is used

Question

WITH 
TEST_RESULT_CTE AS
(SELECT R.DSDW_RESULT_ID, 
R.PARAM_VALUE AS TEST_ID
FROM SLIMS_POC_RESULT_DETAIL R
JOIN SLIMS_POC_PARAMETER P ON P.PARAM_ID=R.PARAM_ID
WHERE P.PARAMETER_NAME='TEST_ID' AND P.CATEGORY = 'Result' )

SELECT * FROM
(
SELECT S.SAMPLE_ID, R.DSDW_RESULT_ID, PARA.PARAMETER_NAME as PNAME, R.PARAM_VALUE as PVALUE
FROM SLIMS_POC_RESULT_DETAIL R
JOIN TEST_RESULT_CTE TR ON TR.DSDW_RESULT_ID = R.DSDW_RESULT_ID
JOIN SLIMS_POC_TEST T ON T.TEST_ID = TR.TEST_ID
JOIN SLIMS_POC_SAMPLE S ON S.SAMPLE_ID = T.SAMPLE_ID --AND S.SAMPLE_ID = to_char(113)
JOIN SLIMS_POC_PARAMETER PARA ON PARA.PARAM_ID=R.PARAM_ID AND PARA.CATEGORY='Result'
)
Result_Data
PIVOT
(
  MAX(PVALUE) FOR PNAME IN ( 'TEST_ID', 'RESULT_NAME', 'UNIT', 'RESULT_TEXT', 'VALUE', 'STATUS', 'ENTERED_ON', 'ENTERED_BY', 'RESULT_TYPE' )
) PIVOTED_TAB
WHERE SAMPLE_ID > 111
ORDER BY SAMPLE_ID;

The above sql Query provides an output, without any error. However if I replace '111' with '112' in WHERE cluase, I get the following error:

ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number.

This error is quite strange to me, and that's why tough to fix.

Answer 1

The error is most likely from the data that is being returned from your new sample_id. There is a string being converted to a number that is failing. Check your data for invalid numerical data in varchar columns. Note that it could be an implicit conversion, not necessarily one where you are doing a to_number() call.