Re: Prob with COUNT in a VIEW

In article <332b057a.7177530@gate.idg.no>, Nils Myklebust <Nils.Myklebust@idg.no> writes >David Williams <djw@smooth1.demon.co.uk> wrote: > > >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 Oops! Sorry - though perhaps they might have been - thats why I said try....rather than being definate. > >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 Using this we get >count(*) Legal >count(distinct columnname) Legal >count(unique columnname) -- same as above Legal >count(columnname) NOT LEGAL! >count(all columnname) > NOT LEGAL! There is no path through the lower route which does not pass through either the distinct or unique keywords. >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. > Because they are not valid syntax. >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 I've checked in INFORMIXDIR Guide to SQL Syntax Version 6.0 Page 1-465... " COUNT DISTINCT and UNIQUE Keywords The COUNT DISTINCT and UNIQUE keywords return the number of unique values in the column or expression, as shown in the following example. If the COUNT function encounters nulls, it ignores them. -------------------------------------------------------------------------- SELECT COUNT (DISTINCT item_num) FROM items -------------------------------------------------------------------------- Nulls are ignored unless every value in the specified column is null. If every column value is null, the COUNT keyword returns a zero for that column. " >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. > It would be there because the unique/distinct keyword is missing. Helps if I read the manuals....Thanks for helping me to learn the error of my ways...I don't know everything even after 5 years... > >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 -- David Williams