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

Question

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

Answer 1

So, you intend to compute the equation wherever it makes sense to compute it and fall back to a value when it makes no sense to compute it. You can implement a function like

CREATE FUNCTION is_numeric( p_str IN VARCHAR2 )
  RETURN BOOLEAN
IS
  l_num NUMBER;
BEGIN
  l_num := to_number( p_str );
  RETURN TRUE;
EXCEPTION
  WHEN value_error
  THEN
    RETURN FALSE;
END;

and then use this function in your query:

select     id_no,
           TESTID, 
           UNITID , 
           result,
           MEAN,
           SD,
           case
               when is_numeric(result) then to_char(ROUND(((RESULT- MEAN) / sd),2))
               else result
           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 
where id_no = 29064;

So if the result is numeric, then we compute the formula. We convert it to textual data in order to be consistent with the types, since the fallback (the else) may be a varchar as you expressed earlier, such as an 'NA'.