SELECT in an IN clause-CodePudding

I try to convert a comma separated string to number values. And then I try to chceck if a specified number occures there. I'm doing it inside a function. I'm getting error PLS-00103. The simplified snipped is here:

fieldType NUMBER :=1; 
myString := '2,3,4,5';
IF fieldType NOT IN (SELECT TO_NUMBER(xt.column_value) FROM XMLTABLE(myString) xt)  THEN
   --do soemthing
   -- Problem occures with the (
   -- 
END IF;

CodePudding user response：

You cannot use select statement in if condition

Link : https://stackoverflow.com/a/10203109/8843451

You need to assign the value to variable and use it

strop number :=0;

select count(*) into strop from (SELECT  to_number(trim(COLUMN_VALUE)) as str FROM
 xmltable(('"'|| REPLACE(myString, ',', '","')|| '"'))) where str in (fieldType);
 
 if strop >0 then 
 
    dbms_output.put_line(fieldType || ' Is a Valid Number');
 else
    dbms_output.put_line(fieldType || ' Is a InValid Number');
 end if;

CodePudding user response：

There's a simpler option (SQL*Plus example; you'd use that 1 in line #2, or whatever you'd want to use):

SQL> DECLARE
  2     fieldtype  NUMBER := &par_fieldtype;
  3     mystring   VARCHAR2 (200) := '2,3,4,5';
  4  BEGIN
  5     IF ',' || mystring || ',' LIKE '%,' || fieldtype || ',%'
  6     THEN
  7        DBMS_OUTPUT.put_line ('Member');
  8     ELSE
  9        DBMS_OUTPUT.put_line ('Not a member');
 10     END IF;
 11  END;
 12  /
Enter value for par_fieldtype: 1
Not a member

PL/SQL procedure successfully completed.

SQL> /
Enter value for par_fieldtype: 3
Member

PL/SQL procedure successfully completed.

SQL>