> but remolque is a varchar
> what about using where cast(remolque as numeric(18,0))<60
> Alan

No, the query is fine. I want to select records where "STATUS < 60",
not "REMOLQUE < 60".

'STATUS' is a smallint field. The query also fails if I use quotes for
the remolque number, i.e.

> select id,status
> from tracking
> where remolque ='563075' and status<60

returns nothing, and there *are* records meeting the condition in the
database. If I don't query the status field I can see them.

Regards,

-Benton

--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...> wrote:
>
> > > >> 1) What errors? You might want to list them here, perhaps it's
> > > >> a known thing or perhaps there's a workaround.
> >
> > > > SELECT ID,STATUS FROM TRACKING WHERE CASE='123'
> > > >
> > > > Results:
> > > > ID,STATUS
> > > > ---------
> > > > 12,20
> > > > 13,30
> > > > 14,40
> > > >
> > > > So far so good. But when I try this:
> > > >
> > > > SELECT ID,STATUS FROM TRACKING WHERE CASE='123' AND STATUS<50
> > > >
> > > > nothing is returned. I've recomputed indexes but no luck.
> >
> > > Please give us more detailed informations: platform,Tracking table
> > > structure , indexes,plan etc...
> > >
> > > Why you use CASE as table column name ?
> > > Isn't it a reserved word ?
> >
> > Sorry, actually the field name is 'REMOLQUE', but I was typing in a
> > hurry and because I wanted to be clear, I typed in a meaningful word.
> >
> > I found this bug(?) while using the just released Firebird Beta 2 for
> > Windows *and* Linux Classic version.
> >
> > As requested, here's the relevant information:
> >
> > -------------------------
> > 'TRACKING' TABLE STRUCTURE
> > -------------------------
> >
> > CREATE TABLE TRACKING (
> > ID INTEGER NOT NULL,
> > IMPEXP IMPOEXPO /* IMPOEXPO = CHAR(1) NOT NULL CHECK (VALUE
> > IN ('I','E')) */,
> > FECHA DATE NOT NULL,
> > HORA VARCHAR(5) NOT NULL,
> > TRACTOR INTEGER NOT NULL,
> > REMOLQUE VARCHAR(15) NOT NULL,
> > TIPOREM SMALLINT NOT NULL,
> > COMPANIA SMALLINT NOT NULL,
> > CAROVAC CAROVACI /* CAROVACI = CHAR(1) default 'C' NOT NULL
> > CHECK (VALUE IN ('C','V','T')) */,
> > CLIENTE INTEGER NOT NULL,
> > AGENTE INTEGER NOT NULL,
> > ORIGEN VARCHAR(15),
> > DESTINO VARCHAR(15),
> > OBSERVA VARCHAR(80),
> > CONTROL INTEGER default 0 NOT NULL,
> > STATUS SMALLINT NOT NULL,
> > OPERADOR SMALLINT NOT NULL,
> > PROPFORA PROPIOFORAN /* PROPIOFORAN = CHAR(1) DEFAULT 'P' NOT
> > NULL CHECK (VALUE IN ('P','F')) */,
> > OPERFORAN VARCHAR(25),
> > CONTRAN INTEGER default 0,
> > ODOMETRO INTEGER default 0,
> > SALIDA INTEGER default 0 NOT NULL,
> > PRONUMBER VARCHAR(20),
> > HORADOCS VARCHAR(5),
> > PRIORIDAD SMALLINT,
> > IDTRASLA INTEGER,
> > FACTURA VARCHAR(20),
> > FECHAFAC DATE,
> > PEDIMENTO VARCHAR(20),
> > REFERENCIA VARCHAR(20)
> > );
> >
> >
> > ---------
> > QUERY # 1 (RETURNS SEVERAL RECORDS WHITH STATUS < 60)
> > ---------
> > select id,status
> > from tracking
> > where remolque =563075
> >
> > ---------
> > QUERY # 2 (NOTHING IS RETURNED)
> > ---------
> > select id,status
> > from tracking
> > where remolque =563075 and status<60
> >
> >
> > ---------------------------
> > QUERY PLAN FOR BOTH QUERIES
> > ---------------------------
> > PLAN (TRACKING INDEX (TRACK_REM))
> >
> > ---------------------------
> > INDEX USED BY THE OPTIMIZER
> > ---------------------------
> > CREATE INDEX TRACK_REM ON TRACKING (REMOLQUE, STATUS, CONTROL);