Skip to Content
avatar image
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

Thanks in advance,


Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • avatar image
    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.



    Add comment
    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.



    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thanks for your reply

      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

  • avatar image
    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:

    Add comment
    10|10000 characters needed characters exceeded