Home » SQL & PL/SQL » SQL & PL/SQL » Query Help with table function (Oracle 10.2.0.4, AIX 5.3)
Query Help with table function [message #568681] Mon, 15 October 2012 14:25 Go to next message
sspn2010
Messages: 147
Registered: October 2008
Senior Member
Hi,

Can someone help me on this below query. I've below pipelined function to parse the values (which is coming from table)


CREATE OR REPLACE FUNCTION parse_list(p_list varchar2, p_del varchar2 := ',')
    RETURN split_tbl
    PIPELINED IS
    l_idx  pls_integer;
    l_list varchar2(32767) := p_list;
  begin
    loop
      l_idx := instr(l_list, p_del);
      if l_idx > 0 then
        pipe row(upper(substr(l_list, 1, l_idx - 1)));
        l_list := substr(l_list, l_idx + length(p_del));

      else
        pipe row(upper(l_list));
        exit;
      end if;
    end loop;
    return;
  END parse_list;

Now running below query and getting error "ORA-01427 single-row subquery returns more than one row".

with tmp_tbl as
 (
select 1 as empno, 'Dupe Within Feed' audit_errors from dual
 union
select 2 as empno, 'Already in DB' audit_errors from dual
 union
select 3 as empno, 'Invalid state' audit_errors from dual
)
select * from tmp_tbl
 where upper(audit_errors) not like (
   select * from table(parse_list('%Dupe Within Feed,%Already in DB')));



How can i resolve the error. Appreciate your help.

Thanks
Srniath
Re: Query Help with table function [message #568682 is a reply to message #568681] Mon, 15 October 2012 14:31 Go to previous messageGo to next message
Michel Cadot
Messages: 60060
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot use LIKE with a SELECT that returns more than one row.
I mean as the second operand of LIKE.
The error is not from the table function itself.

Regards
Michel

[Updated on: Mon, 15 October 2012 14:35]

Report message to a moderator

Re: Query Help with table function [message #568683 is a reply to message #568681] Mon, 15 October 2012 14:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2086
Registered: January 2010
Senior Member
You don't need pipelined function at all:

with tmp_tbl as (
                 select 1 as empno, 'Dupe Within Feed' audit_errors from dual union all
                 select 2 as empno, 'Already in DB' audit_errors from dual union all
                 select 3 as empno, 'Invalid state' audit_errors from dual
                )
select  *
  from  tmp_tbl
  where not exists (
                    with p as (
                               select '%Dupe Within Feed,%Already in DB' list from dual
                              )
                    select  1
                      from  p
                      where upper(audit_errors) like upper(regexp_substr(list,'[^,]+',1,level))
                      connect by level <= length(regexp_replace(list,'[^,]')) + 1
                   )
/

     EMPNO AUDIT_ERRORS
---------- ----------------
         3 Invalid state

SQL> 


And if you are on 11g:

with tmp_tbl as (
                 select 1 as empno, 'Dupe Within Feed' audit_errors from dual union all
                 select 2 as empno, 'Already in DB' audit_errors from dual union all
                 select 3 as empno, 'Invalid state' audit_errors from dual
                )
select  *
  from  tmp_tbl
  where not exists (
                    with p as (
                               select '%Dupe Within Feed,%Already in DB' list from dual
                              )
                    select  1
                      from  p
                      where upper(audit_errors) like upper(regexp_substr(list,'[^,]+',1,level))
                      connect by level <= regexp_count(list,',') + 1
                   )
/

     EMPNO AUDIT_ERRORS
---------- ----------------
         3 Invalid state

SQL> 


SY.
Re: Query Help with table function [message #570869 is a reply to message #568683] Fri, 16 November 2012 13:49 Go to previous message
sspn2010
Messages: 147
Registered: October 2008
Senior Member
Thank you all for the help.

Regards
Sri
Previous Topic: How to pass parameter in a View?
Next Topic: Query help
Goto Forum:
  


Current Time: Fri Dec 26 09:07:40 CST 2014

Total time taken to generate the page: 0.07236 seconds