Function abs does not work

[alfer]

I'm writing a stored procedure. I have a variable named ':my_number'. I know that value of the variable is negative. I'm serching one record that has field named 'number' equal to absmy_number). The select statement like:
SET :abs_number = (SELECT TOP 1 number FROM TEST WHERE number=ABSmy_number));
returns error - 'invalid argument'
The select statement like:
SET :abs_number = (SELECT TOP 1 number FROM TEST WHERE number=-my_number));
works fine!

All instructions to see this behaviour:
CREATE TABLE TEST (
primarykey int primary key,
number decimal(19,0)
)#

INSERT INTO TEST (primarykey,number) values(1,100)#
INSERT INTO TEST (primarykey,number) values(2,200)#
INSERT INTO TEST (primarykey,number) values(3,-100)#

CREATE PROCEDURE AbsRec() AS
BEGIN
DECLARE :my_number decimal(19,0);
SET :my_number=(SELECT TOP 1 number FROM TEST WHERE number<0);
PRINT :my_number;

DECLARE :abs_number decimal(19,0);

--this returns error
SET :abs_number = (SELECT TOP 1 number FROM TEST WHERE number=ABSmy_number));
--this works
--SET :abs_number = (SELECT TOP 1 number FROM TEST WHERE number=-my_number));

PRINT :abs_number;
END;#

CALL AbsRec#

What am I doing wrong? Why the first select statement does not work?

Thanks in advance

 

[mirtheil]

I've tried your statement and it does fail. What I find interesting is that if I change the &quot;:my_number&quot; in the ABS function to a constant value (-100), then it works.
At this point, I would contact Pervasive and report this as a defect.


info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician