Hello I test this example v 7.6.00.10 build 010-121-096-895 and
7.6.00.14 (last update from your cvs 18.07.2005)
run 'test packet' and CALL LAB.listRuleReferenceTree(30447)
occur error
---- Error -------------------------------
Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
Numeric value out of range;-3019 POS(236) Invalid exponent
CALL LAB.listRuleReferenceTree(30447, 3, 1388)
PS.
if I in DBPROC LAB.listRuleReferenceTree replace ':rule' on const 'rule
= 30447' and recreate dbproc, that execute will succeed
if I in DBPROC LAB.listRuleReferenceTree remove condition 'where rule
=:rule' and recreate dbproc, that execute will succeed
if I in DBPROC LAB.listRuleReferenceTree remove ' union all selec ...'
and recreate dbproc, that execute will succeed
'test packet'
DROP TABLE LAB.Test
//
CREATE TABLE LAB.Test
(
reference_range INT NOT NULL DEFAULT
SERIAL(1),
PRIMARY KEY (reference_range),
rule INT NULL,
full_attr INT NULL,
reference_flag VARCHAR(32) NULL,
normal INT NOT NULL DEFAULT 0,
CONSTRAINT normal IN (0, 1),
range_type VARCHAR(10) NOT NULL,
CONSTRAINT range_type IN ('string', 'interval', 'const', 'flag'),
l_value FLOAT(5) NULL,
u_value FLOAT(5) NULL,
interval VARCHAR(2) NULL,
CONSTRAINT interval='=' OR interval='[]' OR interval='(]' OR
interval='()' OR interval='[)' OR interval='>=' OR interval='>' OR
interval='<=' OR interval='<' OR interval IS NULL,
s_value VARCHAR(1000) NULL,
result_code VARCHAR (25) NULL,
result_name VARCHAR (100) NULL,
FromRevisionNo INT NULL,
ToRevisionNo INT NULL
)
//
insert Test (reference_range, rule, full_attr, reference_flag, normal,
range_type, l_value, u_value, interval, s_value, result_code,
result_name, FromRevisionNo, ToRevisionNo) values (32198, 30447, 1395,
'H', 0, 'interval', 4.9290E+02, NULL, '>', NULL, NULL, NULL, 3, NULL)
//
DROP FUNCTION LAB.rangeToString
//
CREATE FUNCTION LAB.rangeToString
( range INT
)
RETURNS VARCHAR
AS
VAR s VARCHAR(1000);
rule INT;
reference_flag VARCHAR(32);
normal INT;
range_type VARCHAR(10);
l_value FLOAT(5);
u_value FLOAT(5);
interval VARCHAR(2);
s_value VARCHAR(1000);
result_code VARCHAR(25);
result_name VARCHAR(100);
unit_name VARCHAR(25);
flag_name VARCHAR(50);
BEGIN
SET s = NULL;
IF range IS NULL THEN
RETURN s;
TRY
SELECT a.range_type, a.interval, a.l_value, a.u_value,
a.s_value, a.result_name
INTO :range_type, :interval, :l_value, :u_value, :s_value,
:result_name
FROM
LAB.er_reference_ranges a
WHERE
a.reference_range = :range;
CASE
WHEN range_type = 'string' THEN BEGIN
RETURN TRIM(s_value);
END;
WHEN range_type = 'interval' THEN BEGIN
CASE
WHEN interval = '[]' THEN BEGIN
SET s = '' || CHR(FIXED(l_value, 5, 2)) || ' - '
|| CHR(FIXED(u_value, 5, 2)) || '';
END;
WHEN interval = '>=' THEN BEGIN
SET s = '>= ' || CHR(FIXED(l_value, 5, 2));
END;
WHEN interval = '>' THEN BEGIN
SET s = '> ' || CHR(FIXED(l_value, 5, 2));
END;
WHEN interval = '<=' THEN BEGIN
SET s = '<= ' || CHR(FIXED(u_value, 5, 2));
END;
WHEN interval = '<' THEN BEGIN
SET s = '< ' || CHR(FIXED(u_value, 5, 2));
END;
ELSE
RETURN s;
END CASE;
END;
END CASE;
CATCH
IF $rc <> 100 THEN
STOP($rc, $errmsg);
RETURN TRIM(s);
END;
//
DROP DBPROC LAB.listRuleReferenceTree
//
CREATE DBPROC LAB.listRuleReferenceTree
( IN rule INT
)
RETURNS CURSOR AS
VAR SqlCmd VARCHAR(1000);
BEGIN
TRY
$CURSOR = 'LIST_CURSOR';
DECLARE :$CURSOR CURSOR FOR
SELECT
LAB.rangeToString(f.reference_range)
FROM
LAB.Test f
WHERE
f.rule = :rule
UNION ALL
SELECT
LAB.rangeToString(f.reference_range)
FROM
LAB.Test f
WHERE
f.rule = :rule;
CATCH
STOP($rc, $errmsg);
END;
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
