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
|
