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:
|
What version do you use? In case it is v12 or below: I guess the error is similar to the one discussed in that other FAQ - please look at Nick's answer particularly. 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; Thanks a million, that saved my day (or maybe a couple more). Indeed this was version 11, but I like the cross apply simplicity too!
(15 Jun '16, 05:51)
Franky
Replies hidden
Glad to hear that - feel free to accept that answer then (i.e. click on the checkmark...)
(15 Jun '16, 06:18)
Volker Barth
never noticed that checkmark before, learned something new again :-)
(15 Jun '16, 11:20)
Franky
|