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:

ALTER VIEW "dba"."vArtProps"
AS
  SELECT 
  artikel.artsiteid,
  artikel.artnum,
  l_fuqty.npercolli,
  l_fuqty.siteid1,
  l_fuqty.id1,
  l_fuqty.n1,
  l_fuqty.siteid2,
  l_fuqty.id2,
  l_fuqty.n2,
  l_fuqty.siteid3,
  l_fuqty.id3,
  l_fuqty.n3,
  l_fuqty.k1,
  l_fuqty.k2,
  l_fuqty.k3,
  l_fuqty.F2to1,
  l_fuqty.F3to2,
  l_fuqty.CntNperColli,
  l_fuqty.FuType_id,
  l_fuqty.nLaag,
  l_fuqty.nDc
  FROM artikel,
  LATERAL( G_S_FuQtyCalcEquiv(        
        'a',
        artikel.artsiteid,
        artikel.artnum,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        10,
        1,
        null,
        'BRO2',
        null,
        null,
        null,
        null,
        null,
        null
             ) WITH (
        npercolli integer,
        siteid1 integer,
        id1 integer,
        n1 decimal(30,6),
        siteid2 integer,
        id2 integer,
        n2 decimal(30,6),
        siteid3 integer,
        id3 integer,
        n3 decimal(30,6),
        k1 decimal(30,6),
        k2 decimal(30,6),
        k3 decimal(30,6),
        F2to1 integer,
        F3to2 integer,
        CntNperColli decimal(30,6),
        FuType_id integer,
        nLaag integer,
        nDc integer
           ) 
          ) l_fuqty

asked 14 Jun '16, 17:09

Franky's gravatar image

Franky
6115
accept rate: 0%


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

answered 15 Jun '16, 04:02

Volker%20Barth's gravatar image

Volker Barth
29.6k293444650
accept rate: 32%

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

×25
×5

question asked: 14 Jun '16, 17:09

question was seen: 213 times

last updated: 15 Jun '16, 11:20