varchar to int warning while using join

[Former Member]

Gurus,

I'm joining varchar and integer fields.Due to that, I'm getting varchar to int warning.Tried using Cast in Integer field like below,warning disappears but join doesn't works.I don't want to put one more query transform for converting int to varchar.

A.fieldA=Cast(fieldB as varchar(10))

Kindly help.

Thanks

[former_member187605 · Accepted solution]

If both fields contain numeric values only, you should cast the char field to int.

Casting an int to varchar will produce a string with a leading space (for the minus sign), and that won't match the character column. That's why your join doesn't work.

You can check in the repository AL_FUNCINFO table whether the cast function will be pushed down to your  database. Use to_char / to_decimal if it doesn't.

[Former Member · Accepted solution]

I tried this query.We have a equivalent DB function for CAST.

SELECT NAME,FUNC_DBNAME FROM AL_FUNCINFO  

where name='cast'

That means, cast should work.

I don't know, why it was not pushed to DB. Do you think, prefix space will  be created, while using cast to convert INT to VARCHAR?

Thanks

[former_member187605 · Accepted solution]

Your query should be:

SELECT NAME,FUNC_DBNAME FROM AL_FUNCINFO  

where name='cast' and source = '<your database type>'

[former_member198401 · Accepted solution]

You can use the ltrim_blanks_ext() function to get rid of the blank spaces and other characters in the varchar column. Just  a thought

What do you say Dirk!!

Regards

Arun Sasi

[former_member187605 · Accepted solution]

Definitely!

One should never go blind on this type of joins. Profile your data first. And then take the right decisions, taking performance considerations (e.g. push down or not) into account.

On another note, I personally never join tables this way. I always load both sources into a staging area and while doing so make sure any join columns are converted to a common data type.

[Former Member · Accepted solution]

Thanks Dirk.

Oracle doesn't support CAST

[Former Member]

Thank you guys. It worked.

I used ltrim_blanks(to_char(fieldB)). It was pushed down to DB.

[mageshwaran_subramanian]

A.fieldA=Cast(fieldB ,'varchar(10)') would generally work. There could be couple of reasons why the join doesn't work

1.  A possible truncation during 'varchar(10)' which result in non-matching

2. There's no matching records

[Former Member]

Thanks Mahesh.

But, in validate sql, this particular join was not pushed down to DB. I doubt, join was not happening in data services engine.

[former_member198401]

Can you try modifying the Varchar column if A table to integer. This would make it easier for designer for join process.

I am assuming that you have integer values in the column of A table used for join.

Regards

Arun Sasi