3 Replies Latest reply: Nov 15, 2012 12:06 AM by Most Wanted!!!! RSS

    Calling an  pipeline function in a Select query

    Most Wanted!!!!
      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
          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
            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!!!!
              thank for the guidance gurus :)

              Regards
              Friend