on 2015 Sep 07 10:20 PM
I need to use a derived table ( derived from a stored procedure in a view ). I am using SQL Anywhere 12.
Below is an example and the resultant error.
Kind Regards,
Robert.
create table t1 ( id integer ); insert into t1 (id) values (1); insert into t1 (id) values (2); create procedure Test (IN pId integer) result (descr varchar(3)) begin if pId = 1 then select 'abc'; endif end; select * from t1, lateral(test(t1.id)) as r; create or replace view vw_derived_test as select t1.id, r.descr from t1, lateral(test(t1.id)) as r; select * from vw_derived_test;
gives error
Could not execute statement.
Derived table 'r' has no name for column 1
SQLCODE=-163, ODBC 3 State="42000"
Line 1, column 1
Request clarification before answering.
I've slightly modified the sample (just added delimiters and a final select over the view), and the code works with 16.0.0.2158.
With 12.0.1.4301, I get the same error message, so that might be a bug in that version.
However, you can resolve that by adding an explicit column list to the lateral derived query (because that's the meaning of the error message in my humble understanding), i.e. by defining the view as following:
create or replace view vw_derived_test as select t1.id, r.descr from t1, lateral(test(t1.id)) r(descr);
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Unfortunately. I tried this with more than 1 column in the return set and it broke again.
For example
create table t1 ( id integer ); insert into t1 (id) values (1); insert into t1 (id) values (2); create or replace procedure Test (IN pId integer) result (descr varchar(3), descr2 varchar(3)) begin if pId = 1 then select 'abc', 'def'; endif end; create or replace view vw_derived_test as select * from t1, lateral(test(t1.id)) as r(descr,descr2);
gives error
Could not execute statement. The SELECT list for the derived table 'r' has no expression to match 'descr2' SQLCODE=-812, ODBC 3 State="42000" Line 1, column 1
User | Count |
---|---|
65 | |
10 | |
8 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.