strange behavior with views and proxy-tables

[Former Member]

SQL Anywhere 16

Hello, I have a strange behavior with the following select:

select min(a.LS2MENGE), a.ls2pkey, b.LS1PKEY from  Tabelle1 as a, Tabelle2 as b 
where a.ls2pkey = b.ls1pkey


Tabelle2 is a Proxy-Table. When I execute this query, all is ok.When I make a view from this query:
create view ViewTest  
as  
select min(a.LS2MENGE), a.ls2pkey, b.LS1PKEY from  Tabelle1 as a, Tabelle2 as b 
where a.ls2pkey = b.ls1pkey


and execute the view 
Select * from viewTest


I get the following error :  Invalid use of an aggregate function (-150).
I do not understand, why this different behavior happens. 
Maybe someone can give me an hint.
Thanks in advance.
Erich

[VolkerBarth]

Hm, IMHO the first query should also raise that error (or SQLCODE -149 "Function or column reference to '%1' must also appear in a GROUP BY") because the use of MIN() as an aggregate function requires that the other columns of the select list are either

• contained in a GROUB BY clause (say, "group by a.ls2pkey, b.LS1PKEY") or
• are used with aggregate functions, too.

[Former Member]

Yes, you are right. I now copied the view from my real database:

select LSKOPF.LS1_PKEY, isnull((select min(view_Bauunternehmer.BU_KURZBEZ) from Faktura.view_Bauunternehmer where LSKOPF.ls1_BUNR = view_Bauunternehmer.BU_BAUNUM),'') as BU_KURZBEZ, ARCHIVIWDEX.BEMTEXT from Faktura.LSKOPFWERK as LSKOPF left outer join faktura.ARCHIVIWDEX on (LSKOPF.LS1_PKEY = ARCHIVIWDEX.PK_LS1_PKEY)

Here, "ARCHIVIWDEX" is the proxytable. This select runs fine. But building a view

create view View_Test as select LSKOPF.LS1_PKEY, isnull((select min(view_Bauunternehmer.BU_KURZBEZ) from Faktura.view_Bauunternehmer where LSKOPF.ls1_BUNR = view_Bauunternehmer.BU_BAUNUM),'') as BU_KURZBEZ, ARCHIVIWDEX.BEMTEXT from Faktura.LSKOPFWERK as LSKOPF left outer join faktura.ARCHIVIWDEX on (LSKOPF.LS1_PKEY = ARCHIVIWDEX.PK_LS1_PKEY)

and running the view, shows the error, I described in my first post.

[Breck_Carter]

What does view_Bauunternehmer look like?

What is the remote data source? Another SQL Anywhere 16 table?

There are many vague and strange restrictions on remote data access, some of them described in Features not supported for remote data and other specific to the capabilities of the remote data source in particular... you won't be the first to stumble upon an undocumented restriction, and the solution usually requires you to "try a different way" to reach your goal.