I am checking string for a value after $ character and i want only those records where that character is numeric line number from 1 to 16 when executed gives me the desired result but when i add the line number 17 it gives me ORA-01722: invalid number error,
Now for checking whether i am comparing a string with number i wrote line number 7 and 8 and no problems it gives me results so i am not comparing a string with number then why does line number 17 gives me a invalid number error
select
TASK_ID,
TAM.TOWER TOWER,
TASK_STATUS STATUS,
cast(TASK_ACTIVATE_TIME as timestamp) TASK_ACTIVATE_TIME,
CHANGE_SUMMARY
/*CASE WHEN substr(CHANGE_SUMMARY,INSTR(CHANGE_SUMMARY,'$')+1,1) <= 2 THEN 'Pass'
WHEN substr(CHANGE_SUMMARY,INSTR(CHANGE_SUMMARY,'$')+1,1) > 2 THEN 'Fail'
END SERVER_COUNT*/
FROM V_CHANGE_TASK_LIVE_MONITOR@NTT_QQITSMREP.BMW CTLM
JOIN TOWER_ASSGNEE_MAPPING TAM on TAM.ASSIGNEE_GROUP_NAME = CTLM.TASK_ASSIGNEE_GROUP
where TASK_STATUS <> 'Closed'
AND TASK_STATUS <> 'Staged'
AND TASK_NAME IN (select TASK_NAME from NON_PROVNG_DECOMNG_TASKS)
AND TAM.TOWER IN ('LINUX', 'WINDOWS')
AND TRIM(TRANSLATE(substr(CHANGE_SUMMARY,INSTR(CHANGE_SUMMARY,'$')+1,1), '0123456789-,.', ' ')) IS NULL
AND substr(CHANGE_SUMMARY,INSTR(CHANGE_SUMMARY,'$')+1,1) <= 2
Please advise.
Thanks
Rakesh