SAP SQL Anywhere

Question

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

Accepted Answer

1

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 Barth
39.3k●354●538●808
accept rate: 33%

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

flat view

SAP SQL Anywhere

Unable to create a view "Derived table x has no name for column y"

Follow this question

Related questions