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 |
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); 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} A little hard to gleen, I agree:) Well, the v12 docs do state that, too:
and a procedure call with parameters is one of the possible table expressions. And they also tell:
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:) |