on 01-20-2016 10:15 PM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
Thank you guys. It worked.
I used ltrim_blanks(to_char(fieldB)). It was pushed down to DB.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
94 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.