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 |
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
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 '18, 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 '18, 06:41)
Volker Barth
|