[Info-Ingres] How to get number of returned rows of another select query?

[tianfeichen at gmail.com]

On May 18, 5:37 pm, "Roy Hann" <specia... at processed.almost.meat>
wrote:
> <tianfeic... at gmail.com> wrote in message
>
> news:1179463228.542379.292110 at e65g2000hsc.googlegroups.com...
>
> > To get number of returned rows directly in sql, I usually write sql
> > this way:
> >  SELECT count(*) as row_count
> >  FROM ( SELECT DISTINCT "rt_airline" FROM "route" WHERE "rt_airline"
> > IS NOT NULL  )
>
> > It works in Access, SQLServer, Oracle, but get syntax error in Ingres.
>
> > Is there any way to directly get the result count of sub SELECT query?
> > Or is there any special function/keywords to do this?
>
> The ability to use a subquery in the FROM clause was introduced in Ingres
> 2006 Release 2, so this won't work with any version of Ingres earlier than
> that.
>
> Secondly--and I don't know if this is ANSI/ISO standard or not--Ingres
> insists that the subquery in the FROM clause has to have a correlation name,
> so if you take the SQL you wrote above and just add an x (say) after the
> closing bracket then it will work correctly.  Otherwise you get a misleading
> syntax error message, probably about an unexpected EOF.
>
> As an aside, this query can be written more portably as follows:
>
>    SELECT count(DISTINCT "rt_airline") FROM "route";
>
> This works equivalently to your query because unlike count(*), count(column)
> ignores nulls.
>
> Roy
>
> Ingres Users Association Spring Conference, 6 June 2007, London
> Secure your place by registering NOW via the IUA web sitehttp://www.iua.org.uk/

Hi Roy and Martin

You are correct. I'm using latest Ingres2006 release 2. Thanks for
letting me know that it won't work on previous versions of Ingres. And
Roy thank you again for the simpilifed query. It works well.

However I have another similar query gives me differnet result. For
example, to get how many value patterns out there I write:
  SELECT count(*)
  FROM ( SELECT "rt_airline" FROM "route" group by "rt_airline" having
count("rt_airline") > 1  ) as row_count
It returns a number (a single value 42) for me.

Due to it will not work in previous versions, I rewrite it to
  SELECT count("rt_airline") FROM "route" group by "rt_airline" having
count("rt_airline") > 1
This time it returns me a list of numbers (42 rows), which are not
even correct value patterns.

  SELECT "rt_airline" FROM "route" group by "rt_airline" having
count("rt_airline") > 1
will give me correct patterns.

Because there may be large amount of patterns in real database,
getting all patterns as my last query is not desired. Do you have any
idea how to tune it to work in previous version?

Thanks