Using Derived tables in views

[Former Member]

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

[VolkerBarth · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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

[Former Member]

This one is a little hard to gleen from the docs but ... from one internal source I have

"LATERAL(proc(T.A))" is a short hand for "LATERAL( select * from proc(T.A))"

and the "select * from proc(...)" is not allowed in versions before 16.

In the V16 Create View article you will find the sentence

"SELECT * can be used in the main query, a subquery, a derived table, or a subselect of the CREATE VIEW statement."

which is missing from the V12 Create View article but in the latter article you will find the restriction:

"SELECT * can only be used in the main query of the CREATE VIEW statement. Derived tables and subqueries must use full expressions in the SELECT list, rather than the * operator. For example, . . . "

instead.

So that means, with v12, you would need to expand that lateral statement to be

create view vw_derived_test as select * from t1, lateral(select descr, descr2 from test(t1.id)) as r;

until you get to the next release (or newer). {the good tip about using APPLY not withstanding}

[VolkerBarth]

A little hard to gleen, I agree:)

Well, the v12 docs do state that, too:

Specifying LATERAL (table-expression) is equivalent to specifying LATERAL (SELECT * FROM table-expression).

and a procedure call with parameters is one of the possible table expressions.

And they also tell:

LATERAL ( table-expression ), which is a vendor extension. LATERAL ( select-statement ) is in the SQL/2008 standard as optional SQL language feature T491.

So the apparent work around is even more standard SQL:)

[VolkerBarth]

Here's another approach:

While LATERAL is somewhat more standard SQL (though SQL Anywhere has its vendor extensions), the APPLY operators work in a similar fashion.

And they do not show that particular issue:

create or replace view vw_derived_test
as
select * from t1 cross apply test(t1.id);

select * from vw_derived_test;

works for me, and it seems way easier to code and understand:)