Re: How to improve this query?

If you are using Oracle 9, you can use:
select count(*) from mytable where nullif(myfield,'') is not null

Nullif will convert the empty strings to null and thus the statement will cover both Null and empty string.

lv

"Chris" <cs123._no_spam__at_telstra.com> wrote in message news:<gbmwb.25063$aT.23972_at_news-server.bigpond.net.au>...
> "Sybrand Bakker" <gooiditweg_at_sybrandb.nospam.demon.nl> wrote in message
> news:21k3svgttf4ujetb51q1g3ugds9q6jj45t_at_4ax.com...
> > On Fri, 21 Nov 2003 15:17:22 +0100, "Tom Miskiewicz"
> > <miskiewicz2_at_yahoo.com> wrote:
> >
> > >Hello!
> > >
> > >I want to count those records in a table, where the field I specified is
> not
> > >empty. How to make such a query efficient. Is there a different/more
> > >efficient way to achieve that, then select count(1) from mytable where
> > >myfield != NULL? Myfield is a varchar2 field. I'm using Oracle 9.
> > >
> > >
> > >Thanks
> > >Thomas
> > >
> >
> > != NULL won't work. You can't apply equality or inequality to null as
> > NULL doesn't compare to anything. SQL implements three-valued logic:
> > true, false and NULL. You'll need to use IS NOT NULL or > chr(0)
> > provided the column is indexed.
> >
> >
> > --
> > Sybrand Bakker, Senior Oracle DBA
>
> If all you want to test for is not null and your column is indexed then you
> should use
> select count(*)
> from table
> where col is not null;
>
> AFAIK this will use the index instead of a FTS
Received on Mon Nov 24 2003 - 11:52:29 CST