Former Member

### Joining 2 columns with different data types

Hi All,

I have a doubt regarding a join condition, hope anyone of you can help in resolving it

1st column (ACCNO) in the join is always 8 digit numeric value and

2nd column (UNIT) has mostly numeric values (but with different lengths) and some string values(Alphabhets)

We want to implement a case statement to do this where if the column has numeric value then the join should convert the UNIT column to 8 length and connect to ACCNO else it should be null

How to find if the implement this as a join condition (does Digits function in IDT work for this?)

To change the length of 2nd column, I'm planning on using RIGHT('00000000' || B_LEDGER.UNIT,8)

Client Data base: DB2

Universe is built on IDT 4.0 SP04 version

Mitch

10|10000 characters needed characters exceeded

• Former Member
Aug 04, 2015 at 10:03 PM

Why don't you convert your first column ACCNO to char and then append 0 on left.

First make sure what is the maximum length for any of the column.

Then using same you can convert the 1st column to character first and then append 0 on left for remaining characters.

Like wise for second column you can do that same.

Now both the columns are in character you can then join the same.

Thanks,

Swapnil

10|10000 characters needed characters exceeded
• Former Member Former Member

Yes, but you can expression based indexing as I mentioned earlier ðŸ˜Š. I agree that it's not best practice but it's a quick win if you've got a tight deadline. If you want to do it properly, add a column in the database that is of the correct data format.

• Aug 05, 2015 at 05:44 AM

Hi Mitch,

I believe this other strategy will work

1. Convert ACCNO to CHAR

2. Use your proposed technique to convert UNIT to a 8-character field with padded zeros.

3. JOIN the expressions

The join condition should look like this:

CHAR( CHAR( ACCNO ), 8 ) = RIGHT('00000000' || B_LEDGER.UNIT, 8)

Innermost CHAR converts ACCNO from numeric to char

Outermost CHAR trims the expression to 8 characters (maybe you won't need this one but I include it because I'm not pretty sure that the result of the innermost CHAR function will be of length 8).

Alphanumeric ocurrences of b_ledger.unit will be automatically discarded because they will not satisfy the join condition.

Regards,

Fernando

10|10000 characters needed characters exceeded
• Former Member

Requirement is to make the join work only when UNIT has numeric value

However, I tried using the method you suggested (converting ACCNO to CHAR). All I'm getting for ACCNO value is NULL values and the UNIT is coming with all values including Alpha Alphanumerical

• Former Member
Aug 06, 2015 at 04:00 PM

Performance will be horrendous. I'd recommend adding a new column and populating it appropriately.

If you can't, then take a look at Expression Based Indexing: