11 Replies Latest reply: Feb 8, 2012 11:26 AM by sb92075 RSS

    pipelined function, select from table t1 or t2 depending on user's choise

    marco
      Hi all,

      My need is to select data from table t1 or t2 depending on user's choise using pipelined function. You can find my first guess below. Maybe someone can help me to save some code, I mean getting the same result without writing "PIPE ROW" twice.
      FUNCTION fn_indicators (p_datbeg date, p_datend date, p_indicatorid number) return cl_risk_act pipelined IS
      v_obj cl_user_type;
      BEGIN
      
      case when p_indicatorid = 1 then 
      FOR e IN (
      
      select trunc(sysdate-1) as adate, 0 as cid, 0 as indicatorid, '0' as code, '0' as indicatorname, 0 as value, 0 as cnt, '0' as cname from dual
      
                    )
      LOOP
      v_obj.adate              := e.adate;
      v_obj.cid                  := e.cid;
      v_obj.indicatorid       := e.indicatorid;
      v_obj.code               := e.code;
      v_obj.indicatorname  := e.indicatorname;
      v_obj.value              := e.value;
      v_obj.cnt                 := e.cnt;
      v_obj.cname            := e.cname;
      PIPE ROW (v_obj);
      END LOOP;
      
      when p_indicatorid = 2 then 
      FOR e IN (
      
      select trunc(sysdate-2) as adate, 1 as cid, 1 as indicatorid, '1' as code, '1' as indicatorname, 1 as value, 1 as cnt, '1' as cname from dual
      
                    )
      LOOP
      v_obj.adate              := e.adate;
      v_obj.cid                  := e.cid;
      v_obj.indicatorid       := e.indicatorid;
      v_obj.code               := e.code;
      v_obj.indicatorname  := e.indicatorname;
      v_obj.value              := e.value;
      v_obj.cnt                 := e.cnt;
      v_obj.cname            := e.cname;
      PIPE ROW (v_obj);
      END LOOP;
      
      end case;
      
      RETURN;
      end;
        • 1. Re: pipelined function, select from table t1 or t2 depending on user's choise
          jeneesh
          FUNCTION fn_indicators (p_datbeg date, p_datend date, p_indicatorid number) return cl_risk_act pipelined IS
          v_obj cl_user_type;
          BEGIN
           
          FOR e IN (
           
          select trunc(sysdate-1) as adate, 0 as cid, 0 as indicatorid, '0' as code, '0' as indicatorname, 0 as value, 0 as cnt, '0' as cname from dual
          where p_indicatorid = 1
          union all
          select trunc(sysdate-2) as adate, 1 as cid, 1 as indicatorid, '1' as code, '1' as indicatorname, 1 as value, 1 as cnt, '1' as cname from dual
          where p_indicatorid = 2 
                        )
          LOOP
          v_obj.adate              := e.adate;
          v_obj.cid                  := e.cid;
          v_obj.indicatorid       := e.indicatorid;
          v_obj.code               := e.code;
          v_obj.indicatorname  := e.indicatorname;
          v_obj.value              := e.value;
          v_obj.cnt                 := e.cnt;
          v_obj.cname            := e.cname;
          PIPE ROW (v_obj);
          END LOOP;
           
          RETURN;
          end;
          But I dont understand - For what the code is used..
          Is this the real code?
          • 2. Re: pipelined function, select from table t1 or t2 depending on user's choise
            marco
            Thanks jeneesh! It's sample code.
            • 3. Re: pipelined function, select from table t1 or t2 depending on user's choise
              Nikolay Savvinov
              Hi,

              why pipelined function? From your code it looks like for any values of the arguments in will return at most one row? Can't you use an object-type function, or a procedure with several out parameters? Why loop over SELECT ... FROM DUAL which only returns why row?


              And of course there is no need to do everything twice since you can use generic expressions with p_indicatorid (e.g. trunc(sysdate-1) as adate => trunc(sysdate - p_indicatorid)).

              If this is actual code, then it's a mess, if this is an example, then I don't get it.

              Best regards,
              Nikolay
              • 4. Re: pipelined function, select from table t1 or t2 depending on user's choise
                Solomon Yakobson
                jeneesh wrote:
                select trunc(sysdate-1) as adate, 0 as cid, 0 as indicatorid, '0' as code, '0' as indicatorname, 0 as value, 0 as cnt, '0' as cname from dual
                where p_indicatorid = 1
                union all
                select trunc(sysdate-2) as adate, 1 as cid, 1 as indicatorid, '1' as code, '1' as indicatorname, 1 as value, 1 as cnt, '1' as cname from dual
                where p_indicatorid = 2
                Or simply:
                select  trunc(sysdate - p_indicatorid) as adate,
                        p_indicatorid - 1 as cid,
                        p_indicatorid - 1 as indicatorid,
                        p_indicatorid - 1 as code,
                        p_indicatorid - 1 as indicatorname,
                        p_indicatorid - 1 as value,
                        p_indicatorid - 1 as cnt,
                        p_indicatorid - 1 as cname
                  from  dual
                SY.
                • 5. Re: pipelined function, select from table t1 or t2 depending on user's choise
                  jeneesh
                  :)

                  I replied assuming that the code is just a sample..

                  I really didnt understand his code.
                  • 6. Re: pipelined function, select from table t1 or t2 depending on user's choise
                    marco
                    Although my beginning question is answered I still need help...

                    The task is to select data (using pipelined function) from real table t1 or from table t2 generated by another pipelined function.

                    So this statement performs selection from both tables (real and generated by pipelined function), but showing only results from one of them (depending on user's choise).
                    select trunc(sysdate-1) as adate, 0 as cid, 0 as indicatorid, 
                    '0' as code, '0' as indicatorname, 0 as value, 
                    0 as cnt, '0' as cname from dual --t1 (real)
                    where p_indicatorid = 1
                    union all
                    select trunc(sysdate-2) as adate, 1 as cid, 1 as indicatorid, 
                    '1' as code, '1' as indicatorname, 1 as value, 
                    1 as cnt, '1' as cname from dual --t2 (pipelined)
                    where p_indicatorid = 2
                    I've tested it watching time consumption, so if selection from real table performs about 1 second and selection from pipelined function performs about 5 seconds then in real life quoted statement works about 6 seconds no matter what is user's choise.

                    So my need is to prevent selection from table t2 (generated by pipelined function) when user's choise is p_indicatorid = 1.

                    Any ideas?
                    • 7. Re: pipelined function, select from table t1 or t2 depending on user's choise
                      JustinCave
                      The simplest option since you're writing a pipelined table function would seem to be to just write an IF, i.e.
                      IF p_indicatorid = 1
                      THEN
                        <<select from real table>>
                      ELSE
                        <<select from pipelined table function>>
                      END IF;
                      Justin
                      • 8. Re: pipelined function, select from table t1 or t2 depending on user's choise
                        marco
                        Justin, your way is equal to my original solution. In real life code is too big to keep it twise. This is also poor for future support (programmer needs to edit code twise). So I'm still thinking...
                        • 9. Re: pipelined function, select from table t1 or t2 depending on user's choise
                          JustinCave
                          I'm not sure I understand-- why would you need to duplicate any code? You put the code in a procedure or you open a cursor in the IF statement that you process outside of the loop
                          SQL> ed
                          Wrote file afiedt.buf
                          
                            1  declare
                            2    l_cursor sys_refcursor;
                            3    l_flag   number := 1;
                            4  begin
                            5    if( l_flag = 1 )
                            6    then
                            7      open l_cursor for select * from emp;
                            8    else
                            9      open l_cursor for select * from emp where empno < 10000;
                           10    end if;
                           11* end;
                           12  /
                          
                          PL/SQL procedure successfully completed.
                          or any number of other ways to structure code so that you're not writing the same code twice.

                          Justin
                          • 10. Re: pipelined function, select from table t1 or t2 depending on user's choise
                            marco
                            Justin,

                            In my real code table real_t1 or pipelinedfn_t2 is joined to other tables, so surely real code is bigger than sample one. It is not wise to keep code twise with only one table name changed. I think about dynamic sql to choose from two tables:
                            with t1 as 
                            (select * from real_t1
                            ), 
                            t2 as 
                            (select * from pipelinedfn_t2
                            ),
                            t3 as (
                            [dynamic selection of t1 or t2 depending on user's choise]
                            )
                            select * from t3, t... where...
                            I don't know correct syntax.
                            Any ideas?
                            • 11. Re: pipelined function, select from table t1 or t2 depending on user's choise
                              sb92075
                              marco wrote:
                              Justin,

                              In my real code table real_t1 or pipelinedfn_t2 is joined to other tables, so surely real code is bigger than sample one. It is not wise to keep code twise with only one table name changed. I think about dynamic sql to choose from two tables:
                              with t1 as 
                              (select * from real_t1
                              ), 
                              t2 as 
                              (select * from pipelinedfn_t2
                              ),
                              t3 as (
                              [dynamic selection of t1 or t2 depending on user's choise]
                              )
                              select * from t3, t... where...
                              I don't know correct syntax.
                              Any ideas?
                              >
                              Justin,

                              In my real code table real_t1 or pipelinedfn_t2 is joined to other tables, so surely real code is bigger than sample one. It is not wise to keep code twise with only one table name changed. I think about dynamic sql to choose from two tables:
                              with t1 as 
                              (select * from real_t1
                              ), 
                              t2 as 
                              (select * from pipelinedfn_t2
                              ),
                              t3 as (
                              [dynamic selection of t1 or t2 depending on user's choise]
                              )
                              select * from t3, t... where...
                              I don't know correct syntax.
                              Any ideas?
                              Dynamic code does NOT scale.
                              Making the tradeoff of smaller code size for reduced performance is not one that I would make.