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

asked 04 Nov '14, 08:31

EGlashagen's gravatar image

EGlashagen
16112
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
1

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
Be the first one to answer this question!
toggle preview

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

Question tags:

×63
×25

question asked: 04 Nov '14, 08:31

question was seen: 507 times

last updated: 04 Nov '14, 10:17