Re: Prob with COUNT in a VIEW
Gottfried Schwieters 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.
Nils suggested:
> Won't adding "and tt.ladeeinheit is not null" to the where clause and
> using count(*) give the result you want?
Here's my $.02 US:
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.
However, the syntax COUNT (unique tt.ladeeinheit) was legal even in 4.0!
Perhaps that is what you were looking for.
--
-- Jake (Knows how to inflate 2 cents)
. .
_..-'( )`-.._
./'. '||\\\\. }\\_/{ .//||` .`\\.
./'.|'.'||||\\\\|.. )o o( ..|//||||`.`|.`\\.
./'..|'.|| |||||\\`````` \\,@,/ ''''''/||||| ||.`|..`\\.
./'.||'.|||| ||||||||||||. ||| .|||||||||||| ||||.`||.`\\.
/'|||'.|||||| ||||||||||||{ | }|||||||||||| ||||||.`|||`\\
'.|||'.||||||| ||||||||||||{ | }|||||||||||| |||||||.`|||.`
'.||| ||||||||| |/' ``\\||`` | ''||/'' `\\| ||||||||| |||.`
|/' \\./' `\\./ \\!|\\ /|!/ \\./' `\\./ `\\|
V V V }' `\\ /' `{ V V V
\\ \\ \\ V / / /
+-----------------------------------------------------------+
| Impeccable Logic: A thought process which successfully |
| resists chicken bites |
+-----------------------------------------------------------+