cancel
Showing results for 
Search instead for 
Did you mean: 

Using Derived tables in views

Former Member
3,497

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

View Entire Topic
VolkerBarth
Contributor
0 Kudos

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);
Former Member
0 Kudos

I am using 12.0.1.4231. I was attempting to use the WITH statement to specify the column list and that failed as well

Former Member
0 Kudos

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

VolkerBarth
Contributor
0 Kudos

Yes, I can confirm that with 12.0.1.4301. FWIW, it does not matter whether you use "select *" or "select t1.id, r.descr, r.descr2" in the view's definition.

VolkerBarth
Contributor
0 Kudos

FWIW, you can enclose your code snippets with a pre-tag-pair.