The forum will be down for maintenance at some point from Friday, November 16 at 19:00 EDT until Sunday, November 18 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

Hi all,
How can i get a list of alias column names of the result set for a query?

begin
  declare t_msg long varchar;
  set t_msg = 'begin select 123 as "col name 1",456 as "col name 2" end';
  message 'describe list: '+(select list(name) from sa_describe_query(t_msg));
end

result -> SqlCode: -894, SqlState: "0AW14", Message: "Plan cannot be generated for this type of statement"


begin
  declare t_msg long varchar;
  set t_msg = 'begin select 123 as "col name 1",456 as "col name 2" end';
  begin
    declare crsr cursor using t_msg;
    open crsr;
    message 'cursor list: '+(select list(name) from sa_describe_cursor('crsr'));
    close crsr;
  end
end

result -> cursor list: expression,expression

begin
  declare t_msg long varchar;
  set t_msg = 'begin select 123 as "col name 1",456 as "col name 2" end';
  drop procedure if EXISTS  tt;
  execute IMMEDIATE 'create procedure tt() begin '+t_msg+' end;';
  message 'procedure list: '+(select list(parm_name)
    from sysprocparm join sysprocedure
    where parm_type = 1 and proc_name = 'tt') to client;
end

result -> procedure list: col name 1,col name 2

Is there another way to get alias column names of the result set for a query without creating a procedure?

Regards,
Veselin Ivanov

asked 16 Feb, 04:36

Veselin%20Ivanov's gravatar image

Veselin Ivanov
44468
accept rate: 0%


It's way easier, you can use the procedure sa_describe_query() in the FROM clause and access its result set that way, such as:

select name
from sa_describe_query('select 123 as "col name 1", 456 as "col name 2"') sp
order by column_number;

which returns

col name 1
 col name 2    

permanent link

answered 16 Feb, 05:16

Volker%20Barth's gravatar image

Volker Barth
32.4k328476693
accept rate: 32%

edited 16 Feb, 05:17

For a simple query sa_describe_query returns column names but when the query is more complex it may return an SqlCode: -894 error. Possible option is to obtain the out parameters of a temporary procedure like a permanent procedure but I did not find any information in the documentation.

(16 Feb, 05:41) Veselin Ivanov
Replies hidden

IMHO, that is a misunderstanding. The procedure is meant to describe a single query but not a code block, so you cannot use it with BEGIN/END statements as you tried.

When using stored procedures, you need to use them as part of the FROM clause, not via a CALL statement. This works fine:

call sa_describe_query('select * from sa_conn_info() s');
(16 Feb, 06:41) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×425

question asked: 16 Feb, 04:36

question was seen: 219 times

last updated: 16 Feb, 06:41