Cast Integer to Boolean in Informix

Question

I need to design my schema in Informix such that querying a BOOLEAN with an INTEGER type will work. e.g. SELECT id FROM mytable WHERE isavailable = ? works if I provide either a boolean False or an integer 0. I know I need to set up the schema with some kind of cast, but I'm not sure how. The reason for doing this is a bug in another part of the software I'm working with, which is not mine to fix :-(

Answer 1

Function and Cast

create function expcast_int_to_bool(i integer) returning boolean;
    if   (i is null) then return null;
    elif (i != 0)    then return 't';
    else                  return 'f';
    end if;
end function;

create explicit cast (integer as boolean with expcast_int_to_bool);

Demonstration

create table mytable
(
    id integer not null,
    isavailable boolean not null
);
insert into mytable values(1, 't');
insert into mytable values(2, 'f');

select id from mytable where isavailable = cast(0 as boolean);
select id from mytable where isavailable = cast(1 as boolean);
select id from mytable where isavailable = cast(-1 as boolean);
select id from mytable where isavailable = cast('t' as boolean);
select id from mytable where isavailable = cast('f' as boolean);

This demonstration can be run through DB-Access - and it correctly returns ID 2 for the first and last SELECT statements and ID 1 for the other three. I have not formally demonstrated that it still works when the integer or boolean literals are replaced by '?'. There is at least a moderate chance that it will work, though.