How can I execute the query and skip the equation if result entered NA or null?

I need to make calculation in my oracle query depends on the entered result.

This is the query :

SELECT
    id_no,
    TESTID, 
    UNITID , 
    result,
    MEAN,
    SD,
    ROUND(((RESULT - MEAN) / sd), 2) SDI,
    ROUND(MEAN - (NVL(SD, 0) * 2), 4) LOW_LIMIT,
    ROUND(MEAN + (NVL(SD, 0) * 2), 4) HIGH_LIMIT, 
    PERF_ID,
    2 AS VALIDATED
FROM
    HAJJ_QC_RESULTS 
WHERE
    id_no = 29064;

The issue some times there is no result its null or empty or no number they enter NA means Not Available in these cases the query return error invalid identifier.

The result column is of type varchar2 - not number.

How can I skip execute the query if the result null or empty or NA and execute only if result number ?

The error occurs on this line

ROUND(((RESULT - MEAN) / sd), 2) SDI,

Also, I don't need to use NVL because 0 some time is a result so not correct to convert empty or null to zero just I need to show what is the entered result.

Thank you in advance

Solution

You can entirely skip the rows with an incorrect RESULT by adding a WHERE clause to filter out values not looking like numbers, for example with REGEXP_LIKE(RESULT, '^[0-9]+([.][0-9]+)?$') (see REGEXP_LIKE documentation; basically: anything that, from start (^), has one or more (+) digits ([0-9]); then optionally ((…)?) a dot ([.], within square brackets else it has a special meaning in regexes) followed by one or more digits, and then followed by the end ($) to be sure there's a number and nothing else).

Alternatively, you can return the row with a -1 in it by adding a CASE WHEN <correct number> THEN <your compute> ELSE -1 END (or get a NULL instead of the -1 by removing the ELSE -1).

select     id_no,
           TESTID, 
           UNITID , 
           result,
           MEAN,
           SD,
           CASE WHEN REGEXP_LIKE(RESULT, '^[0-9]+([.][0-9]+)?$')
               THEN ROUND(((RESULT- MEAN) / sd),2)
               ELSE -1
           END SDI ,
           Round (MEAN - (NVL(SD ,0) * 2) , 4) LOW_LIMIT ,
           Round (MEAN + (NVL(SD ,0) * 2) , 4)  HIGH_LIMIT, 
           PERF_ID,
           2 AS VALIDATED
FROM HAJJ_QC_RESULTS

(see it running in a fiddle)