CODEWITHSUNDEEP
oracle-databaseplsql
Anil Kumar K
Anil Kumar K

Reputation: 35

I am getting invalid number error

SET SERVEROUTPUT ON    
DECLARE
    lv_comp_msr VARCHAR2(20000);
 BEGIN
     WITH msr AS
        (SELECT REGEXP_SUBSTR ('02,03,04,09,12', '[^,]+',1,LEVEL) AS msr_id
           FROM DUAL
          CONNECT BY REGEXP_SUBSTR ('02,03,04,09,12', '[^,]+',1,LEVEL) IS NOT NULL    ) 
     SELECT listagg (measure_id, ',') WITHIN GROUP (    ORDER BY measure_id) AS MEASURE_ID 
     INTO lv_comp_msr
    FROM  
          (SELECT measure_id FROM irp_measures_def   ) 
    WHERE measure_id IN  (SELECT listagg (msr_id, ',') WITHIN GROUP (ORDER BY msr_id) msr_id
                          FROM msr   ) 
      --and COMP_MSR_FLAG is null
   ;   
   DBMS_OUTPUT.put_line('lv_comp_msr=' || lv_comp_msr); 
END;

Upvotes: 0

Views: 630

Answers (1)

APC
APC

Reputation: 146349

listagg() produces a string. In your WHERE clause you compare it with measure_id, which I'm going to guess is numeric. You're comparing a number and a string, so Oracle performs an implicit datatype conversion. But of course an aggregated comma-separated string can't be converted to a number, hence the ORA-01722 error.

The error is easily avoided. Your have a WITH clause which produces a table of numbers: why are you applying a listagg() in the first place? You can just use the output of the sub-query factoring:

 WHERE measure_id IN  (SELECT msr_idFROM msr   )

Upvotes: 1

Related Questions