Re: Prob with COUNT in a VIEW

Hello, Nils Myklebust wrote: > > David Williams <djw@smooth1.demon.co.uk> wrote: > > :>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: > :> > : I've checked in INFORMIXDIR Guide to SQL Syntax Version 6.0 Page 1-462... > : > : The syntax diagram is > : > : -->-+--COUNT(*)----------------------------------------------------+-------- > : | | > : | | > : | | > : | | > : | | > : +-COUNT---+--(-+- DISTINCT----+----+---------------+==Column-)-+ > : | | | | | Name > : | | | +---Table-------+ > : | +---UNIQUE-----+ | Name | > : | | | p. 1-506 | > : | | | | > : | | +---Synonym=====+ > : | | | Name | > : | | | p. 1-504 | > : | | | | > : +-----+ | +---View--------+ > : | + | | Name > : +-----+ | p. 1-510 > : | | > +-COUNT---+--(-+--------------+ > | | > | | > +---ALL--------+ > > The above branch is added in the manual I downloaded though, which > makes all my examples leagal, but any further explanation is nowhere > to be found. (The plus in a black square in the manual says it's an > Informix extention of the ANSI standard.) > Anybody in the know? > That was my experience too - no further explanation - so I posted the problem here. By the way I worked around the problem quite quickly by creating two views instead of one (using count (*) in one of them). But the question 'where is the illegal character' still remains for me. I programmed the following select to the view using esql/c: EXEC SQL SELECT anz INTO :anzahl FROM kapazitaet WHERE nr = '01.110'; and this is what i got (printed out by an exception handler inside the C-Program directly after the select): SQLSTATE Classcode : IX SQLSTATE Subclasscode : 000 SQLCODE : -202 Error Message : An illegal character has been found in the statement. SQLERRD [0] : 1 SQLERRD [1] : 0 SQLERRD [2] : 0 SQLERRD [3] : 1 SQLERRD [4] : 15864 SQLERRD [5] : 0 If sqlerrd[4] gives the position of the error and so the position of the illegal character then I don't know how to interpret this value. The definition of the view takes about 500 Characters and the above select statement less than 100 so where is char position 15864? I'm quite a beginner using Informix but what I take of your answers is that the chosen syntax is quite a new feature of Informix/SQL and so i begin to think it may be an informix bug. In fact I 'ported' the syntax from oracle which I was using before. Could perhaps someone else using SE 7.22 check out if he gets the same error. I get this error with every view that contains a 'count(mycolumn)' or a 'count (mytable.mycolumn)' clause. Gottfried. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Gottfried Schwieters (schwiete@quantum.de) Quantum Gesellschaft fuer Software mbH Emil-Figge-Strasse 83 ** D-44227 Dortmund ** Germany '$c$?'}][ signature fault - core dumped