ORA-00920: invalid relational operator




function getFormattedString(f_inputString varchar2,
                            f_param       varchar2) return varchar2 is
  v_result             varchar2(8000);
  v_cnt                number := 1;
  v_Array eact_reports.t_array;
begin

  v_Array := Eact_Reports.split(f_inputString, ',');

  WHILE v_cnt <= v_Array.COUNT LOOP
    v_result := v_result || '''' || v_Array(v_cnt) || '''' || ',';
    v_cnt    := v_cnt + 1;
  end loop;

  if v_result is not null then
    v_result := f_param || ' IN (' ||
                substr(v_result, 0, length(v_result) - 1) || ')';
  else
    v_result := f_param ||' like  ''%''';
  end if;
  return v_result;
end getFormattedString;

select *  from EMP
where
    EXAMPLE_PACKAGE.getFormattedString(A,B,C,EMP.CODE)


I have written above function in a package.
i am calling this function in the where condition;but i am expecting the query should be formatted like this;

select *  from table
where
EMP.CODE IN('A','B','C')

but i am getting  below expection

ORA-00920: invalid relational operator
LVL 20
chaitu chaituAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>where     EXAMPLE_PACKAGE.getFormattedString(A,B,C,EMP.CODE)
that won't work, as it will interprete like this:

WHERE ' EMP.CODE IN(''A'',''B'',''C'') '
aka, a single string value. it cannot know you want to evaluate that "string" as part of the sql itself.

you will need dynamic sql. however, with SELECT, this will be tricky.
0
chaitu chaituAuthor Commented:
actually we have written a view for this query.

how to write dynamic sql for this or any other solution?
0
schwertnerCommented:
Functions deliver one value and could be written in SQL.

But

select *  from EMP
where
    EXAMPLE_PACKAGE.getFormattedString(A,B,C,EMP.CODE)

delivers ONE value let us say 10

Then

select *  from EMP
where
    10

has no meaning

Possible

select *  from EMP
where
    ammount < EXAMPLE_PACKAGE.getFormattedString(A,B,C,EMP.CODE)

is more meaningfull

Its is like

select *  from EMP
where
    ammount < 10




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chaitu chaituAuthor Commented:
function getFormattedString(f_inputString varchar2,f_delim varchar2) return varchar2
  is
  v_result varchar2(8000) ;
  v_cnt number:=1;
  v_Array eact_reports.t_array ;
  begin
   
             
                v_Array := Eact_Reports.split(f_inputString , f_delim);
   
                WHILE v_cnt <= v_Array.COUNT LOOP
                v_result := v_result || '''' || v_Array(v_cnt) || '''' || ',';
                v_cnt :=v_cnt+1;
                end loop;
     if v_result is not null then
                            v_result  := ''|| ' ('||substr(v_result ,1,length(v_result)-1 )||')';
                            else
                            v_result :=' LIKE ''%'' ';
                end if;        
  return v_result;
  end ;
END tax_report_package;

I have changed like this but still records r not coming.

select *  from table
where
AND EMP.CODE IN (SELECT EXAMPLE_PACKAGE.getFormattedString('A$B$C','$') FROM DUAL);
0
schwertnerCommented:
This is correct and it should formally work.
Investigate the values and their type.
The function returns one value of type varchar2.
What is the type of EMP.CODE?
If it is number use conversion TO_NUMBER or TO_CHAR.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.