Re: Prob with COUNT in a VIEW

David Williams <djw@smooth1.demon.co.uk> wrote: :In article <E71JKA.MG9@nonexistent.com>, Jacob Salomon :<jake@apparel.net> writes :>The syntax COUNT (tt.ladeeinheit) struck me as problematic. I seem to :>recall (with my somewhat flakey memory ;-) that thuse used to be an :>illegal syntax in previous versions (going back to 4.x here) and this :>has always made sense to me. After all, the number of occurrences of any :>column [in the group] *must* match the number of rows in the group. If :>the syntax is now legal, it has the taste of an afterthought. : : Correct and agreed. Informix 4 uses to allow : update tab1 set (tab1.col1,tab1.col2) = (value1,value2) : ^ ^ : | | : Extra copies of tablename : : and a similar thing for inserts. : : This is no longer legal syntax. : : Tr changing it to : : count(ladeeinheit) ....assuming the column name is unique. : :>However, the syntax COUNT (unique tt.ladeeinheit) was legal even in 4.0! :>Perhaps that is what you were looking for. : :David Williams No no David. This is not related. In the update clause the extra copies of tablename are redundant and ANSI have decided they shall not be there at all. In a select statement tablenames are not allways redundant and are allowed. Even though they may sometimes be redundant they are not allways and must therefore allways be allowed. See more below. Gottfried Schwieters <schwiete@quantum.de> wrote: :Hello, : :I encountered the following SQL problem with Informix SE 7.2 :(dbaccess V 7.2, isql V 6.01) on DEC Alpha with OSF1. : :The following VIEW is created using dbaccess: : :CREATE VIEW kapazitaet (nr, bez, anz, max, mfb, typ, richt, stoer) AS : SELECT mfp.nummer, : mfp.bezeichnung, : COUNT (tt.ladeeinheit), : mfp.max_kap, : mfp.mf_bereich, : mfp.typ, : mfp.richtung, : mfp.stoer_kz : FROM mf_punkt mfp, OUTER teiltransport tt : WHERE tt.mf_element = mfp.nummer : GROUP BY mfp.nummer, : mfp.bezeichnung, : mfp.max_kap, : mfp.mf_bereich, : mfp.typ, : mfp.richtung, : mfp.stoer_kz; : :There are no problems with creation. : :But when i do (from dbaccess or isql) : : SELECT * FROM kapazitaet : :I encounter the error : : 202 An illegal character has been found in the statement. : :When I run the SELECT-Statement on which the view is based, :it gives the result I want with no errors. : :There is nothing special about the referenced tables mf_punkt :and teiltransport, they only consist of char, smallint and :int columns. : :I found out that when changing the COUNT (tt.ladeeinheit) in :the view-definition to COUNT (*) the view is ok and values :can be selected from it (but count (*) is not what I want). : :I didn't find anything in the manuals about a limitation using :COUNT (tablename.columnname) with views. : :Any suggestions? : : :Gottfried Schwieters (schwiete@quantum.de) :Quantum Gesellschaft fuer Software mbH :Emil-Figge-Strasse 83 :D-44227 Dortmund, Germany According to the manual "Informix Guide to SQL: Syntax for Windows NT" for OnLine 7.22 page 1-592 there are the following possible syntaxs for count: count(*) count(distinct columnname) count(unique columnname) -- same as above count(columnname) count(all columnname) All columnname can of course optionally have tablename., synonymname., or viewname. in front of them according to the syntaxdiagram. It is explained and clear what count(*) and count(distinct columnname) does. There is however *no* explanation of the two last options that I can find anywhere in this manual. Have anyone seen other references to this. If not Informix has to fix it. Someone claimed a few days ago that count(columnname) would only count non null columns. That sounds plausible. I don't have 7.22 available to test and 7.10 and 7.12 give me a syntax error on this statement, but let's assume that is the case. (Can someone confirm this?) Then the select statement in question should run fine. You might want to use the sqlca information to find where the illegal character is. My guess is it's on the first t in COUNT (tt.ladeeinheit). If it is it's probably a defect in the engine and you have given Informix all possible information so they can find it. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org