This discussion is archived
10 Replies Latest reply: Jan 21, 2010 2:07 AM by BluShadow RSS

function output in a select statement

user483578 Newbie
Currently Being Moderated
I have written a function(get_cols) which returns the following string (this string is created dynamically from the fuctions depending on the rows of the tables)
the output of the function is;
MAX (CASE WHEN field_code = 'test_pho' THEN VALUE END ) AS test_pho
,MAX (CASE WHEN field_code = 'ESN' THEN VALUE END ) AS ESN
,MAX (CASE WHEN field_code = 'IMSI' THEN VALUE END ) AS IMSI
,MAX (CASE WHEN field_code = 'PHONE_NO' THEN VALUE END ) AS PHONE_NO


What I need to do is to use this as it is in a another select statement like;
(1)
select
empno,
MAX (CASE WHEN field_code = 'test_pho' THEN VALUE END ) AS test_pho
,MAX (CASE WHEN field_code = 'ESN' THEN VALUE END ) AS ESN
,MAX (CASE WHEN field_code = 'IMSI' THEN VALUE END ) AS IMSI
,MAX (CASE WHEN field_code = 'PHONE_NO' THEN VALUE END ) AS PHONE_NO
from my_employee e, my_columns c
where e.emp_no = c.emp_no
and c.emp_no = '100003'
group by empno



function returns the correct output, but when i call the function in the select like below it get it as a whole string and doesn't give the correct output
(2)
select empno, get_cols('100003')
from my_employee e, my_columns c
where e.emp_no = c.emp_no
and c.emp_no = '100003'

how can i get the output of the function to the select as separate line as shown is above(1)
When I get the above output separately and give in the select as above (1) it gives the correct output I want ??

any help please
  • 1. Re: function output in a select statement
    494599 Pro
    Currently Being Moderated
    Are you getting any error ??


    ******* EDITED ***********

    Also please elaborate more about your output you want.

    Edited by: Gj on Jan 20, 2010 11:22 AM
  • 2. Re: function output in a select statement
    user483578 Newbie
    Currently Being Moderated
    Hi Gj

    output of the function is;
    MAX (CASE WHEN field_code = 'test_pho' THEN VALUE END ) AS test_pho
    ,MAX (CASE WHEN field_code = 'ESN' THEN VALUE END ) AS ESN
    ,MAX (CASE WHEN field_code = 'IMSI' THEN VALUE END ) AS IMSI
    ,MAX (CASE WHEN field_code = 'PHONE_NO' THEN VALUE END ) AS PHONE_NO

    The above output is dynamic.

    I want to include this in a select statement (as columns) with some other column names...as given below;
    select
    empno,
    MAX (CASE WHEN field_code = 'test_pho' THEN VALUE END ) AS test_pho
    ,MAX (CASE WHEN field_code = 'ESN' THEN VALUE END ) AS ESN
    ,MAX (CASE WHEN field_code = 'IMSI' THEN VALUE END ) AS IMSI
    ,MAX (CASE WHEN field_code = 'PHONE_NO' THEN VALUE END ) AS PHONE_NO
    from my_employee e, my_columns c
    where e.emp_no = c.emp_no
    and c.emp_no = '100003'
    group by empno;

    for example the output will be should be;
    empno test_pho ESN IMSI PHONE
    10003, 12345, esn_1, imsi_1, 123451


    when I call the functionlike below....
    select empno, get_cols('100003')
    from my_employee e, my_columns c
    where e.emp_no = c.emp_no
    and c.emp_no = '100003'

    Hope u got it...and will be able to help me .. :-)
  • 3. Re: function output in a select statement
    494599 Pro
    Currently Being Moderated
    Try this
     create or replace function get_cols (Field_code_p varchar, emp_no_p varchar)
    return varchar2
    is
         value_v          varchar2(100);
    begin
         select value into value_v from my_columns where field_code = field_code_p and emp_no = emp_no_p;
         return value_v;
    end;
    
    
    select
         emp_no,
         get_cols ('test_pho', emp_no) Test_pho,
         get_cols ('ESN', emp_no) ESN,
         get_cols ('IMSI', emp_no) IMSI,
         get_cols ('PHONE_NO', emp_no) PHONE_NO
    from 
         my_employee 
    where 
         emp_no = '100003';
     
    Function will return only one value at a time.

    Hope this will help you ....
  • 4. Re: function output in a select statement
    user483578 Newbie
    Currently Being Moderated
    fields are coming dynamically my get_col function is given below ;

    As give by you I cannot pass the field code as 'testpho', 'ESN', IMSI' etc etc.. as those are dynamic and will vary for the given primary_value
  • 5. Re: function output in a select statement
    494599 Pro
    Currently Being Moderated
    Ohh Ok ... In that case you can't use directly the function output as a columns in query you will have to use dynamic sql and sys reference cursor in that case like:
    CREATE OR REPLACE PACKAGE Types AS 
      TYPE cursor_type IS REF CURSOR;
    END Types; 
    /
    
    
    
    create or replace Procedure GET_DYNAMIC_FIELDS 
              (p_primary_value in dlg_t_service_component.primary_value%TYPE DEFAULT '1000003',
               p_recordset      out types.cursor_type) 
    AS
         field_list           varchar2(2000);
         SQL_STR_v          varchar2(4000);
         CURSOR c1 IS
              SELECT     
                   DISTINCT',MAX (CASE WHEN field_code = '''||     field_code||     ''' THEN VALUE END ) AS '|| field_code ||chr(10)     AS cur_row
              FROM     
                   dlg_t_package_service ps, 
                   dlg_t_service_component sc, 
                   dlg_t_service_component_data scd , 
                   dlg_t_ser_compdata_tempate sct
              WHERE 
                   ps.pkg_service_id = sc.pkg_service_id
                   AND sc.service_component_id = scd.service_component_id
                   AND scd.field_id = sct.field_id
                   AND ps.service_id = sct.service_id
                   AND sc.primary_value = p_primary_value ; --'1000003';
                   field_list VARCHAR2(4000) := NULL;
    
    BEGIN 
         FOR cur_rec IN c1 LOOP
              field_list:= field_list ||cur_rec.cur_row;
         END LOOP;
         field_list := LTRIM(field_list,',');
    
    
         SQL_STR_V := 'SELECT 
                        ps.profile_pkg_id, 
                        ps.service_id, 
                        sc.service_component_id, 
                        sc.primary_value,
                        '|| field_list ||'
                   FROM 
                        dlg_t_package_service ps, 
                        dlg_t_service_component sc, 
                        dlg_t_service_component_data scd , 
                        dlg_t_ser_compdata_tempate sct
                   WHERE 
                        ps.pkg_service_id = sc.pkg_service_id
                        AND sc.service_component_id = scd.service_component_id
                        AND scd.field_id = sct.field_id
                        AND ps.service_id = sct.service_id
                        AND sc.primary_value = '1000003'
                   GROUP BY 
                        ps.profile_pkg_id, 
                        ps.service_id, 
                        sc.service_component_id, 
                        sc.primary_value';
    
         OPEN p_recordset for SQL_STR_v;
    
    EXCEPTION
         WHEN others THEN
              RAISE; 
    END;
    You can optimize the cursor sql for field_list. if you are in java then you can use following code to fetch the output
    import java.sql.*;
    import oracle.jdbc.*;
    
    public class TestResultSet  {
      public TestResultSet() {
        try {
          DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
          Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@w2k1", "scott", "tiger");
          CallableStatement stmt = conn.prepareCall("BEGIN GET_DYNAMIC_FIELDS (?, ?); END;");
          stmt.setInt(1, 30); // DEPTNO
          stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR
          stmt.execute();
          ResultSet rs = ((OracleCallableStatement)stmt).getCursor(2);
          while (rs.next()) {
            System.out.println(rs.getString("ename") + ":" + rs.getString("empno") + ":" + rs.getString("deptno")); 
          }
          rs.close();
          rs = null;
          stmt.close();
          stmt = null;
          conn.close();
          conn = null;
        }
        catch (SQLException e) {
          System.out.println(e.getLocalizedMessage());
        }
      }
    
      public static void main (String[] args) {
        new TestResultSet();
      }
    } 
    If you are in .net you can use following code to fetch the output
    Dim conn, cmd, rs
    
    Set conn = Server.CreateObject("adodb.connection")
    conn.Open "DSN=TSH1;UID=scott;PWD=tiger"
    
    Set cmd = Server.CreateObject ("ADODB.Command")
    Set cmd.ActiveConnection = conn
    cmd.CommandText = "GetEmpRS"
    cmd.CommandType = 4 'adCmdStoredProc
    
    Dim param1
    Set param1 = cmd.CreateParameter ("deptno", adInteger, adParamInput)
    cmd.Parameters.Append param1
    param1.Value = 30
    
    Set rs = cmd.Execute
    
    Do Until rs.BOF Or rs.EOF
      -- Do something
      rs.MoveNext
    Loop
    
    rs.Close
    conn.Close
    Set rs     = nothing
    Set param1 = nothing
    Set cmd    = nothing
    Set conn   = nothing
    If you want to see the out put in SQLPLUS then you have to use PLSQL
    SET SERVEROUTPUT ON SIZE 1000000
    DECLARE
      v_cursor  Types.cursor_type;
      v_ename   emp.ename%TYPE;
      v_empno   emp.empno%TYPE;
      v_deptno  emp.deptno%TYPE;
    BEGIN
      GET_DYNAMIC_FIELDS  (p_deptno    => 30,
                p_recordset => v_cursor);
                
      LOOP 
        FETCH v_cursor
        INTO  v_ename, v_empno, v_deptno;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_ename || ' | ' || v_empno || ' | ' || v_deptno);
      END LOOP;
      CLOSE v_cursor;
    END;
    /
    you have to change all the calling procedure name and parameter values according to your requirment. I have just copied all those from the link http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php


    Hope this will help.

    Edited by: Gj on Jan 20, 2010 1:42 PM
  • 6. Re: function output in a select statement
    BluShadow Guru Moderator
    Currently Being Moderated
    You could use a pipeline function to return multiple columns. Here's an example of such a thing...
    CREATE OR REPLACE TYPE myrec AS OBJECT
    ( col1   VARCHAR2(10),
      col2   VARCHAR2(10)
    )
    /
    
    CREATE OR REPLACE TYPE myrectable AS TABLE OF myrec
    /
    
    CREATE OR REPLACE FUNCTION pipedata(p_str IN VARCHAR2) RETURN myrectable PIPELINED IS
      v_str VARCHAR2(4000) := REPLACE(REPLACE(p_str, '('),')');
      v_obj myrec := myrec(NULL,NULL);
    BEGIN
      LOOP
        EXIT WHEN v_str IS NULL;
        v_obj.col1 := SUBSTR(v_str,1,INSTR(v_str,',')-1);
        v_str := SUBSTR(v_str,INSTR(v_str,',')+1);
        IF INSTR(v_str,',')>0 THEN
          v_obj.col2 := SUBSTR(v_str,1,INSTR(v_str,',')-1);
          v_str := SUBSTR(v_str,INSTR(v_str,',')+1);
        ELSE
          v_obj.col2 := v_str;
          v_str := NULL;
        END IF;
        PIPE ROW (v_obj);
      END LOOP;
      RETURN;
    END;
    /
    
    SQL> select *
      2  from table(pipedata('(1,2),(3,4),(5,6)'));
    
    COL1       COL2
    ---------- ----------
    1          2
    3          4
    5          6
    This is a completely flexible method allowing you to define what columns should be returned and what functionality populates those columns, whether that is fixed data as in the example shown or whether that data comes from another table during the query.

    However, you should really consider if there is a need to be using a function in your query or whether it would be better to just code it directly as SQL in the select statement itself. The general rule is that you should maximise SQL and minimise PL/SQL and calling a PL/SQL function during a query should only be a last resort if you really cannot carry out the functionality directly in SQL. If you do use a PL/SQL function then you will cause context switching between the SQL engine and the PL/SQL engine and this can have a very noticable impact on the performance of your query.
  • 7. Re: function output in a select statement
    494599 Pro
    Currently Being Moderated
    Thanks BluShadow ... I missed pipeline functions
  • 8. Re: function output in a select statement
    user483578 Newbie
    Currently Being Moderated
    ThanX a lot Gj & BluShadow ....
    That helped me a lot .. will try this methods ...

    Cheers
  • 9. Re: function output in a select statement
    533748 Newbie
    Currently Being Moderated
    Hi BluShadow,

    Your solution seem interesting. Can you explain how can this be used to produce the required output ? Do we need to maintain a separate my_columns table ?
  • 10. Re: function output in a select statement
    BluShadow Guru Moderator
    Currently Being Moderated
    josleen wrote:
    Hi BluShadow,

    Your solution seem interesting. Can you explain how can this be used to produce the required output ? Do we need to maintain a separate my_columns table ?
    Not quite sure what you are asking?

    As with any query, the output columns have to be defined at design time, you cannot have the number of columns dynamically generated based on the data. So, if you said you wanted to pivot data from rows to columns and the number of possible values in those rows could change, you cannot pivot those to columns unless you are expecting those values or have allowed for maximum number of values.

    Basic example... Let say we have the following data:
    SQL> select * from dept;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    And we want to pivot that data to put the departments as columns rather than rows. We can simply do something like:
    SQL> ed
    Wrote file afiedt.buf
    
      1  select max(decode(deptno,10,dname)) as dname_10
      2        ,max(decode(deptno,10,loc)) as   loc_10
      3        ,max(decode(deptno,20,dname)) as dname_20
      4        ,max(decode(deptno,20,loc)) as   loc_20
      5        ,max(decode(deptno,30,dname)) as dname_30
      6        ,max(decode(deptno,30,loc)) as   loc_30
      7        ,max(decode(deptno,40,dname)) as dname_40
      8        ,max(decode(deptno,40,loc)) as   loc_40
      9* from dept
    SQL> /
    
    DNAME_10       LOC_10        DNAME_20       LOC_20        DNAME_30       LOC_30        DNAME_40       LOC_40
    -------------- ------------- -------------- ------------- -------------- ------------- -------------- -------------
    ACCOUNTING     NEW YORK      RESEARCH       DALLAS        SALES          CHICAGO       OPERATIONS     BOSTON
    
    SQL>
    However if a further department is added:
    SQL> insert into dept values (50, 'IT SUPPORT', 'LONDON');
    
    1 row created.
    
    SQL> select max(decode(deptno,10,dname)) as dname_10
      2        ,max(decode(deptno,10,loc)) as   loc_10
      3        ,max(decode(deptno,20,dname)) as dname_20
      4        ,max(decode(deptno,20,loc)) as   loc_20
      5        ,max(decode(deptno,30,dname)) as dname_30
      6        ,max(decode(deptno,30,loc)) as   loc_30
      7        ,max(decode(deptno,40,dname)) as dname_40
      8        ,max(decode(deptno,40,loc)) as   loc_40
      9  from dept
     10  /
    
    DNAME_10       LOC_10        DNAME_20       LOC_20        DNAME_30       LOC_30        DNAME_40       LOC_40
    -------------- ------------- -------------- ------------- -------------- ------------- -------------- -------------
    ACCOUNTING     NEW YORK      RESEARCH       DALLAS        SALES          CHICAGO       OPERATIONS     BOSTON
    
    SQL>
    we obviously don't get to see the new data, unless we change our query to add this expected additional column(s) in...
    SQL> ed
    Wrote file afiedt.buf
    
      1  select max(decode(deptno,10,dname)) as dname_10
      2        ,max(decode(deptno,10,loc)) as   loc_10
      3        ,max(decode(deptno,20,dname)) as dname_20
      4        ,max(decode(deptno,20,loc)) as   loc_20
      5        ,max(decode(deptno,30,dname)) as dname_30
      6        ,max(decode(deptno,30,loc)) as   loc_30
      7        ,max(decode(deptno,40,dname)) as dname_40
      8        ,max(decode(deptno,40,loc)) as   loc_40
      9        ,max(decode(deptno,50,dname)) as dname_50
     10        ,max(decode(deptno,50,loc)) as   loc_50
     11* from dept
    SQL> /
    
    DNAME_10       LOC_10        DNAME_20       LOC_20        DNAME_30       LOC_30        DNAME_40       LOC_40        DNAME_50       LOC_50
    -------------- ------------- -------------- ------------- -------------- ------------- -------------- ------------- -------------- -------------
    ACCOUNTING     NEW YORK      RESEARCH       DALLAS        SALES          CHICAGO       OPERATIONS     BOSTON        IT SUPPORT     LONDON
    
    SQL>
    Now, rather than having a messy SQL statment with lots of max(decode... statements we can provide a pipelined function to return the same thing...
    SQL> ed
    Wrote file afiedt.buf
    
      1  CREATE OR REPLACE TYPE mydepts AS OBJECT
      2  ( dname_10   VARCHAR2(14),
      3    loc_10     VARCHAR2(13),
      4    dname_20   VARCHAR2(14),
      5    loc_20     VARCHAR2(13),
      6    dname_30   VARCHAR2(14),
      7    loc_30     VARCHAR2(13),
      8    dname_40   VARCHAR2(14),
      9    loc_40     VARCHAR2(13),
     10    dname_50   VARCHAR2(14),
     11    loc_50     VARCHAR2(13)
     12* )
     13  /
    
    Type created.
    
    SQL> ed
    Wrote file afiedt.buf
    
      1* CREATE OR REPLACE TYPE mydepttable AS TABLE OF mydepts
    SQL> /
    
    Type created.
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  CREATE OR REPLACE FUNCTION alldepts RETURN mydepttable PIPELINED IS
      2    v_obj mydepts := mydepts(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
      3    CURSOR cur_depts IS
      4      select deptno, dname, loc from dept;
      5  BEGIN
      6    FOR i IN cur_depts
      7    LOOP
      8      CASE i.deptno
      9       WHEN 10 THEN v_obj.dname_10 := i.dname; v_obj.loc_10 := i.loc;
     10       WHEN 20 THEN v_obj.dname_20 := i.dname; v_obj.loc_20 := i.loc;
     11       WHEN 30 THEN v_obj.dname_30 := i.dname; v_obj.loc_30 := i.loc;
     12       WHEN 40 THEN v_obj.dname_40 := i.dname; v_obj.loc_40 := i.loc;
     13       WHEN 50 THEN v_obj.dname_50 := i.dname; v_obj.loc_50 := i.loc;
     14      ELSE NULL;
     15      END CASE;
     16    END LOOP;
     17    PIPE ROW (v_obj);
     18    RETURN;
     19* END;
    SQL> /
    
    Function created.
    
    SQL> select *
      2  from table(alldepts());
    
    DNAME_10       LOC_10        DNAME_20       LOC_20        DNAME_30       LOC_30        DNAME_40       LOC_40        DNAME_50       LOC_50
    -------------- ------------- -------------- ------------- -------------- ------------- -------------- ------------- -------------- -------------
    ACCOUNTING     NEW YORK      RESEARCH       DALLAS        SALES          CHICAGO       OPERATIONS     BOSTON        IT SUPPORT     LONDON
    
    SQL>
    So, now there is a single function that acts like a table and does the functionality of pivoting the data. What the function actually does to generate the data is entirely up to you whether that is obtaining data from different tables or some PL/SQL code processing to perform some complex algorithmic type thing on the data from a passed in parameter etc. The point of the pipeline function is that it can return multiple columns of data and act as if it is a table that can be queried against, however it still remains that the output columns must be known at design time. This is actually a requirement of the SQL engine, as you cannot make a dynamic function that returns X number of columns based on data.

    There is however a technique that can allow you to dynamically generate a number of columns based on data, but it involves getting a little more under the hood of Oracle and interfacing with the ODCI interface, actually using a pipelined funcion technique in conjunction with, in laymans terms, stepping in at the point the query is executing and telling oracle what columns this pipelined function is going to return, but at the same time as defining the returned columns.

    An example of doing this is given by AScheffer on this thread: How to pipeline a function with a dynamic number of columns?

    ... but you really are getting into a complex world if you try and write your queries this way, just to try and make generic queries with dynamic columns.

    In essence, the actual business need to actually return a dynamic number of columns is very slim as most applications, whether that is a user facing interface or report generator etc. will be expecting certain data columns to be returned to expected columns or fields within them. If you really want to dynamically pivot data with an unknown number of columns it is usually best to let things like reporting tools deal with this area as that is what they are best at, and don't try and do it within SQL.

Legend

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