The point here is can I use double.cmp function in my SQL Statement in baan?

T-SQL Version - if I don't cast the value for a.t_mtst I am getting result that I don't need.

SELECT * FROM tcxlog070800 a
WHERE (a.t_pdno = 'B00000480' AND (CAST(a.t_mtst as Decimal(13,1)) < 1842.9 AND a.t_mten > 1791.2))

BAAN Version

select a.*
from cxlog070 a
where (a._index3 = {:l.clot} and (a.mtst < :l.enmt and a.mten > :l.stmt)


My Attempt to use double.cmp I am getting invalid argument error : Has anybody tried to compare decimal yet ?

select a.*
from cxlog070 a
a._index3 = {:l.clot} and ((double.cmp(a.mtst,l.enmt,0.0001) < 0


Your help will be greatly appreciated.

select a.*
from cxlog070 a
a._index3 = {:l.clot} and
( a.mtst-l.enmt <0.0001 and a.mtst-l.enmt >-0.0001)

Hi,

try the T-SQL round() (https://msdn.microsoft.com/en-us/library/ms175003.aspx) function instead of cast():

SELECT * FROM tcxlog070800 a
WHERE (a.t_pdno = 'B00000480'
AND (round(a.t_mtst, 2) < 1842.9 AND round(a.t_mten, 2) > 1791.2)

I get this pcxcsb0100( 135): Error SQL: SQLState 42I03: Unknown column 'round' (error 302)

select a.*
from cxlog070 a
where ((a._index3 = {:l.clot} and (a.mten > :l.stmt and a.mtst <= :l.stmt)) or
(a._index3 = {:l.clot} and ((round(a.mtst,2) < :l.enmt) and round(a.mten,2) > :l.stmt)))

Hi,

Try using it inside your select do using if statement. If condition doesn't satisfy use continue.

if condition then
do something...
else
continue
endif