strange behavior with views and proxy-tables

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

proxy-tables view

asked 04 Nov '14, 08:31

EGlashagen
16●1●1●2
accept rate: 0%

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.

(04 Nov '14, 08:47) Volker Barth

Replies hidden

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.

(04 Nov '14, 09:03) EGlashagen

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.

(04 Nov '14, 10:17) Breck Carter

flat view

Be the first one to answer this question!

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

proxy-tables ×68
view ×31

question asked: 04 Nov '14, 08:31

question was seen: 1,779 times

last updated: 04 Nov '14, 10:17

SAP SQL Anywhere

strange behavior with views and proxy-tables

Follow this question

Related questions