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

asked 07 Sep '15, 21:20

LoganTennis's gravatar image

LoganTennis
1217712
accept rate: 100%

edited 08 Sep '15, 02:12

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819


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);
permanent link

answered 08 Sep '15, 02:12

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 08 Sep '15, 02:21

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

(08 Sep '15, 02:15) LoganTennis

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

(08 Sep '15, 03:17) LoganTennis
Replies hidden

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.

(08 Sep '15, 04:23) Volker Barth

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

(08 Sep '15, 04:24) Volker Barth

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}

permanent link

answered 08 Sep '15, 12:02

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 32%

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:)

(09 Sep '15, 00:53) Volker Barth

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:)

permanent link

answered 08 Sep '15, 06:20

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×125
×31
×5
×5

question asked: 07 Sep '15, 21:20

question was seen: 3,036 times

last updated: 09 Sep '15, 00:53