Hi.

EDB 2.03 b21

I have a table where I execute this sql code:

SELECT * FROM LOTES WHERE ARTICULO='171004' AND ALMACEN='02' AND EXIST>0 ORDER BY FECHA

The query results one record but this record have field EXIST=0 and I don't know why.

Thanks and regards.

Francisco
> I have a table where I execute this sql code:
>
> SELECT * FROM LOTES WHERE ARTICULO='171004' AND ALMACEN='02' AND EXIST>0 ORDER BY FECHA
>
> The query results one record but this record have field EXIST=0 and I don't know why.

What type of field is EXIST?  If it is a float it might be fractionally above 0 (but display shows 0), if it is an
integer have you tried repairing the table?

John

John.

Thank's for your response.

Field EXIST is a field type DECIMAL Scale 2

Francisco
>
> Field EXIST is a field type DECIMAL Scale 2
>

OK this is floating point value.  If you change you query to the following does it give the correct result?

SELECT * FROM LOTES WHERE ARTICULO='171004' AND ALMACEN='02' AND EXIST>0.005 ORDER BY FECHA

John

>>OK this is floating point value.  If you change you query to the following does it give the correct result?

>>SELECT * FROM LOTES WHERE ARTICULO='171004' AND ALMACEN='02' AND EXIST>0.005 ORDER >>BY FECHA

Yes, no rows with this query.

> >>OK this is floating point value.  If you change you query to the following does it give the correct result?
>
> >>SELECT * FROM LOTES WHERE ARTICULO='171004' AND ALMACEN='02' AND EXIST>0.005 ORDER >>BY FECHA
>
> Yes, no rows with this query.
>

Looks like a rounding problem.  If you are using 2 decimal places you can correct the existing data by running an update
query like update lotes set exist=round(exist to 2).  If you need exact amounts you might want to think about holding
them as BCD or Integer fields.

John

Oops that was a DBISAM answer.  I don't think Decimal 2 should allow rounding errors but I think Tim will have to
confirm.

John

John.

Thank's for your help.

John,

<< Oops that was a DBISAM answer.  I don't think Decimal 2 should allow
rounding errors but I think Tim will have to confirm. >>

No, a DECIMAL is a Currency value internally (max scale of 4), and shouldn't
have rounding issues.

--
Tim Young
Elevate Software
www.elevatesoft.com

Francisco,

<< Yes, no rows with this query. >>

Can you send me your database catalog along with this one table's files
(.edbtbl, .edbidx, and .edbblb) ?  I'd like to run this here and see what's
going on.

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com