Convert() or Cast() to Integer

[LarrySteele]

I'm having a problem with a union query that's uniting a MySQL table with a DB2 table. We're using MySQL 4 on a Windows 2003 server and DB2 6.1 on z/OS. I'm using ColdFusion MX 6.1 to handle the union.

The DB2 table contains an identity column - datatype of integer. I need this same column ~defined~ in the MySQL sql for the union. This is accomplished easily:

[tt]select 0 as row_id_column,
job_name,
file_name,
file_description,
recipient
from download_files[/tt]

When I attempt to unite this table with the DB2 table, ColdFusion complains that it [tt]"Cannot mix types 'INTEGER' and 'BIGINT' in a 'compare' binary operation."[/tt]

I tried Cast(0 as Integer) as row_id_column, but MySQL reported a syntax error in the sql statement. I checked MySQL help and I couldn't figure out what numeric datatypes I could cast() or convert() to. However, I did find a couple of examples where people had casted to unsigned. Unfortunately, unsigned is also a bigint. I even tried some numeric functions, but all appeared to return bigint numbers except for round() - which returned a double. At least by using round(), I did change the error message: [tt]"Cannot mix types 'INTEGER' and 'DOUBLE' in a 'compare' binary operation."[/tt] Obviously, I'm looking to eliminate the error message, not just change it.

Anyway, is there a way to cast(), convert(), or otherwise change a query's column datatype to integer?

Thanks in advance,
Larry

 

[TonyGroves]

All I can think of is to create a temporary table, defining the fields as you want them, select data into the table, then select from the table into the union.

Alternatively, you could try and get DB2 to return bigints.

 

[LarrySteele]

Thanks for the suggestions Tony. I'd rather not create a temp table for this. I have considered casting the DB2 column to bigint. Unfortunately, our version of DB2 is so old, that the solutions I've found so far are for 7+.

Thanks again. Hopefully someone will post with a method for casting to Integer.

- Larry

 

[LarrySteele]

I finally found the available datatypes that Cast() can cast to:

http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html

With regards to numbers, it's either signed or unsigned. Even though the document says these are integers, they're actually big integers.

- Larry

 

[LarrySteele]

SOLUTION:
The solution I chose was to cast the row_id_column to a char field in both queries. They united just fine after that.

I may not like it, but it works.

- Larry