This discussion is archived
3 Replies Latest reply: Nov 14, 2012 10:06 PM by Most Wanted!!!! RSS

Calling an  pipeline function in a Select query

Most Wanted!!!! Journeyer
Currently Being Moderated
Hello gurus ,

i have a query calling pipeline function
WITH t AS
     (SELECT dep_code, emp_id
        FROM test1
       WHERE dep_code = 'C1' AND emp_id = '123')
SELECT *
  FROM TABLE
          (CAST
              ((pk_get_emp_dtls.fn_t_get_emp_dtls (t.dep_code,
                                                   t.empid,
                                                   TRUNC (SYSDATE)
                                                  )
               ) AS ps_ot_emp_dtls
              )
          ),
       t;
in this above query i want to use the emp id ,dept code from the with clause as parameters in the function pk_get_emp_dtls.fn_t_get_emp_dtls

but error occures SQL command not ended properly



Regards,
Friend

Edited by: most wanted!!!! on Nov 14, 2012 6:17 AM
  • 1. Re: Calling an  pipeline function in a Select query
    Solomon Yakobson Guru
    Currently Being Moderated
    This is the case where table order matters. TABLE operator doesn't support forward references, therefore table t MUST be listed before TABLE operator. And there is no need to CAST:
    WITH t AS (
               SELECT  dep_code,
                       emp_id
                 FROM  test1
                 WHERE dep_code = 'C1'
                   AND emp_id = '123'
              )
    SELECT  *
      FROM  t,
            TABLE(
                  pk_get_emp_dtls.fn_t_get_emp_dtls(
                                                    t.dep_code,
                                                    t.empid,
                                                    TRUNC(SYSDATE)
                                                   )
                 )
    /
    SY.
  • 2. Re: Calling an  pipeline function in a Select query
    BluShadow Guru Moderator
    Currently Being Moderated
    I see Solomon beat me to it...
    SQL> create or replace type o_emp as object (empno number, ename varchar2(10))
      2  /
    
    Type created.
    
    SQL>
    SQL> create or replace type t_emp as table of o_emp
      2  /
    
    Type created.
    
    SQL>
    SQL> create or replace function get_emp(p_deptno in number) return t_emp pipelined as
      2    v_emp o_emp := o_emp(null,null);
      3    cursor cur_emp is
      4      select empno, ename
      5      from   emp
      6      where  deptno = p_deptno;
      7  begin
      8    for i in cur_emp
      9    loop
     10      v_emp.empno := i.empno;
     11      v_emp.ename := i.ename;
     12      pipe row (v_emp);
     13    end loop;
     14    return;
     15  end;
     16  /
    
    Function created.
    
    SQL>
    SQL>
    SQL> with t as (select deptno from dept where dname = 'SALES')
      2  select x.*
      3  from   t, table(get_emp(t.deptno)) x
      4  /
    
         EMPNO ENAME
    ---------- ----------
          7499 ALLEN
          7521 WARD
          7654 MARTIN
          7698 BLAKE
          7844 TURNER
          7900 JAMES
    
    6 rows selected.
    
    SQL> with t as (select deptno from dept where dname = 'SALES')
      2  select x.*
      3  from   table(get_emp(t.deptno)) x, t
      4  /
    from   table(get_emp(t.deptno)) x, t
                         *
    ERROR at line 3:
    ORA-00904: "T"."DEPTNO": invalid identifier
  • 3. Re: Calling an  pipeline function in a Select query
    Most Wanted!!!! Journeyer
    Currently Being Moderated
    thank for the guidance gurus :)

    Regards
    Friend

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points