Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Red face Unanswered: HELP~~ Can't have column name on result when using stored procedure.

    I just start to learn how to write stored procedure of Informix.
    Does any one know how to show the column name on the final result?
    My result shows like below,

    (expression)

    100

    How can I give an alias name instead of (expression)?

    Here is my code.
    +++++++++++++++

    create procedure totalcnt(userid char(8))
    returning int;
    define cnt int;

    select count(*) into cnt from gltmdept
    where gldt_userid=userid;
    return cnt;
    end procedure;
    +++++++++++++++


    Anyone can help me..... I try thousands times!!!
    Thank you so much.


    CHIA

  2. #2
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    183
    Hmmm....

    Maybe not the best idea, but this should work;

    SELECT totalcnt('informix') as myfield
    FROM systables
    WHERE tabid = 1;

    Hope this helps a bit,

    Rob Prop

  3. #3
    Join Date
    Dec 2002
    Location
    Portland, OR, USA
    Posts
    26

    Re: HELP~~ Can't have column name on result when using stored procedure.

    Sounds to me as if you would like to see a column heading returned out of the stored procedure. Keep in mind that the stored procedure is an extra abstraction layer between you and the query. That means what you are seeing coming back is not what the SQL returns, but what you asked the procedure to return. That gives you power to control the results, but not directly from the column name itself.

    Try this, for example:
    +++++++++++++++

    create procedure totalcnt(userid char(8))
    returning char(8), int;
    define cnt int;
    define heading char(8);

    LET heading = "myheader";
    select count(*) into cnt from gltmdept
    where gldt_userid=userid;
    return heading, cnt;
    end procedure;
    +++++++++++++++

    By altering the size and contents of the var heading (and the size in your "returning" clause, you can pair any string you want with the results of the count when it comes back from the procedure...

    Best regards,
    Joe

  4. #4
    Join Date
    Apr 2004
    Posts
    2

    Re: HELP~~ Can't have column name on result when using stored procedure.

    Thank you, Joe.
    Although it's not exactly what I want, I appreciate your response.
    I think I can't ask procedure to do the same as what SQL returns....
    I know what I should do right now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •