Re: Prob with COUNT in a VIEW

Won't adding "and tt.ladeeinheit is not null" to the where clause and using count(*) give the result you want? 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 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