SQL Anywhere 16 Hello, I have a strange behavior with the following select:
Tabelle2 is a Proxy-Table. When I execute this query, all is ok.When I make a view from this query:
and execute the view
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 |
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
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.
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.