Join Char and Serial

Hi, We are running Informix 12.10.FC9W1 and have a scenario that we need to join 2 tables and join on columns where 1 column in table_char is a char(13) and the other column in table_serial is serial. Based on certain flags in table_char we know that the values in char(13) will be integer. Generally this join works though we encountered the "1213: A character to numeric conversion process failed" error and we saw it happened because the query plan changed so the optimizer took a different route to get to the data. I know it's not best practice to join this way. Though in the legacy system this is the current need and I'm trying to assist in a solution. Any suggestions on how to work with this? We tried casting in the join to a varchar though the optimizer doesn't use the index and the query takes a long time. table_serial.serial_col::varchar(13) = table_char.char_col We tried using the join order "+ORDERED" optimizer directive and this does help. Though we know this is a suggestion and the optimizer may or may not choose to follow the directive. We are looking for a more "guaranteed" fool proof approach. We also thought of using a functional index and converting the serial to a char thereby trying to force the join on char to char instead of having the engine doing the char to numeric conversion, though this doesn't work either. The engine doesn't use the index and even using a directive to force the index the query doesn't return quickly. Here is the function and index we tried creating: create function fn_to_char ( f1 integer ) returns varchar(13) with (notvariant); return f1::varchar(13); end function; create index idx_fn_table_serial_1 on table_serial ( fn_to_char (serial_col) ) ; Any suggestions? --Dave

Using the functional index, if you construct your query similar to the following: SELECT s.serial_col , c.char_col FROM table_serial AS s INNER JOIN table_char AS c ON fn_to_char( s.serial_col ) = c.char_col ; Do you get a better access plan ( I did a simple test on my machine and the plan for this query did use the indexes on both columns )? I would also change the index function to return CHAR( 13 ) instead of VARCHAR.

Hi Luis, Thank you. You are absolutely right. Using the fn_to_char() wrapper around the join column does use the index and solves the issue. Thank you for pointing that out. The query works great now. --Dave