Joining two tables based on column having two different data types

[Former Member]

Hi,

I need to join two tables based on a column which is having different data types in the tables.

I have a column WorkGroupCode with Numeric data type in Table A and with Character Datatype in Table B.Is there any way we can change either of the datatypes to one  ( Numeric to Character or viceversa) and perform the join using IDT in Business Objects. Is there we can do it in IDT instead of changing at the database level.

Please advice!

[Former Member]

Thanks Gaurav

[Former Member]

Doing it in database will be good as the conversion of data types before joining will be faster in database.

But this can be done in IDT by using database functions.

If you are using SQL server use CAST function, but use this in the join condition.

Double click the join between two tables:

and in join condition mention

WorkGroupCode=CAST(WorkGroupCode as INT)

So this will do conversion of datatype before joining.

Thanks

Gaurav

[Former Member]

Performance of the join will be questionable. You will negate the use of any index on WorkGroupCode by casting it.

I would look at adding an extra column to the table that is of the correct datatype. Correct it in the database rather than within the join.