if I modify my function , then select 'select * from (SELECT
attrToString('sex', '2', 11) from DUAL)' succeed
DROP FUNCTION LAB.attrToString
//
CREATE FUNCTION LAB.attrToString
( attr CHAR(50)
, value INT
, revision INT
)
RETURNS VARCHAR
AS
VAR sResult VARCHAR(1000);
attr_name VARCHAR(255);
attr_type VARCHAR(10);
value_type VARCHAR(10);
interval VARCHAR(2);
l_value VARCHAR(12);
u_value VARCHAR(12);
l_unit VARCHAR(10);
u_unit VARCHAR(10);
const_value VARCHAR(50);
const_name VARCHAR(255);
synonym VARCHAR(255);
BEGIN
IF attr IS NULL OR value IS NULL OR revision IS NULL THEN
RETURN '';
SET sResult = '';
TRY
TRY
SELECT cc.attr_name, cc.attr_type, dd.value_type,
dd.interval, dd.l_value, dd.u_value, dd.l_unit, dd.u_unit,
dd.const_value, dd.const_name, CHR(null) synonym
INTO :attr_name, :attr_type, :value_type, :interval,
:l_value, :u_value, :l_unit, :u_unit, :const_value, :const_name, :synonym
FROM
LAB.er_dictionary_attributes cc,
LAB.er_attribute_default_values dd
WHERE
cc.attr = :attr
AND cc.defined = 'const'
AND dd.attr_value = :value;
CATCH
IF $rc = 100 THEN BEGIN
SELECT cc.attr_name, cc.attr_type, dd.value_type,
dd.interval, dd.l_value, dd.u_value, dd.l_unit, dd.u_unit,
dd.const_value, dd.const_name, dd.synonym
INTO :attr_name, :attr_type, :value_type, :interval,
:l_value, :u_value, :l_unit, :u_unit, :const_value, :const_name, :synonym
FROM
LAB.er_dictionary_attributes cc,
LAB.er_attribute_user_values dd
WHERE
cc.attr = :attr
AND cc.defined = 'user'
AND dd.attr_value = :value
AND dd.FromRevisionNo <= :revision
AND :revision < DECODE(dd.ToRevisionNo, NULL,
:revision + 1, dd.ToRevisionNo);
END
ELSE
STOP($rc, $errmsg);
CASE
WHEN value_type = 'const' THEN BEGIN
SET sResult = attr_name || ': ' || const_name;
END;
WHEN value_type = 'interval' THEN BEGIN
CASE
WHEN interval = '[]' THEN BEGIN
SET sResult = '' || l_value || ' ' ||
LAB.unitToString(l_unit) || ' - ' || u_value || ' ' ||
LAB.unitToString(u_unit);
END;
WHEN interval = '>=' THEN BEGIN
SET sResult = '>= ' || l_value || ' ' ||
LAB.unitToString(l_unit);
END;
WHEN interval = '<=' THEN BEGIN
SET sResult = '<= ' || u_value || ' ' ||
LAB.unitToString(u_unit);
END;
WHEN interval = '>' THEN BEGIN
/* not implement */
RETURN sResult;
END;
WHEN interval = '<' THEN BEGIN
/* not implement */
RETURN sResult;
END;
WHEN interval = '=' THEN BEGIN
/* not implement */
RETURN sResult;
END;
WHEN interval = '(]' THEN BEGIN
/* not implement */
RETURN sResult;
END;
WHEN interval = '()' THEN BEGIN
/* not implement */
RETURN sResult;
END;
WHEN interval = '[)' THEN BEGIN
/* not implement */
RETURN sResult;
END;
ELSE
RETURN sResult;
END CASE;
SET sResult = attr_name || ': ' || sResult;
IF synonym IS NOT NULL THEN
SET sResult = sResult || ' (' || synonym || ')';
/*
IF unit_name IS NOT NULL THEN
SET s = s || ' ' || unit_name;
*/
END;
ELSE
RETURN 'error: no case value';
END CASE;
CATCH
IF $rc <> 100 THEN
STOP($rc, $errmsg);
RETURN TRIM(sResult);
END;
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
