Error creating view ...

Hi all,

I'm trying to create a view as follows, but get this errormessage below. The 
query itself runs without problems when pasted into ISQL. Does anybody know 
what is wrong here?

Thanks in advance,
Ton

Could not execute statement.
Derived table 'sp' has no name for column 1
SQLCODE=-163, ODBC 3 State="42000"
Line 1, column 1

create view V_artikel_fuqty as
select
artikel.artsiteid,artikel.artnum,
sp.npercolli,sp.siteid1,sp.id1,sp.n1,sp.siteid2,sp.id2,sp.n2,sp.siteid3,sp.id3,sp.n3,sp.k1,sp.k2,sp.k3,sp.F2to1,sp.F3to2,sp.cntnpercolli,sp.futype_id,sp.nlaag,sp.ndc
from
artikel,LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent > 0 then 'M' else 
'A' endif,artikel.artsiteid,artikel.artnum,null,
if artikel.isparent=10 then artikel.am_fk_fustsiteid else null endif,
if artikel.isparent=10 then artikel.am_fk_fustid else null endif,
null,
null,
null,
null,
if artikel.isparent=0 then 10 else if artikel.isparent = 10 then 20 else if 
artikel.isparent = 20 then null else null endif endif endif,
1.0,
if artikel.isparent=0 then 1 else artikel.fk_fuprmodel_id endif,
'BRO2',
null,
null,
if artikel.isparent=0 then null else 1.0 endif,
if artikel.isparent=0 then null else artikel.isparent endif,
1)) AS sp 


0
Ton
1/4/2011 9:13:39 PM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

11 Replies
555 Views

Similar Articles

[PageSpeed] 19

Still trying to get it to work:

The docs say I need to "Use a select list alias in the query specification 
or use a derived columns specifier" so I've tried the following
select list:

artikel.artsiteid as "artsiteid",artikel.artnum as "artnum",
sp.npercolli as "npercolli",sp.siteid1 as "siteid1",sp.id1 as "id1" ,sp.n1 
as "n1",sp.siteid2 as "siteid2",sp.id2 as "id2",sp.n2 as "n2",sp.siteid3 as 
"siteid3",sp.id3 as "id3",sp.n3 as "n3",sp.k1 as "k1",sp.k2 as "k2",sp.k3 as 
"k3",sp.F2to1 as "f2to1",sp.F3to2 as "f3to2",sp.cntnpercolli as 
"cntnpercolli",sp.futype_id as "futype_id",sp.nlaag as "nlaag",sp.ndc as 
"ndc"

which didn't make any difference. Also I haven't got a clue what a "derived 
columns speficier" is nor how I can use it <g>. The strange thing is that I 
just used my original query (which ran fine) and just prepended it with the 
"create view ... as".

Any clues are most welcome,

Thanks in advance,
Ton

"Ton van den Broek" <REMOVEtonTHE@CAPITALStvdb.nl> schreef in bericht 
news:4d238d83$1@forums-1-dub...
> Hi all,
>
> I'm trying to create a view as follows, but get this errormessage below. 
> The query itself runs without problems when pasted into ISQL. Does anybody 
> know what is wrong here?
>
> Thanks in advance,
> Ton
>
> Could not execute statement.
> Derived table 'sp' has no name for column 1
> SQLCODE=-163, ODBC 3 State="42000"
> Line 1, column 1
>
> create view V_artikel_fuqty as
> select
> artikel.artsiteid,artikel.artnum,
> sp.npercolli,sp.siteid1,sp.id1,sp.n1,sp.siteid2,sp.id2,sp.n2,sp.siteid3,sp.id3,sp.n3,sp.k1,sp.k2,sp.k3,sp.F2to1,sp.F3to2,sp.cntnpercolli,sp.futype_id,sp.nlaag,sp.ndc
> from
> artikel,LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent > 0 then 'M' else 
> 'A' endif,artikel.artsiteid,artikel.artnum,null,
> if artikel.isparent=10 then artikel.am_fk_fustsiteid else null endif,
> if artikel.isparent=10 then artikel.am_fk_fustid else null endif,
> null,
> null,
> null,
> null,
> if artikel.isparent=0 then 10 else if artikel.isparent = 10 then 20 else 
> if artikel.isparent = 20 then null else null endif endif endif,
> 1.0,
> if artikel.isparent=0 then 1 else artikel.fk_fuprmodel_id endif,
> 'BRO2',
> null,
> null,
> if artikel.isparent=0 then null else 1.0 endif,
> if artikel.isparent=0 then null else artikel.isparent endif,
> 1)) AS sp
>
> 


0
Ton
1/5/2011 11:07:58 AM
Ton,

the SELECT list of the view definition seems fine as there are no 
non-unique columns names (which must be resolved by an alias for views) 
and there are no unnamed expressions. As such, the explicit aliases you 
have used won't help as they are not necessary.

I guess the problem lies within the usage of the *procedure* in the FROM 
clause.
Does this procedure have a result clause, i.e. something like

    CREATE PROCEDURE ...
    RESULT (col1 int, col2 varchar(100), ...)
?

If not, it could be that just running the query works fine, as the query 
engine will take the result column names by executing (particularly by 
"describing") the procedure (and take them from the according SELECT in 
the procedure's body).
In contrast, it could be that for a view, such a dynamical "describe" 
does not take place, and therefore the column names can't be known 
beforehand.

If my assumption is correct, then instead of using a RESULT clause in 
the proc, you might as well use the WITH clause for the procedure in the 
FROM clause and specify the column names thereby, i.e. something like

....LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent>  0 then 'M' else...
    if artikel.isparent=0 then null else artikel.isparent endif,
    1)) WITH (col1 int, col2 varchar(100), ...) as sp


HTH
Volker



Ton van den Broek wrote:
> Still trying to get it to work:
>
> The docs say I need to "Use a select list alias in the query specification
> or use a derived columns specifier" so I've tried the following
> select list:
>
> artikel.artsiteid as "artsiteid",artikel.artnum as "artnum",
> sp.npercolli as "npercolli",sp.siteid1 as "siteid1",sp.id1 as "id1" ,sp.n1
> as "n1",sp.siteid2 as "siteid2",sp.id2 as "id2",sp.n2 as "n2",sp.siteid3 as
> "siteid3",sp.id3 as "id3",sp.n3 as "n3",sp.k1 as "k1",sp.k2 as "k2",sp.k3 as
> "k3",sp.F2to1 as "f2to1",sp.F3to2 as "f3to2",sp.cntnpercolli as
> "cntnpercolli",sp.futype_id as "futype_id",sp.nlaag as "nlaag",sp.ndc as
> "ndc"
>
> which didn't make any difference. Also I haven't got a clue what a "derived
> columns speficier" is nor how I can use it<g>. The strange thing is that I
> just used my original query (which ran fine) and just prepended it with the
> "create view ... as".
>
> Any clues are most welcome,
>
> Thanks in advance,
> Ton
>
> "Ton van den Broek"<REMOVEtonTHE@CAPITALStvdb.nl>  schreef in bericht
> news:4d238d83$1@forums-1-dub...
>> Hi all,
>>
>> I'm trying to create a view as follows, but get this errormessage below.
>> The query itself runs without problems when pasted into ISQL. Does anybody
>> know what is wrong here?
>>
>> Thanks in advance,
>> Ton
>>
>> Could not execute statement.
>> Derived table 'sp' has no name for column 1
>> SQLCODE=-163, ODBC 3 State="42000"
>> Line 1, column 1
>>
>> create view V_artikel_fuqty as
>> select
>> artikel.artsiteid,artikel.artnum,
>> sp.npercolli,sp.siteid1,sp.id1,sp.n1,sp.siteid2,sp.id2,sp.n2,sp.siteid3,sp.id3,sp.n3,sp.k1,sp.k2,sp.k3,sp.F2to1,sp.F3to2,sp.cntnpercolli,sp.futype_id,sp.nlaag,sp.ndc
>> from
>> artikel,LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent>  0 then 'M' else
>> 'A' endif,artikel.artsiteid,artikel.artnum,null,
>> if artikel.isparent=10 then artikel.am_fk_fustsiteid else null endif,
>> if artikel.isparent=10 then artikel.am_fk_fustid else null endif,
>> null,
>> null,
>> null,
>> null,
>> if artikel.isparent=0 then 10 else if artikel.isparent = 10 then 20 else
>> if artikel.isparent = 20 then null else null endif endif endif,
>> 1.0,
>> if artikel.isparent=0 then 1 else artikel.fk_fuprmodel_id endif,
>> 'BRO2',
>> null,
>> null,
>> if artikel.isparent=0 then null else 1.0 endif,
>> if artikel.isparent=0 then null else artikel.isparent endif,
>> 1)) AS sp
>>
>>
>
>
0
Volker
1/5/2011 12:48:12 PM
Hi Volker,

thanks for looking at it! The procedure indeed has a RESULT clause (and it's 
used to describe the result set at least when I use the query itself, for 
example "select * from table, lateral(stored proc())" without the WITH 
clause runs fine too. I tried using the WITH clause as you described (and 
even tried it with removing the result clause from the stored proc), but all 
at no avail. All queries run fine on their own, but I cannot prepend the 
create view  statement to it without getting the error message :-(

So still trying and hoping for the right clue!

Best regards,
Ton

"Volker Barth" <No_VBarth@Spam_GLOBAL-FINANZ.de> schreef in bericht 
news:4d24688c$1@forums-1-dub...
> Ton,
>
> the SELECT list of the view definition seems fine as there are no 
> non-unique columns names (which must be resolved by an alias for views) 
> and there are no unnamed expressions. As such, the explicit aliases you 
> have used won't help as they are not necessary.
>
> I guess the problem lies within the usage of the *procedure* in the FROM 
> clause.
> Does this procedure have a result clause, i.e. something like
>
>    CREATE PROCEDURE ...
>    RESULT (col1 int, col2 varchar(100), ...)
> ?
>
> If not, it could be that just running the query works fine, as the query 
> engine will take the result column names by executing (particularly by 
> "describing") the procedure (and take them from the according SELECT in 
> the procedure's body).
> In contrast, it could be that for a view, such a dynamical "describe" does 
> not take place, and therefore the column names can't be known beforehand.
>
> If my assumption is correct, then instead of using a RESULT clause in the 
> proc, you might as well use the WITH clause for the procedure in the FROM 
> clause and specify the column names thereby, i.e. something like
>
> ...LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent>  0 then 'M' else...
>    if artikel.isparent=0 then null else artikel.isparent endif,
>    1)) WITH (col1 int, col2 varchar(100), ...) as sp
>
>
> HTH
> Volker
>
>
>
> Ton van den Broek wrote:
>> Still trying to get it to work:
>>
>> The docs say I need to "Use a select list alias in the query 
>> specification
>> or use a derived columns specifier" so I've tried the following
>> select list:
>>
>> artikel.artsiteid as "artsiteid",artikel.artnum as "artnum",
>> sp.npercolli as "npercolli",sp.siteid1 as "siteid1",sp.id1 as "id1" 
>> ,sp.n1
>> as "n1",sp.siteid2 as "siteid2",sp.id2 as "id2",sp.n2 as "n2",sp.siteid3 
>> as
>> "siteid3",sp.id3 as "id3",sp.n3 as "n3",sp.k1 as "k1",sp.k2 as "k2",sp.k3 
>> as
>> "k3",sp.F2to1 as "f2to1",sp.F3to2 as "f3to2",sp.cntnpercolli as
>> "cntnpercolli",sp.futype_id as "futype_id",sp.nlaag as "nlaag",sp.ndc as
>> "ndc"
>>
>> which didn't make any difference. Also I haven't got a clue what a 
>> "derived
>> columns speficier" is nor how I can use it<g>. The strange thing is that 
>> I
>> just used my original query (which ran fine) and just prepended it with 
>> the
>> "create view ... as".
>>
>> Any clues are most welcome,
>>
>> Thanks in advance,
>> Ton
>>
>> "Ton van den Broek"<REMOVEtonTHE@CAPITALStvdb.nl>  schreef in bericht
>> news:4d238d83$1@forums-1-dub...
>>> Hi all,
>>>
>>> I'm trying to create a view as follows, but get this errormessage below.
>>> The query itself runs without problems when pasted into ISQL. Does 
>>> anybody
>>> know what is wrong here?
>>>
>>> Thanks in advance,
>>> Ton
>>>
>>> Could not execute statement.
>>> Derived table 'sp' has no name for column 1
>>> SQLCODE=-163, ODBC 3 State="42000"
>>> Line 1, column 1
>>>
>>> create view V_artikel_fuqty as
>>> select
>>> artikel.artsiteid,artikel.artnum,
>>> sp.npercolli,sp.siteid1,sp.id1,sp.n1,sp.siteid2,sp.id2,sp.n2,sp.siteid3,sp.id3,sp.n3,sp.k1,sp.k2,sp.k3,sp.F2to1,sp.F3to2,sp.cntnpercolli,sp.futype_id,sp.nlaag,sp.ndc
>>> from
>>> artikel,LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent>  0 then 'M' 
>>> else
>>> 'A' endif,artikel.artsiteid,artikel.artnum,null,
>>> if artikel.isparent=10 then artikel.am_fk_fustsiteid else null endif,
>>> if artikel.isparent=10 then artikel.am_fk_fustid else null endif,
>>> null,
>>> null,
>>> null,
>>> null,
>>> if artikel.isparent=0 then 10 else if artikel.isparent = 10 then 20 else
>>> if artikel.isparent = 20 then null else null endif endif endif,
>>> 1.0,
>>> if artikel.isparent=0 then 1 else artikel.fk_fuprmodel_id endif,
>>> 'BRO2',
>>> null,
>>> null,
>>> if artikel.isparent=0 then null else 1.0 endif,
>>> if artikel.isparent=0 then null else artikel.isparent endif,
>>> 1)) AS sp
>>>
>>>
>>
>>
> 


0
Ton
1/5/2011 1:54:49 PM
Hi Volker,

thanks for looking at it! The procedure indeed has a RESULT clause (and it's
used to describe the result set at least when I use the query itself, for
example "select * from table, lateral(stored proc())" without the WITH
clause runs fine too. I tried using the WITH clause as you described (and
even tried it with removing the result clause from the stored proc), but all
at no avail. All queries run fine on their own, but I cannot prepend the
create view  statement to it without getting the error message :-(

So still trying and hoping for the right clue!

Best regards,
Ton

"Volker Barth" <No_VBarth@Spam_GLOBAL-FINANZ.de> schreef in bericht
news:4d24688c$1@forums-1-dub...
> Ton,
>
> the SELECT list of the view definition seems fine as there are no
> non-unique columns names (which must be resolved by an alias for views)
> and there are no unnamed expressions. As such, the explicit aliases you
> have used won't help as they are not necessary.
>
> I guess the problem lies within the usage of the *procedure* in the FROM
> clause.
> Does this procedure have a result clause, i.e. something like
>
>    CREATE PROCEDURE ...
>    RESULT (col1 int, col2 varchar(100), ...)
> ?
>
> If not, it could be that just running the query works fine, as the query
> engine will take the result column names by executing (particularly by
> "describing") the procedure (and take them from the according SELECT in
> the procedure's body).
> In contrast, it could be that for a view, such a dynamical "describe" does
> not take place, and therefore the column names can't be known beforehand.
>
> If my assumption is correct, then instead of using a RESULT clause in the
> proc, you might as well use the WITH clause for the procedure in the FROM
> clause and specify the column names thereby, i.e. something like
>
> ...LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent>  0 then 'M' else...
>    if artikel.isparent=0 then null else artikel.isparent endif,
>    1)) WITH (col1 int, col2 varchar(100), ...) as sp
>
>
> HTH
> Volker
>
>
>
> Ton van den Broek wrote:
>> Still trying to get it to work:
>>
>> The docs say I need to "Use a select list alias in the query
>> specification
>> or use a derived columns specifier" so I've tried the following
>> select list:
>>
>> artikel.artsiteid as "artsiteid",artikel.artnum as "artnum",
>> sp.npercolli as "npercolli",sp.siteid1 as "siteid1",sp.id1 as "id1"
>> ,sp.n1
>> as "n1",sp.siteid2 as "siteid2",sp.id2 as "id2",sp.n2 as "n2",sp.siteid3
>> as
>> "siteid3",sp.id3 as "id3",sp.n3 as "n3",sp.k1 as "k1",sp.k2 as "k2",sp.k3
>> as
>> "k3",sp.F2to1 as "f2to1",sp.F3to2 as "f3to2",sp.cntnpercolli as
>> "cntnpercolli",sp.futype_id as "futype_id",sp.nlaag as "nlaag",sp.ndc as
>> "ndc"
>>
>> which didn't make any difference. Also I haven't got a clue what a
>> "derived
>> columns speficier" is nor how I can use it<g>. The strange thing is that
>> I
>> just used my original query (which ran fine) and just prepended it with
>> the
>> "create view ... as".
>>
>> Any clues are most welcome,
>>
>> Thanks in advance,
>> Ton
>>
>> "Ton van den Broek"<REMOVEtonTHE@CAPITALStvdb.nl>  schreef in bericht
>> news:4d238d83$1@forums-1-dub...
>>> Hi all,
>>>
>>> I'm trying to create a view as follows, but get this errormessage below.
>>> The query itself runs without problems when pasted into ISQL. Does
>>> anybody
>>> know what is wrong here?
>>>
>>> Thanks in advance,
>>> Ton
>>>
>>> Could not execute statement.
>>> Derived table 'sp' has no name for column 1
>>> SQLCODE=-163, ODBC 3 State="42000"
>>> Line 1, column 1
>>>
>>> create view V_artikel_fuqty as
>>> select
>>> artikel.artsiteid,artikel.artnum,
>>> sp.npercolli,sp.siteid1,sp.id1,sp.n1,sp.siteid2,sp.id2,sp.n2,sp.siteid3,sp.id3,sp.n3,sp.k1,sp.k2,sp.k3,sp.F2to1,sp.F3to2,sp.cntnpercolli,sp.futype_id,sp.nlaag,sp.ndc
>>> from
>>> artikel,LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent>  0 then 'M'
>>> else
>>> 'A' endif,artikel.artsiteid,artikel.artnum,null,
>>> if artikel.isparent=10 then artikel.am_fk_fustsiteid else null endif,
>>> if artikel.isparent=10 then artikel.am_fk_fustid else null endif,
>>> null,
>>> null,
>>> null,
>>> null,
>>> if artikel.isparent=0 then 10 else if artikel.isparent = 10 then 20 else
>>> if artikel.isparent = 20 then null else null endif endif endif,
>>> 1.0,
>>> if artikel.isparent=0 then 1 else artikel.fk_fuprmodel_id endif,
>>> 'BRO2',
>>> null,
>>> null,
>>> if artikel.isparent=0 then null else 1.0 endif,
>>> if artikel.isparent=0 then null else artikel.isparent endif,
>>> 1)) AS sp
>>>
>>>
>>
>>
>



0
Ton
1/5/2011 1:54:50 PM
Ton,

I suspect (but I'm just guessing!) the problem has to do with the 
LATERAL call.

As to the V12 docs, the syntax is

    LATERAL ( select-statement | table-expression )
    [ AS ] correlation-name [ ( column-name, ... ) ]

Therefore, it might be necessary to put the column list after the "AS 
sp" alias (and not in the WITH clause).

FWIW, you might try the view without the LATERAL call, i.e. just call 
the procedure with constant arguments (or ones not bound to table 
"artikel"). That test might point out whether the problem has to do with 
the LATERAL call or the procedure itself.

Just my 0.02 cents:)

Volker



Ton van den Broek wrote:
> Hi Volker,
>
> thanks for looking at it! The procedure indeed has a RESULT clause (and it's
> used to describe the result set at least when I use the query itself, for
> example "select * from table, lateral(stored proc())" without the WITH
> clause runs fine too. I tried using the WITH clause as you described (and
> even tried it with removing the result clause from the stored proc), but all
> at no avail. All queries run fine on their own, but I cannot prepend the
> create view  statement to it without getting the error message :-(
>
> So still trying and hoping for the right clue!
>
> Best regards,
> Ton
>
> "Volker Barth"<No_VBarth@Spam_GLOBAL-FINANZ.de>  schreef in bericht
> news:4d24688c$1@forums-1-dub...
>> Ton,
>>
>> the SELECT list of the view definition seems fine as there are no
>> non-unique columns names (which must be resolved by an alias for views)
>> and there are no unnamed expressions. As such, the explicit aliases you
>> have used won't help as they are not necessary.
>>
>> I guess the problem lies within the usage of the *procedure* in the FROM
>> clause.
>> Does this procedure have a result clause, i.e. something like
>>
>>     CREATE PROCEDURE ...
>>     RESULT (col1 int, col2 varchar(100), ...)
>> ?
>>
>> If not, it could be that just running the query works fine, as the query
>> engine will take the result column names by executing (particularly by
>> "describing") the procedure (and take them from the according SELECT in
>> the procedure's body).
>> In contrast, it could be that for a view, such a dynamical "describe" does
>> not take place, and therefore the column names can't be known beforehand.
>>
>> If my assumption is correct, then instead of using a RESULT clause in the
>> proc, you might as well use the WITH clause for the procedure in the FROM
>> clause and specify the column names thereby, i.e. something like
>>
>> ...LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent>   0 then 'M' else...
>>     if artikel.isparent=0 then null else artikel.isparent endif,
>>     1)) WITH (col1 int, col2 varchar(100), ...) as sp
>>
>>
>> HTH
>> Volker
>>
>>
>>
>> Ton van den Broek wrote:
>>> Still trying to get it to work:
>>>
>>> The docs say I need to "Use a select list alias in the query
>>> specification
>>> or use a derived columns specifier" so I've tried the following
>>> select list:
>>>
>>> artikel.artsiteid as "artsiteid",artikel.artnum as "artnum",
>>> sp.npercolli as "npercolli",sp.siteid1 as "siteid1",sp.id1 as "id1"
>>> ,sp.n1
>>> as "n1",sp.siteid2 as "siteid2",sp.id2 as "id2",sp.n2 as "n2",sp.siteid3
>>> as
>>> "siteid3",sp.id3 as "id3",sp.n3 as "n3",sp.k1 as "k1",sp.k2 as "k2",sp.k3
>>> as
>>> "k3",sp.F2to1 as "f2to1",sp.F3to2 as "f3to2",sp.cntnpercolli as
>>> "cntnpercolli",sp.futype_id as "futype_id",sp.nlaag as "nlaag",sp.ndc as
>>> "ndc"
>>>
>>> which didn't make any difference. Also I haven't got a clue what a
>>> "derived
>>> columns speficier" is nor how I can use it<g>. The strange thing is that
>>> I
>>> just used my original query (which ran fine) and just prepended it with
>>> the
>>> "create view ... as".
>>>
>>> Any clues are most welcome,
>>>
>>> Thanks in advance,
>>> Ton
>>>
>>> "Ton van den Broek"<REMOVEtonTHE@CAPITALStvdb.nl>   schreef in bericht
>>> news:4d238d83$1@forums-1-dub...
>>>> Hi all,
>>>>
>>>> I'm trying to create a view as follows, but get this errormessage below.
>>>> The query itself runs without problems when pasted into ISQL. Does
>>>> anybody
>>>> know what is wrong here?
>>>>
>>>> Thanks in advance,
>>>> Ton
>>>>
>>>> Could not execute statement.
>>>> Derived table 'sp' has no name for column 1
>>>> SQLCODE=-163, ODBC 3 State="42000"
>>>> Line 1, column 1
>>>>
>>>> create view V_artikel_fuqty as
>>>> select
>>>> artikel.artsiteid,artikel.artnum,
>>>> sp.npercolli,sp.siteid1,sp.id1,sp.n1,sp.siteid2,sp.id2,sp.n2,sp.siteid3,sp.id3,sp.n3,sp.k1,sp.k2,sp.k3,sp.F2to1,sp.F3to2,sp.cntnpercolli,sp.futype_id,sp.nlaag,sp.ndc
>>>> from
>>>> artikel,LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent>   0 then 'M'
>>>> else
>>>> 'A' endif,artikel.artsiteid,artikel.artnum,null,
>>>> if artikel.isparent=10 then artikel.am_fk_fustsiteid else null endif,
>>>> if artikel.isparent=10 then artikel.am_fk_fustid else null endif,
>>>> null,
>>>> null,
>>>> null,
>>>> null,
>>>> if artikel.isparent=0 then 10 else if artikel.isparent = 10 then 20 else
>>>> if artikel.isparent = 20 then null else null endif endif endif,
>>>> 1.0,
>>>> if artikel.isparent=0 then 1 else artikel.fk_fuprmodel_id endif,
>>>> 'BRO2',
>>>> null,
>>>> null,
>>>> if artikel.isparent=0 then null else 1.0 endif,
>>>> if artikel.isparent=0 then null else artikel.isparent endif,
>>>> 1)) AS sp
>>>>
>>>>
>>>
>>>
>>
>
>
0
Volker
1/5/2011 2:47:02 PM
Hi Ton,

The problem is in the LATERAL part.
A while ago we had the same problem. After contact with Sybase we found the 
following solution:

LATERAL((proc2(col1)) is interpreted internally as the derived table 
"LATERAL(select * from proc2(col1))"

And any nested derived table (including derived tables with outreferences 
defined using LATERAL) used in the view definitions cannot have " select * 
". They must have a fully specified select list. This is the general rule.

So you need to change "LATERAL(nG_S_FuQtyCalcEquiv()) AS sp" to 
"LATERAL(SELECT <fields> FROM nG_S_FuQtyCalcEquiv()) AS sp"

Regards,
Christian


"Ton van den Broek" <REMOVEtonTHE@CAPITALStvdb.nl> schreef in bericht 
news:4d24782a$1@forums-1-dub...
> Hi Volker,
>
> thanks for looking at it! The procedure indeed has a RESULT clause (and 
> it's
> used to describe the result set at least when I use the query itself, for
> example "select * from table, lateral(stored proc())" without the WITH
> clause runs fine too. I tried using the WITH clause as you described (and
> even tried it with removing the result clause from the stored proc), but 
> all
> at no avail. All queries run fine on their own, but I cannot prepend the
> create view  statement to it without getting the error message :-(
>
> So still trying and hoping for the right clue!
>
> Best regards,
> Ton
>
> "Volker Barth" <No_VBarth@Spam_GLOBAL-FINANZ.de> schreef in bericht
> news:4d24688c$1@forums-1-dub...
>> Ton,
>>
>> the SELECT list of the view definition seems fine as there are no
>> non-unique columns names (which must be resolved by an alias for views)
>> and there are no unnamed expressions. As such, the explicit aliases you
>> have used won't help as they are not necessary.
>>
>> I guess the problem lies within the usage of the *procedure* in the FROM
>> clause.
>> Does this procedure have a result clause, i.e. something like
>>
>>    CREATE PROCEDURE ...
>>    RESULT (col1 int, col2 varchar(100), ...)
>> ?
>>
>> If not, it could be that just running the query works fine, as the query
>> engine will take the result column names by executing (particularly by
>> "describing") the procedure (and take them from the according SELECT in
>> the procedure's body).
>> In contrast, it could be that for a view, such a dynamical "describe" 
>> does
>> not take place, and therefore the column names can't be known beforehand.
>>
>> If my assumption is correct, then instead of using a RESULT clause in the
>> proc, you might as well use the WITH clause for the procedure in the FROM
>> clause and specify the column names thereby, i.e. something like
>>
>> ...LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent>  0 then 'M' else...
>>    if artikel.isparent=0 then null else artikel.isparent endif,
>>    1)) WITH (col1 int, col2 varchar(100), ...) as sp
>>
>>
>> HTH
>> Volker
>>
>>
>>
>> Ton van den Broek wrote:
>>> Still trying to get it to work:
>>>
>>> The docs say I need to "Use a select list alias in the query
>>> specification
>>> or use a derived columns specifier" so I've tried the following
>>> select list:
>>>
>>> artikel.artsiteid as "artsiteid",artikel.artnum as "artnum",
>>> sp.npercolli as "npercolli",sp.siteid1 as "siteid1",sp.id1 as "id1"
>>> ,sp.n1
>>> as "n1",sp.siteid2 as "siteid2",sp.id2 as "id2",sp.n2 as "n2",sp.siteid3
>>> as
>>> "siteid3",sp.id3 as "id3",sp.n3 as "n3",sp.k1 as "k1",sp.k2 as 
>>> "k2",sp.k3
>>> as
>>> "k3",sp.F2to1 as "f2to1",sp.F3to2 as "f3to2",sp.cntnpercolli as
>>> "cntnpercolli",sp.futype_id as "futype_id",sp.nlaag as "nlaag",sp.ndc as
>>> "ndc"
>>>
>>> which didn't make any difference. Also I haven't got a clue what a
>>> "derived
>>> columns speficier" is nor how I can use it<g>. The strange thing is that
>>> I
>>> just used my original query (which ran fine) and just prepended it with
>>> the
>>> "create view ... as".
>>>
>>> Any clues are most welcome,
>>>
>>> Thanks in advance,
>>> Ton
>>>
>>> "Ton van den Broek"<REMOVEtonTHE@CAPITALStvdb.nl>  schreef in bericht
>>> news:4d238d83$1@forums-1-dub...
>>>> Hi all,
>>>>
>>>> I'm trying to create a view as follows, but get this errormessage 
>>>> below.
>>>> The query itself runs without problems when pasted into ISQL. Does
>>>> anybody
>>>> know what is wrong here?
>>>>
>>>> Thanks in advance,
>>>> Ton
>>>>
>>>> Could not execute statement.
>>>> Derived table 'sp' has no name for column 1
>>>> SQLCODE=-163, ODBC 3 State="42000"
>>>> Line 1, column 1
>>>>
>>>> create view V_artikel_fuqty as
>>>> select
>>>> artikel.artsiteid,artikel.artnum,
>>>> sp.npercolli,sp.siteid1,sp.id1,sp.n1,sp.siteid2,sp.id2,sp.n2,sp.siteid3,sp.id3,sp.n3,sp.k1,sp.k2,sp.k3,sp.F2to1,sp.F3to2,sp.cntnpercolli,sp.futype_id,sp.nlaag,sp.ndc
>>>> from
>>>> artikel,LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent>  0 then 'M'
>>>> else
>>>> 'A' endif,artikel.artsiteid,artikel.artnum,null,
>>>> if artikel.isparent=10 then artikel.am_fk_fustsiteid else null endif,
>>>> if artikel.isparent=10 then artikel.am_fk_fustid else null endif,
>>>> null,
>>>> null,
>>>> null,
>>>> null,
>>>> if artikel.isparent=0 then 10 else if artikel.isparent = 10 then 20 
>>>> else
>>>> if artikel.isparent = 20 then null else null endif endif endif,
>>>> 1.0,
>>>> if artikel.isparent=0 then 1 else artikel.fk_fuprmodel_id endif,
>>>> 'BRO2',
>>>> null,
>>>> null,
>>>> if artikel.isparent=0 then null else 1.0 endif,
>>>> if artikel.isparent=0 then null else artikel.isparent endif,
>>>> 1)) AS sp
>>>>
>>>>
>>>
>>>
>>
>
>
> 

0
Christian
1/5/2011 3:37:59 PM
Hi Christian,

Spot on! The thing works flawlessly now. Thanks a million!.
Just curious : is this undocumented or did I completely miss it?

Best regards,
Ton

"Christian Hamers" <chamers@[ n o_s p a m ]bcsbv.nl> schreef in bericht 
news:4d249057$1@forums-1-dub...
> Hi Ton,
>
> The problem is in the LATERAL part.
> A while ago we had the same problem. After contact with Sybase we found 
> the following solution:
>
> LATERAL((proc2(col1)) is interpreted internally as the derived table 
> "LATERAL(select * from proc2(col1))"
>
> And any nested derived table (including derived tables with outreferences 
> defined using LATERAL) used in the view definitions cannot have " select * 
> ". They must have a fully specified select list. This is the general rule.
>
> So you need to change "LATERAL(nG_S_FuQtyCalcEquiv()) AS sp" to 
> "LATERAL(SELECT <fields> FROM nG_S_FuQtyCalcEquiv()) AS sp"
>
> Regards,
> Christian
>
>
> "Ton van den Broek" <REMOVEtonTHE@CAPITALStvdb.nl> schreef in bericht 
> news:4d24782a$1@forums-1-dub...
>> Hi Volker,
>>
>> thanks for looking at it! The procedure indeed has a RESULT clause (and 
>> it's
>> used to describe the result set at least when I use the query itself, for
>> example "select * from table, lateral(stored proc())" without the WITH
>> clause runs fine too. I tried using the WITH clause as you described (and
>> even tried it with removing the result clause from the stored proc), but 
>> all
>> at no avail. All queries run fine on their own, but I cannot prepend the
>> create view  statement to it without getting the error message :-(
>>
>> So still trying and hoping for the right clue!
>>
>> Best regards,
>> Ton
>>
>> "Volker Barth" <No_VBarth@Spam_GLOBAL-FINANZ.de> schreef in bericht
>> news:4d24688c$1@forums-1-dub...
>>> Ton,
>>>
>>> the SELECT list of the view definition seems fine as there are no
>>> non-unique columns names (which must be resolved by an alias for views)
>>> and there are no unnamed expressions. As such, the explicit aliases you
>>> have used won't help as they are not necessary.
>>>
>>> I guess the problem lies within the usage of the *procedure* in the FROM
>>> clause.
>>> Does this procedure have a result clause, i.e. something like
>>>
>>>    CREATE PROCEDURE ...
>>>    RESULT (col1 int, col2 varchar(100), ...)
>>> ?
>>>
>>> If not, it could be that just running the query works fine, as the query
>>> engine will take the result column names by executing (particularly by
>>> "describing") the procedure (and take them from the according SELECT in
>>> the procedure's body).
>>> In contrast, it could be that for a view, such a dynamical "describe" 
>>> does
>>> not take place, and therefore the column names can't be known 
>>> beforehand.
>>>
>>> If my assumption is correct, then instead of using a RESULT clause in 
>>> the
>>> proc, you might as well use the WITH clause for the procedure in the 
>>> FROM
>>> clause and specify the column names thereby, i.e. something like
>>>
>>> ...LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent>  0 then 'M' else...
>>>    if artikel.isparent=0 then null else artikel.isparent endif,
>>>    1)) WITH (col1 int, col2 varchar(100), ...) as sp
>>>
>>>
>>> HTH
>>> Volker
>>>
>>>
>>>
>>> Ton van den Broek wrote:
>>>> Still trying to get it to work:
>>>>
>>>> The docs say I need to "Use a select list alias in the query
>>>> specification
>>>> or use a derived columns specifier" so I've tried the following
>>>> select list:
>>>>
>>>> artikel.artsiteid as "artsiteid",artikel.artnum as "artnum",
>>>> sp.npercolli as "npercolli",sp.siteid1 as "siteid1",sp.id1 as "id1"
>>>> ,sp.n1
>>>> as "n1",sp.siteid2 as "siteid2",sp.id2 as "id2",sp.n2 as 
>>>> "n2",sp.siteid3
>>>> as
>>>> "siteid3",sp.id3 as "id3",sp.n3 as "n3",sp.k1 as "k1",sp.k2 as 
>>>> "k2",sp.k3
>>>> as
>>>> "k3",sp.F2to1 as "f2to1",sp.F3to2 as "f3to2",sp.cntnpercolli as
>>>> "cntnpercolli",sp.futype_id as "futype_id",sp.nlaag as "nlaag",sp.ndc 
>>>> as
>>>> "ndc"
>>>>
>>>> which didn't make any difference. Also I haven't got a clue what a
>>>> "derived
>>>> columns speficier" is nor how I can use it<g>. The strange thing is 
>>>> that
>>>> I
>>>> just used my original query (which ran fine) and just prepended it with
>>>> the
>>>> "create view ... as".
>>>>
>>>> Any clues are most welcome,
>>>>
>>>> Thanks in advance,
>>>> Ton
>>>>
>>>> "Ton van den Broek"<REMOVEtonTHE@CAPITALStvdb.nl>  schreef in bericht
>>>> news:4d238d83$1@forums-1-dub...
>>>>> Hi all,
>>>>>
>>>>> I'm trying to create a view as follows, but get this errormessage 
>>>>> below.
>>>>> The query itself runs without problems when pasted into ISQL. Does
>>>>> anybody
>>>>> know what is wrong here?
>>>>>
>>>>> Thanks in advance,
>>>>> Ton
>>>>>
>>>>> Could not execute statement.
>>>>> Derived table 'sp' has no name for column 1
>>>>> SQLCODE=-163, ODBC 3 State="42000"
>>>>> Line 1, column 1
>>>>>
>>>>> create view V_artikel_fuqty as
>>>>> select
>>>>> artikel.artsiteid,artikel.artnum,
>>>>> sp.npercolli,sp.siteid1,sp.id1,sp.n1,sp.siteid2,sp.id2,sp.n2,sp.siteid3,sp.id3,sp.n3,sp.k1,sp.k2,sp.k3,sp.F2to1,sp.F3to2,sp.cntnpercolli,sp.futype_id,sp.nlaag,sp.ndc
>>>>> from
>>>>> artikel,LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent>  0 then 'M'
>>>>> else
>>>>> 'A' endif,artikel.artsiteid,artikel.artnum,null,
>>>>> if artikel.isparent=10 then artikel.am_fk_fustsiteid else null endif,
>>>>> if artikel.isparent=10 then artikel.am_fk_fustid else null endif,
>>>>> null,
>>>>> null,
>>>>> null,
>>>>> null,
>>>>> if artikel.isparent=0 then 10 else if artikel.isparent = 10 then 20 
>>>>> else
>>>>> if artikel.isparent = 20 then null else null endif endif endif,
>>>>> 1.0,
>>>>> if artikel.isparent=0 then 1 else artikel.fk_fuprmodel_id endif,
>>>>> 'BRO2',
>>>>> null,
>>>>> null,
>>>>> if artikel.isparent=0 then null else 1.0 endif,
>>>>> if artikel.isparent=0 then null else artikel.isparent endif,
>>>>> 1)) AS sp
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
>>
> 


0
Ton
1/5/2011 8:15:09 PM
Hi Ton,

It was undocumented, but after our report of this problem they are gonna 
change it. This is the reply we got:

***************
to avoid problems similar to what you get, the documentation of SQL Anywhere 
will change in the following way:

SQL Anywhere Server - SQL Reference � SQL statements � SQL statements � 
CREATE VIEW statement

.. Add a the following note to the "Remarks" section:

CREATE VIEW statement allows the construct "select * ..." only in the main 
query block. Any derived tables and subqueries must fully specified the 
expressions in the SELECT list as you cannot use the SELECT * constructs. 
For example, "CREATE VIEW V as select * from T, (select * from R) as DT" is 
not allowed as the derived table DT is specified using "select * " 
construct. Another example of incorrectly defined view "CREATE VIEW V as 
select * from T, LATERAL(proc(T.A)) as DT": the derived table 
"LATERAL(proc(T.A))" is a short hand for "LATERAL( select * from proc(T.A))" 
hence it is not allowed in the view definition.
***************

Regards,
Christian


"Ton van den Broek" <REMOVEtonTHE@CAPITALStvdb.nl> schreef in bericht 
news:4d24d14d$1@forums-1-dub...
> Hi Christian,
>
> Spot on! The thing works flawlessly now. Thanks a million!.
> Just curious : is this undocumented or did I completely miss it?
>
> Best regards,
> Ton
>
> "Christian Hamers" <chamers@[ n o_s p a m ]bcsbv.nl> schreef in bericht 
> news:4d249057$1@forums-1-dub...
>> Hi Ton,
>>
>> The problem is in the LATERAL part.
>> A while ago we had the same problem. After contact with Sybase we found 
>> the following solution:
>>
>> LATERAL((proc2(col1)) is interpreted internally as the derived table 
>> "LATERAL(select * from proc2(col1))"
>>
>> And any nested derived table (including derived tables with outreferences 
>> defined using LATERAL) used in the view definitions cannot have " select 
>> * ". They must have a fully specified select list. This is the general 
>> rule.
>>
>> So you need to change "LATERAL(nG_S_FuQtyCalcEquiv()) AS sp" to 
>> "LATERAL(SELECT <fields> FROM nG_S_FuQtyCalcEquiv()) AS sp"
>>
>> Regards,
>> Christian
>>
>>
>> "Ton van den Broek" <REMOVEtonTHE@CAPITALStvdb.nl> schreef in bericht 
>> news:4d24782a$1@forums-1-dub...
>>> Hi Volker,
>>>
>>> thanks for looking at it! The procedure indeed has a RESULT clause (and 
>>> it's
>>> used to describe the result set at least when I use the query itself, 
>>> for
>>> example "select * from table, lateral(stored proc())" without the WITH
>>> clause runs fine too. I tried using the WITH clause as you described 
>>> (and
>>> even tried it with removing the result clause from the stored proc), but 
>>> all
>>> at no avail. All queries run fine on their own, but I cannot prepend the
>>> create view  statement to it without getting the error message :-(
>>>
>>> So still trying and hoping for the right clue!
>>>
>>> Best regards,
>>> Ton
>>>
>>> "Volker Barth" <No_VBarth@Spam_GLOBAL-FINANZ.de> schreef in bericht
>>> news:4d24688c$1@forums-1-dub...
>>>> Ton,
>>>>
>>>> the SELECT list of the view definition seems fine as there are no
>>>> non-unique columns names (which must be resolved by an alias for views)
>>>> and there are no unnamed expressions. As such, the explicit aliases you
>>>> have used won't help as they are not necessary.
>>>>
>>>> I guess the problem lies within the usage of the *procedure* in the 
>>>> FROM
>>>> clause.
>>>> Does this procedure have a result clause, i.e. something like
>>>>
>>>>    CREATE PROCEDURE ...
>>>>    RESULT (col1 int, col2 varchar(100), ...)
>>>> ?
>>>>
>>>> If not, it could be that just running the query works fine, as the 
>>>> query
>>>> engine will take the result column names by executing (particularly by
>>>> "describing") the procedure (and take them from the according SELECT in
>>>> the procedure's body).
>>>> In contrast, it could be that for a view, such a dynamical "describe" 
>>>> does
>>>> not take place, and therefore the column names can't be known 
>>>> beforehand.
>>>>
>>>> If my assumption is correct, then instead of using a RESULT clause in 
>>>> the
>>>> proc, you might as well use the WITH clause for the procedure in the 
>>>> FROM
>>>> clause and specify the column names thereby, i.e. something like
>>>>
>>>> ...LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent>  0 then 'M' else...
>>>>    if artikel.isparent=0 then null else artikel.isparent endif,
>>>>    1)) WITH (col1 int, col2 varchar(100), ...) as sp
>>>>
>>>>
>>>> HTH
>>>> Volker
>>>>
>>>>
>>>>
>>>> Ton van den Broek wrote:
>>>>> Still trying to get it to work:
>>>>>
>>>>> The docs say I need to "Use a select list alias in the query
>>>>> specification
>>>>> or use a derived columns specifier" so I've tried the following
>>>>> select list:
>>>>>
>>>>> artikel.artsiteid as "artsiteid",artikel.artnum as "artnum",
>>>>> sp.npercolli as "npercolli",sp.siteid1 as "siteid1",sp.id1 as "id1"
>>>>> ,sp.n1
>>>>> as "n1",sp.siteid2 as "siteid2",sp.id2 as "id2",sp.n2 as 
>>>>> "n2",sp.siteid3
>>>>> as
>>>>> "siteid3",sp.id3 as "id3",sp.n3 as "n3",sp.k1 as "k1",sp.k2 as 
>>>>> "k2",sp.k3
>>>>> as
>>>>> "k3",sp.F2to1 as "f2to1",sp.F3to2 as "f3to2",sp.cntnpercolli as
>>>>> "cntnpercolli",sp.futype_id as "futype_id",sp.nlaag as "nlaag",sp.ndc 
>>>>> as
>>>>> "ndc"
>>>>>
>>>>> which didn't make any difference. Also I haven't got a clue what a
>>>>> "derived
>>>>> columns speficier" is nor how I can use it<g>. The strange thing is 
>>>>> that
>>>>> I
>>>>> just used my original query (which ran fine) and just prepended it 
>>>>> with
>>>>> the
>>>>> "create view ... as".
>>>>>
>>>>> Any clues are most welcome,
>>>>>
>>>>> Thanks in advance,
>>>>> Ton
>>>>>
>>>>> "Ton van den Broek"<REMOVEtonTHE@CAPITALStvdb.nl>  schreef in bericht
>>>>> news:4d238d83$1@forums-1-dub...
>>>>>> Hi all,
>>>>>>
>>>>>> I'm trying to create a view as follows, but get this errormessage 
>>>>>> below.
>>>>>> The query itself runs without problems when pasted into ISQL. Does
>>>>>> anybody
>>>>>> know what is wrong here?
>>>>>>
>>>>>> Thanks in advance,
>>>>>> Ton
>>>>>>
>>>>>> Could not execute statement.
>>>>>> Derived table 'sp' has no name for column 1
>>>>>> SQLCODE=-163, ODBC 3 State="42000"
>>>>>> Line 1, column 1
>>>>>>
>>>>>> create view V_artikel_fuqty as
>>>>>> select
>>>>>> artikel.artsiteid,artikel.artnum,
>>>>>> sp.npercolli,sp.siteid1,sp.id1,sp.n1,sp.siteid2,sp.id2,sp.n2,sp.siteid3,sp.id3,sp.n3,sp.k1,sp.k2,sp.k3,sp.F2to1,sp.F3to2,sp.cntnpercolli,sp.futype_id,sp.nlaag,sp.ndc
>>>>>> from
>>>>>> artikel,LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent>  0 then 'M'
>>>>>> else
>>>>>> 'A' endif,artikel.artsiteid,artikel.artnum,null,
>>>>>> if artikel.isparent=10 then artikel.am_fk_fustsiteid else null endif,
>>>>>> if artikel.isparent=10 then artikel.am_fk_fustid else null endif,
>>>>>> null,
>>>>>> null,
>>>>>> null,
>>>>>> null,
>>>>>> if artikel.isparent=0 then 10 else if artikel.isparent = 10 then 20 
>>>>>> else
>>>>>> if artikel.isparent = 20 then null else null endif endif endif,
>>>>>> 1.0,
>>>>>> if artikel.isparent=0 then 1 else artikel.fk_fuprmodel_id endif,
>>>>>> 'BRO2',
>>>>>> null,
>>>>>> null,
>>>>>> if artikel.isparent=0 then null else 1.0 endif,
>>>>>> if artikel.isparent=0 then null else artikel.isparent endif,
>>>>>> 1)) AS sp
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>>
>>
>
> 

0
Christian
1/6/2011 7:40:27 AM
Ton,

I'm glad you could solve the issue!

Just to add:

Whereas the point is not documented w.r.t. the CREATE VIEW statement(and 
Christian's description seems still missing in the DCX system, c.f. 
http://dcx.sybase.com/index.html#1200en/dbreference/create-view-statement.html), 
the following *is documented*:

"Specifying LATERAL (table-expression) is equivalent to specifying 
LATERAL (SELECT * FROM table-expression)."

Source: 
http://dcx.sybase.com/index.html#1200en/dbreference/from-statement.html, 
"lateral-derived-table".


That being said, it's surely not a precise hint to the particular 
problem you have seen:)

FWIW, I would like to know if the suggestion I've made (using "LATERAL 
.... sp AS (<column-list>)") would resolve that problem, either - just in 
case you may want to engage in further tests...


Volker



Christian Hamers wrote:
> Hi Ton,
>
> It was undocumented, but after our report of this problem they are gonna
> change it. This is the reply we got:
>
> ***************
> to avoid problems similar to what you get, the documentation of SQL
> Anywhere will change in the following way:
>
> SQL Anywhere Server - SQL Reference � SQL statements � SQL statements �
> CREATE VIEW statement
>
> . Add a the following note to the "Remarks" section:
>
> CREATE VIEW statement allows the construct "select * ..." only in the
> main query block. Any derived tables and subqueries must fully specified
> the expressions in the SELECT list as you cannot use the SELECT *
> constructs. For example, "CREATE VIEW V as select * from T, (select *
> from R) as DT" is not allowed as the derived table DT is specified using
> "select * " construct. Another example of incorrectly defined view
> "CREATE VIEW V as select * from T, LATERAL(proc(T.A)) as DT": the
> derived table "LATERAL(proc(T.A))" is a short hand for "LATERAL( select
> * from proc(T.A))" hence it is not allowed in the view definition.
> ***************
>
> Regards,
> Christian
>
>
> "Ton van den Broek" <REMOVEtonTHE@CAPITALStvdb.nl> schreef in bericht
> news:4d24d14d$1@forums-1-dub...
>> Hi Christian,
>>
>> Spot on! The thing works flawlessly now. Thanks a million!.
>> Just curious : is this undocumented or did I completely miss it?
>>
>> Best regards,
>> Ton
>>
>> "Christian Hamers" <chamers@[ n o_s p a m ]bcsbv.nl> schreef in
>> bericht news:4d249057$1@forums-1-dub...
>>> Hi Ton,
>>>
>>> The problem is in the LATERAL part.
>>> A while ago we had the same problem. After contact with Sybase we
>>> found the following solution:
>>>
>>> LATERAL((proc2(col1)) is interpreted internally as the derived table
>>> "LATERAL(select * from proc2(col1))"
>>>
>>> And any nested derived table (including derived tables with
>>> outreferences defined using LATERAL) used in the view definitions
>>> cannot have " select * ". They must have a fully specified select
>>> list. This is the general rule.
>>>
>>> So you need to change "LATERAL(nG_S_FuQtyCalcEquiv()) AS sp" to
>>> "LATERAL(SELECT <fields> FROM nG_S_FuQtyCalcEquiv()) AS sp"
>>>
>>> Regards,
>>> Christian
>>>
>>>
>>> "Ton van den Broek" <REMOVEtonTHE@CAPITALStvdb.nl> schreef in bericht
>>> news:4d24782a$1@forums-1-dub...
>>>> Hi Volker,
>>>>
>>>> thanks for looking at it! The procedure indeed has a RESULT clause
>>>> (and it's
>>>> used to describe the result set at least when I use the query
>>>> itself, for
>>>> example "select * from table, lateral(stored proc())" without the WITH
>>>> clause runs fine too. I tried using the WITH clause as you described
>>>> (and
>>>> even tried it with removing the result clause from the stored proc),
>>>> but all
>>>> at no avail. All queries run fine on their own, but I cannot prepend
>>>> the
>>>> create view statement to it without getting the error message :-(
>>>>
>>>> So still trying and hoping for the right clue!
>>>>
>>>> Best regards,
>>>> Ton
>>>>
>>>> "Volker Barth" <No_VBarth@Spam_GLOBAL-FINANZ.de> schreef in bericht
>>>> news:4d24688c$1@forums-1-dub...
>>>>> Ton,
>>>>>
>>>>> the SELECT list of the view definition seems fine as there are no
>>>>> non-unique columns names (which must be resolved by an alias for
>>>>> views)
>>>>> and there are no unnamed expressions. As such, the explicit aliases
>>>>> you
>>>>> have used won't help as they are not necessary.
>>>>>
>>>>> I guess the problem lies within the usage of the *procedure* in the
>>>>> FROM
>>>>> clause.
>>>>> Does this procedure have a result clause, i.e. something like
>>>>>
>>>>> CREATE PROCEDURE ...
>>>>> RESULT (col1 int, col2 varchar(100), ...)
>>>>> ?
>>>>>
>>>>> If not, it could be that just running the query works fine, as the
>>>>> query
>>>>> engine will take the result column names by executing (particularly by
>>>>> "describing") the procedure (and take them from the according
>>>>> SELECT in
>>>>> the procedure's body).
>>>>> In contrast, it could be that for a view, such a dynamical
>>>>> "describe" does
>>>>> not take place, and therefore the column names can't be known
>>>>> beforehand.
>>>>>
>>>>> If my assumption is correct, then instead of using a RESULT clause
>>>>> in the
>>>>> proc, you might as well use the WITH clause for the procedure in
>>>>> the FROM
>>>>> clause and specify the column names thereby, i.e. something like
>>>>>
>>>>> ...LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent> 0 then 'M' else...
>>>>> if artikel.isparent=0 then null else artikel.isparent endif,
>>>>> 1)) WITH (col1 int, col2 varchar(100), ...) as sp
>>>>>
>>>>>
>>>>> HTH
>>>>> Volker
>>>>>
>>>>>
>>>>>
>>>>> Ton van den Broek wrote:
>>>>>> Still trying to get it to work:
>>>>>>
>>>>>> The docs say I need to "Use a select list alias in the query
>>>>>> specification
>>>>>> or use a derived columns specifier" so I've tried the following
>>>>>> select list:
>>>>>>
>>>>>> artikel.artsiteid as "artsiteid",artikel.artnum as "artnum",
>>>>>> sp.npercolli as "npercolli",sp.siteid1 as "siteid1",sp.id1 as "id1"
>>>>>> ,sp.n1
>>>>>> as "n1",sp.siteid2 as "siteid2",sp.id2 as "id2",sp.n2 as
>>>>>> "n2",sp.siteid3
>>>>>> as
>>>>>> "siteid3",sp.id3 as "id3",sp.n3 as "n3",sp.k1 as "k1",sp.k2 as
>>>>>> "k2",sp.k3
>>>>>> as
>>>>>> "k3",sp.F2to1 as "f2to1",sp.F3to2 as "f3to2",sp.cntnpercolli as
>>>>>> "cntnpercolli",sp.futype_id as "futype_id",sp.nlaag as
>>>>>> "nlaag",sp.ndc as
>>>>>> "ndc"
>>>>>>
>>>>>> which didn't make any difference. Also I haven't got a clue what a
>>>>>> "derived
>>>>>> columns speficier" is nor how I can use it<g>. The strange thing
>>>>>> is that
>>>>>> I
>>>>>> just used my original query (which ran fine) and just prepended it
>>>>>> with
>>>>>> the
>>>>>> "create view ... as".
>>>>>>
>>>>>> Any clues are most welcome,
>>>>>>
>>>>>> Thanks in advance,
>>>>>> Ton
>>>>>>
>>>>>> "Ton van den Broek"<REMOVEtonTHE@CAPITALStvdb.nl> schreef in bericht
>>>>>> news:4d238d83$1@forums-1-dub...
>>>>>>> Hi all,
>>>>>>>
>>>>>>> I'm trying to create a view as follows, but get this errormessage
>>>>>>> below.
>>>>>>> The query itself runs without problems when pasted into ISQL. Does
>>>>>>> anybody
>>>>>>> know what is wrong here?
>>>>>>>
>>>>>>> Thanks in advance,
>>>>>>> Ton
>>>>>>>
>>>>>>> Could not execute statement.
>>>>>>> Derived table 'sp' has no name for column 1
>>>>>>> SQLCODE=-163, ODBC 3 State="42000"
>>>>>>> Line 1, column 1
>>>>>>>
>>>>>>> create view V_artikel_fuqty as
>>>>>>> select
>>>>>>> artikel.artsiteid,artikel.artnum,
>>>>>>> sp.npercolli,sp.siteid1,sp.id1,sp.n1,sp.siteid2,sp.id2,sp.n2,sp.siteid3,sp.id3,sp.n3,sp.k1,sp.k2,sp.k3,sp.F2to1,sp.F3to2,sp.cntnpercolli,sp.futype_id,sp.nlaag,sp.ndc
>>>>>>>
>>>>>>> from
>>>>>>> artikel,LATERAL(nG_S_FuQtyCalcEquiv(if artikel.isparent> 0 then 'M'
>>>>>>> else
>>>>>>> 'A' endif,artikel.artsiteid,artikel.artnum,null,
>>>>>>> if artikel.isparent=10 then artikel.am_fk_fustsiteid else null
>>>>>>> endif,
>>>>>>> if artikel.isparent=10 then artikel.am_fk_fustid else null endif,
>>>>>>> null,
>>>>>>> null,
>>>>>>> null,
>>>>>>> null,
>>>>>>> if artikel.isparent=0 then 10 else if artikel.isparent = 10 then
>>>>>>> 20 else
>>>>>>> if artikel.isparent = 20 then null else null endif endif endif,
>>>>>>> 1.0,
>>>>>>> if artikel.isparent=0 then 1 else artikel.fk_fuprmodel_id endif,
>>>>>>> 'BRO2',
>>>>>>> null,
>>>>>>> null,
>>>>>>> if artikel.isparent=0 then null else 1.0 endif,
>>>>>>> if artikel.isparent=0 then null else artikel.isparent endif,
>>>>>>> 1)) AS sp
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>
0
Volker
1/6/2011 11:57:48 AM
Hi Volker,

> That being said, it's surely not a precise hint to the particular problem 
> you have seen:)

Glad I'm not the only one thinking this <g>

> FWIW, I would like to know if the suggestion I've made (using "LATERAL ... 
> sp AS (<column-list>)") would resolve that problem, either - just in case 
> you may want to engage in further tests...

I've tried it and I think it is recognized as a valid addition but still 
needs the "SELECT <fields> FROM" prior to the stored proc label, so I stick 
to that one!

Best regards,
Ton


0
Ton
1/6/2011 9:12:35 PM
Ton van den Broek wrote:
> Hi Volker,
>
> I've tried it and I think it is recognized as a valid addition but still
> needs the "SELECT<fields>  FROM" prior to the stored proc label, so I stick
> to that one!
>
> Best regards,
> Ton
>

Thanks for testing and reporting. Generally, I prefer a working syntax, 
too:)

Regards
Volker
0
Volker
1/10/2011 8:14:17 AM
Reply:

Similar Artilces:

not error when view from localhost, but error when view from outsite
Recently I have ADSL, but my PC is assigned dynamic IP address by ADSL modem so I registered a account in www.no-ip.com to receive a internet address: http://cuongvt.myvnc.com I'm able to see album starter web site not only in my PC directly from local. (where the my website is located, but from outside also. But the problem is when I view from outsite (for example: from internet cafe or from the PC of my company), when  I clicked "register" link and type correct registering information then clicked "create user" button, my web site return a error page as below: Server Err...

error when viewing groups general properties
Hi, Has anyone seen this /portal/modules/base/skins/default/devices/default/pGroup_Identification.jsp(183,9) Attribute biggerBox invalid for tag mved according to TL this happens in iManager when viewing a group properties. I've seen it on some object before, but cannot remember how/where Is it serious and is it fixable? DB -- DominicusB architect, Southern Finland Former Netware now SLES 10 + OES ------------------------------------------------------------------------ Db, It appears that in the past few days you have not received a response to your ...

Reload.SQl has errors when creating views
Using ASA 7.02, if I use the utilities to unload the data and structure then use the resulting reload.sql to load it back in to a new database, I get an error when creating the views, the rest works fine. The issue is that the owner of the underlying table is not put on part of the joins in the views. I logged in as DBA to do the unload. The user "TableOwner" is owner of all underlying tables. Yet when I look at the SQL generated I see some of the tables are referenced by "TableOwner" but others are referenced without an owner. Is this a known issue, since co...

error creating control in design view
i am trying the popuextener. Have copied all 3 dlls in bin I am getting erro in design view  could not ... microsoft.web.atlas version=2.0.50727.0 .... the ... mainfest... does not match ( ... 0x80131040) OS: germany XP tablet ed. Same error with the preinstalled controltoolkit app.-Hanneshttp://www.preishuber.net http://weblogs.asp.net/hpreishuber...

error create view with union of tables
because a view with a union of tables cannot be generated " select id_col_a from tabla_1 union all select id_col_a from tabla_2 " This of cannot be generated. This message is the error message Seth operators may not to appear within definition of to view Raul, Sorry I appear to be missing your question and also where in PowerBuilder you are facing this issue. Could you please explain the problem in a little bit more detail ? Thanks, Sanjiv. "Raul Gil" <rgil@gruponuevomundo.com.pe> wrote in message news:4060a885$1@forums-1-dub... >...

errors errors errors..
Name: harun Email: harunbjk1903hrnatgmaildotcom Product: Firefox Summary: errors errors errors.. Comments: first of all when i launch firefox (3) i cant enter websites via writing.. for example when I type "google" and then press ctrl and enter, it crashes.. and i take lots of error reports. firefox 2 was better.. see you again , harun Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; tr; rv:1.9) Gecko/2008052906 Firefox/3.0 From URL: http://hendrix.mozilla.org/ ...

error error error
These are the 2 new errors i am getting now:   1).  Server Error in '/' Application. Configuration Error Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately. Parser Error Message: It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. This error can be caused by a virtual directory not being configured as an application in IIS.Source Error: Line 53: ...

Creating view on union subquery over OLEDB
I got error which says - Union is not allowed in subquery - when I try to create view like this:create view v_ab as SELECT id from a UNION ALL select id from bDoes anybody how can I create such view without error?ThanksPaaB Om Sri Sai Ram It should work.Can you give your table definitions,Thanks,RamThanks,RamOm Sri Sai Ram For testing need I just create table named a and b every with only one column named id and defined as integer. When I use the union query on the same tables in MS Access aplication then everything is OK - so it looks like an Access's...

General error: I/O error
From a select statement, I get the following error... Select error: SQLSTATE = S1000 [Sybase][ODBC Driver][Adaptive Server Anywhere]General error:I/O error Fatal error: No error -- transaction rolled back. Since all I'm trying to do is a simple select, It seems like the database file is corrupt. Please advise if you have seen this error or know what is going on. The environment is ASA6.0.03 and WindowsNT4. Thank you. If you look in the window of the database server, do you see any errors? Can you execute other selects? -- Reg Domaratzki Sybase Mobile and...

error while viewing view in .net
xp x64 OS sql any 10 .net provider asa 8.3 server trying to run a view with typed dataset with computed columns generate error. i will update the error message attachment in the next post. thanks vsv "vsv" <nospam@nospam.com> wrote in message news:46a7f128$1@forums-1-dub... > xp x64 OS > sql any 10 .net provider > asa 8.3 server > trying to run a view with typed dataset with computed columns generate > error. > i will update the error message attachment in the next post. > thanks > vsv > > begin 666 errorInSQLAnywh...

Error Creating Control
Very new to ASP.NET and VS so hopefully someone here can staighten me out.  I'm trying to convert some html templates we have over to the ASP.NET environment in Visual Studio.  I'm very used to Dreamweaver so not sure how this master page thing will work in my situation.  There are navigation items at the top like home - skills - services - clients, etc.  Then depending on what you select, the sub page will have nav items on the left.  So if you click services, the services page will come up with all our service options in the left nav.  I'...

how to create customererror page for remote view of all errors
sounds like a stupid question but how do I create a custom error page to view all errors on a remote computer...

Error Creating Control in design view #2
 Hi All, I try to use autocomplete with a textbox in vs2008 for a web application. I have included AjaxControlTookkit35.dll in my bin folder and I can also see it in the reference folder.In the webconfig, I have <add tagPrefix="ajaxToolKit" assembly="AjaxControlToolkit35" namespace="AjaxControlToolkit"/> When I drop a textbox on the design view and "add extender" to add the autocomplete, I get the following error:"Error Creating Control - TextBox1_AutoCompleteExtenderFailed to create designer 'AjaxControlToolkit.AutoComple...

Error, Error and more errors
Okay, all I wanted to do was test this app out and it has been a nightmare. 1.) Didn't install the sql database. I had to manually install it. 2.) I get errors when trying to add a picture to an album. "Procedure or function ngUpdatePicture has too many arguments specified" Any ideas? I've gotten more errors than this, but there is no use in bitchin. Are you using the v1.6.1 installer? Also, did you select to install the SQL Server db and give it a valid admin login? What were some of the error messages? Did you have an existing DB from a previous install? ...

Web resources about - Error creating view ... - sybase.sqlanywhere.general

Londonistan: How Britain is Creating a Terror State Within - Wikipedia, the free encyclopedia
The book encompasses a critique of multiculturalism , alleged weak policing, cultural relativism , and what Phillips calls a 'victim culture'. ...

Facebook’s Matt Kelly Describes Process Of Creating Plugin For WordPress
The plugin for WordPress launched by Facebook in June has been downloaded more than 180,000 times, and Engineer, Developer Relations Matt Kelly ...

Creating Online Ads that Drive Brand Awareness
New research shows how the creative elements of online ads impact brand awareness.

Bug - Creating a Video.Watches Action stopped working - Facebook-Entwickler
Since today executing a POST on a me/video.wacthes endpoints no longer works, it was working around 10 hours ago since then the call always returns ...

Facebook Creating Fourth-Party Privacy Rules for Facebook Connect Widget & Plugin Developers
In a recently-written post on the Facebook Developers Wiki, Facebook had added new rules for information sharing between websites that have implemented ...

The Obama campaign won by creating their own Facebook, with unique features for political campaigns.
... seen as too dangerous to nominate someone who is so thoroughly narcissistic, arrogant and dishonest. The Obama campaign won basically by creating ...

Creating A Great Startup Culture
... with any Bay Area startup these days (ping pong, beer on tap, catered lunches and Macbook Airs) – though these things are, of course, Creating ...

WIRED Creating the Nebula for iPad on the iTunes App Store
Get WIRED Creating the Nebula on the App Store. See screenshots and ratings, and read customer reviews.

Nets of freedom creating mesh networks 03.07.2013 - Flickr - Photo Sharing!
Explore Strelka Institute photo's photos on Flickr. Strelka Institute photo has uploaded 14526 photos to Flickr.

Creating the incorrigible Cartman - YouTube
"South Park" creators Trey Parker and Matt Stone were big fans of "All in the Family," a 1970s CBS sitcom. Perhaps this is why their most incorrigible ...

Resources last updated: 12/1/2015 1:48:14 AM