Hy
We want to use user-defined types, but there are many different errors.
1) After we altered the tables from decimal(16,0) to pieceround_t datatype we have issue with a case/decode expressions. (-800 Corresponding data types must be compatible in CASE expression or DECODE function.)
2) The strangest error is that at the return values of some selects, functions, the system reports an error. SQL Error (-661) : Number of variables does not match number of values returned. The functions worked fine before we defiened the data types.
3) Arithmetic Operators has a problem when we want to use the new datatypes. Can we override the arithmetic function with the new datatypes?
Does anyone have experience using user defined types? Any suggestions on what to look for?
create distinct type pieceround_t as decimal(16,0);
create distinct type piece_t as decimal(26,10);
------------------------------------------------------------------------------------------------------------------------
-- pieceround_t -> piece_t cast function
------------------------------------------------------------------------------------------------------------------------
execute procedure drop_routine( 'pieceround_t_to_piece_t' );
create procedure pieceround_t_to_piece_t( quantity pieceround_t ) returning piece_t with (not variant);
return quantity::decimal(26,10);
end procedure;
------------------------------------------------------------------------------------------------------------------------
-- pieceround_t -> float cast function
------------------------------------------------------------------------------------------------------------------------
execute procedure drop_routine( 'pieceround_t_to_float' );
create procedure pieceround_t_to_float( quantity pieceround_t ) returning float with (not variant);
return quantity::decimal(16,0);
end procedure;
------------------------------------------------------------------------------------------------------------------------
-- pieceround_t -> int cast function
------------------------------------------------------------------------------------------------------------------------
execute procedure drop_routine( 'pieceround_t_to_int' );
create procedure pieceround_t_to_int( quantity pieceround_t ) returning integer with (not variant);
return quantity::decimal(16,0);
end procedure;
------------------------------------------------------------------------------------------------------------------------
-- pieceround_t -> smallint cast function
------------------------------------------------------------------------------------------------------------------------
execute procedure drop_routine( 'pieceround_t_to_smallint' );
create procedure pieceround_t_to_smallint( quantity pieceround_t ) returning smallint with (not variant);
return quantity::decimal(16,0);
end procedure;
------------------------------------------------------------------------------------------------------------------------
-- pieceround_t -> varchar cast function
------------------------------------------------------------------------------------------------------------------------
execute procedure drop_routine( 'pieceround_t_to_varchar' );
create procedure pieceround_t_to_varchar( quantity pieceround_t ) returning varchar(255) with (not variant);
return quantity::decimal(16,0);
end procedure;
------------------------------------------------------------------------------------------------------------------------
-- piece_t -> pieceround_t cast function
------------------------------------------------------------------------------------------------------------------------
execute procedure drop_routine( 'piece_t_to_pieceround_t' );
create procedure piece_t_to_pieceround_t( quantity piece_t ) returning pieceround_t with (not variant);
if round(quantity) != quantity then
raise exception -746, 0, 'Invalid data conversion';end if;
return quantity::decimal(16,0);
end procedure;
------------------------------------------------------------------------------------------------------------------------
-- piece_t -> float cast function
------------------------------------------------------------------------------------------------------------------------
execute procedure drop_routine( 'piece_t_to_float' );
create procedure piece_t_to_float( quantity piece_t ) returning float with (not variant);
return quantity::decimal(26,10);
end procedure;
------------------------------------------------------------------------------------------------------------------------
-- piece_t -> int cast function
------------------------------------------------------------------------------------------------------------------------
execute procedure drop_routine( 'piece_t_to_int' );
create procedure piece_t_to_int( quantity piece_t ) returning integer with (not variant);
if round(quantity) != quantity then
raise exception -746, 0, 'Invalid data conversion';
end if;
return quantity::decimal(26,10);
end procedure;
------------------------------------------------------------------------------------------------------------------------
-- piece_t -> smallint cast function
------------------------------------------------------------------------------------------------------------------------
execute procedure drop_routine( 'piece_t_to_smallint' );
create procedure piece_t_to_smallint( quantity piece_t ) returning smallint with (not variant);
if round(quantity) != quantity then
raise exception -746, 0, 'Invalid data conversion';end if;
return quantity::decimal(26,10);
end procedure;
------------------------------------------------------------------------------------------------------------------------
-- piece_t -> varchar cast function
------------------------------------------------------------------------------------------------------------------------
execute procedure drop_routine( 'piece_t_to_varchar' );
create procedure piece_t_to_varchar( quantity piece_t ) returning varchar(255) with (not variant);
return quantity::decimal(26,10);
end procedure;
------------------------------------------------------------------------------------------------------------------------
-- int, smallint, float -> pieceround_t cast function
------------------------------------------------------------------------------------------------------------------------
execute procedure drop_routine( 'to_pieceround_t' );
create procedure to_pieceround_t( quantity decimal(26,10) ) returning pieceround_t with (not variant);
if round(quantity) != quantity then
raise exception -746, 0, 'Invalid data conversion';end if;
return quantity::pieceround_t;
end procedure;
------------------------------------------------------------------------------------------------------------------------
-- int, smallint, float -> piece_t cast function
------------------------------------------------------------------------------------------------------------------------
execute procedure drop_routine( 'to_piece_t' );
create procedure to_piece_t( quantity decimal(26,10) ) returning piece_t with (not variant);
return quantity::piece_t;
end procedure;
-- IMPLICIT CAST definition
CREATE IMPLICIT CAST (pieceround_t AS piece_t WITH pieceround_t_to_piece_t);
CREATE IMPLICIT CAST (pieceround_t AS float WITH pieceround_t_to_float);
CREATE IMPLICIT CAST (pieceround_t AS int WITH pieceround_t_to_int);
CREATE IMPLICIT CAST (pieceround_t AS smallint WITH pieceround_t_to_smallint);
CREATE IMPLICIT CAST (pieceround_t AS varchar WITH pieceround_t_to_varchar);
CREATE IMPLICIT CAST (piece_t AS pieceround_t WITH piece_t_to_pieceround_t);
CREATE IMPLICIT CAST (piece_t AS float WITH piece_t_to_float);
CREATE IMPLICIT CAST (piece_t AS int WITH piece_t_to_int);
CREATE IMPLICIT CAST (piece_t AS smallint WITH piece_t_to_smallint);
CREATE IMPLICIT CAST (piece_t AS varchar WITH piece_t_to_varchar);
CREATE IMPLICIT CAST (integer AS pieceround_t WITH to_pieceround_t);
CREATE IMPLICIT CAST (float AS pieceround_t WITH to_pieceround_t);
CREATE IMPLICIT CAST (smallint AS pieceround_t WITH to_pieceround_t);
drop cast ( pieceround_tas decimal(16,0));
drop cast ( decimal(16,0) as pieceround_t);
create implicit cast ( pieceround_t as decimal(16,0));
create implicit cast ( decimal(16,0) as pieceround_t);
------------------------------
Gábor Fekete
Software Engineer
Capital Systems
Budapest
------------------------------
#Informix