Hi I'm trying to create a view (my first actually), but I seem to be hitting a wall. Eventually I want to parameterize the view (or actually the in parameters to the stored proc), but I can't get it created in the first place with hardcoded parameters.
I want to "join" a table "artikel" with a stored procedure (G__S_FuQtyCalcEquiv) that returns a single row result set for each "artikel" row. The query (the part after "AS") itself runs fine when executed in ISQL, but whenever I try to create it as a view I get the following error:
Derived table 'l_fuqty' has no name for column 1
I really haven't got a clue what this refers to (especially as the query itself seems ok), below are some extra errorcodes and the code for constructing the view.
Any help is much appreciated.
SQLCODE: -163 SQLSTATE: 42000 SQL Statement:
asked 14 Jun '16, 17:09
What version do you use?
One simple solution would be to replace LATERAL with the CROSS APPLY operator, such as...
... FROM artikel CROSS APPLY G_S_FuQtyCalcEquiv( 'a', ...
Note, you can parametrize views with the help of connection-level variables created via CREATE VARIABE, such as
create view MyView as select * from MyTable where MyCol1 = @MyVar; -- client 101 create variable int @MyVar = 101; -- client 102 create variable int @MyVar = 102;
answered 15 Jun '16, 04:02