Subject Re: Query returning row when it shouldn't...
Author burghard_w_v_britzke
--- In Firebird-Java@yahoogroups.com, "phil_sorry_trouble_with_profile"
<time_lord@h...> wrote:
> --- In Firebird-Java@yahoogroups.com, Stefan Mühlemann <smue@k...> wrote:=

> > Phil wrote:
> >
> > > Hi, I'm a new Firebird user, using Firebird-1.5.0.4306-Win32 and
> > > Firebird-JDBC-SQL-1.5.0Beta3JDK_1.4.
> > >
> > > I have a new database , no data in it yet. When my application
> starts it
> > > looks for a value in the database, returning it if found, i.e
> something
> > > like:
> > >
> > > ...
> > > java.sql.Statement s = myConnection.createStatement();
> > > ResultSet rs = s.executeQuery("select max(PK_SN) from MYTABLE");
> > > if (rs.next())
> > > // Then the row exists - return the value:
> > > return rs.getInt("PK_SN");
> > >
> > This statement will return one row with one field with content "null".
>
> But my original statement should return zero rows! If you change the
> SQL to:
> "select PK_SN from MYTABLE order by PK_SN desc"
> then if there are no rows, then rs.next() returns false (good, what I
> expect!).
>
> >
> > Try
> >
> > ResultSet rs = s.executeQuery("select max(PK_SN) maxPKSN from
> MYTABLE");
> > if (rs.next())
> > // Then the row exists - return the value:
> > return rs.getInt("maxPKSN");
> >
> > or
> >
> > ResultSet rs = s.executeQuery("select max(PK_SN) maxPKSN from
> MYTABLE");
> > if (rs.next())
> > // Then the row exists - return the value:
> > return rs.getInt(1);
> >
> > Stefan
two things/thinks about it;
with "select max(colname) from table" you query the result of an aggregate =
function. so
you get exact one! row and that you should expect!
But, Stefan does firebird accept "select max(colname) AS rsname from table"=
(with AS
keyword)?