User defined data type
Gabor Fekete reported errors (-800, -661, -674) after altering columns to distinct types over decimal on 12.10.FC7, and asked about overriding arithmetic. Art Kagel said distinct types keep implicit conversion to the base type, his tests worked, and UDTs may be needed for full operations. Gabor said it works elsewhere. Art suggested opening a PMR with IBM. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
Gabor:
First, when you create a DISTINCT type it comes with an implicit conversion back to its base type, and that's working for me. Not sure what you need to accomplish with all the other casts.
If you need to operate on the new types with full arithmetic operations, string concatenation, etc. then you may have to define them as UDT's and write support functions for the operations. See the "IBM Informix User-Defined Routines and Data Types Developer's Guide" for details.
The usual operations work fine. Witness:
> create distinct type pieceround_t as decimal(16,0);
Distinct type created.
> create implicit cast (pieceround_t as decimal(16,0));
9919: Cast already exists in database.> create table pieces( one serial, two pieceround_t );Table created.
> insert into pieces values (0, 123::decimal(16,0)::pieceround_t);1 row(s) inserted.
> insert into pieces values (0, 456::decimal::pieceround_t);1 row(s) inserted.
> select * from pieces;one 1
two 123
one 2
two 456
2 row(s) retrieved.
> select two from pieces;two 123
two 456
2 row(s) retrieved.
> select two::decimal from pieces;
(expression)
123.000000000000
456.000000000000
2 row(s) retrieved.
> select two::decimal(16,0) from pieces;
(expression)
123
456
2 row(s) retrieved.
> select two::decimal(26,10) from pieces;
(expression)
123.0000000000
456.0000000000
2 row(s) retrieved.
> select two::decimal * 100 from pieces;
(expression)
12300.0000000000
45600.0000000000
2 row(s) retrieved.
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------
Art
If we didn't define the other casts, then we get the following error.
select * from pieces where two > 123;-- SQL Error (-674) : Routine (greaterthan) can not be resolved.
select decode(one, 2, 0, two) from pieces;
-- SQL Error
(-800) Corresponding data types must be compatible in CASE expression or DECODE function.
We have a similar database for other customer, and there it's work fine more then 15 years. ( Informix versions: 9.4, 10.0, 11.5, 14.10 FC3)
This database running with 12.10 FC7 . I think it's not a version issue.
------------------------------
Gábor Fekete
Software Engineer
Capital Systems
Budapest
------------------------------
Hmm. Interesting. I don't have any version as old as 14.10.FC3, but I did test in .FC6 and the results are the same. I would suggest opening a PMR with IBM. If it is a version specific bug, that's the way to get it fixed. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------