How to compare one double field's value with zero?
The program is:
select tdinv001.item, tdinv001.stoc
from tdinv001
where tdinv001._index2 inrange {:input.item.f} and {:input.item.t}
and tdinv001.stoc > 0.0000
...
selectdo
...
endselect

The domain of tdinv001.stoc is tcqiv1, double.
But I still would get the records which tdinv001.stoc = 0. So unimaginable was, I changed tdinv001.stoc > 0.0000 to tdinv001.stoc > 1.0000. The zero records had appeared as before. It made me mad!! What is going on:eek:!

This problem would exit in all like *.stoc, for example, tdinv700.stoc.

Hi,

Is this the full SQL code or is there still any other code in the where clause? I have just tried your code and it worked with me.

- George

Hi,

Is this the full SQL code or is there still any other code in the where clause? I have just tried your code and it worked with me.

- George


Yes, as followed is all.

select tdinv001.item, tdinv001.cwar, tdinv001.stoc
from tdinv001
where tdinv001._index2 inrange {:input.item.f} and {:input.item.t}
and tdinv001.stoc > 0.0000
and tdinv001.cwar > :old.cwar
selectdo
old.cwar = tdinv001.cwar
selectempty
old.cwar = ""
endselect

I found it would work in some records, the others would not(I just say for one same item).

Let me see if I got you right. This code does not work just for one item? All other items with tdinv001.stoc =0 are not being shown, right?

How are you checking the result of the SQL? Are you making a report?

The value of a double field directly on the data base sometimes is different of the one find in Baan,

We faced this problem here with record on tfgld106.

On the reports sometimes a -0 appears.

Doing a query on the database we found numbers like -0.000000000001254.

We solve that using double.cmp on the selectdo statment. but i do not know if in your case it will do what u need.

We also ran into the same issue. As a matter of fact it worked on our test server, but not on production. We also used double.cmp in the select statement.

Thanks for all your attention.
George7a, you had some misunderstand cause by my indefinite description. I means was it had many situations liked that but I tested one item. Thank you.
Thanks for Ralph. I used tdinv001.stoc > 0.000000000001254 would effectively. But while I tested to the zero records, it should must like tdinv001.stoc > 0.0. Not for the 0.000000000001254 . So strange!
Thanks for mark_h's reply. I didnot know why, but it is the true that use > 0.0 sometimes would effective, and sometimes not.

Thanks for all your attention.
George7a, you had some misunderstand cause by my indefinite description. I means was it had many situations liked that but I tested one item. Thank you.
Thanks for Ralph. I used tdinv001.stoc > 0.000000000001254 would effectively. But while I tested to the zero records, it should must like tdinv001.stoc > 0.0. Not for the 0.000000000001254 . So strange!
Thanks for mark_h's reply. I didnot know why, but it is the true that use > 0.0 sometimes would effective, and sometimes not.

0.000000000001254 is just an example. Can be any number(with any digits).
As me and Mark said. double.cmp can solve your problem

Thanks for your remind. The function double.cmp() can solve it. But it can be used in SELECT clause.
If the compare value not in the SELECT clause, I also can use the followed script to achieve.
if tdinv001.stoc > 0.0000 then
...
endif

Thanks for your remind. The function double.cmp() can solve it. But it can be used in SELECT clause.
If the compare value not in the SELECT clause, I also can use the followed script to achieve.
if tdinv001.stoc > 0.0000 then
...
endif

That was a bad typing on my part - I have not used it in the select clause, but inside the selectdo.

That was a bad typing on my part - I have not used it in the select clause, but inside the selectdo.

Yes, ues IF clause in selectdo is one way. How bad it is.
If it can't not be used in SELECT clause, SQL Performance has a bad efficiency.