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,

Mitch

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.

    Thanks,

    Swapnil

    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.

    Regards,

    Fernando

    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:


    https://www.ibm.com/developerworks/community/blogs/DB2PLSQL/entry/db2_expression_based_indexes?lang=en

    Add comment
    10|10000 characters needed characters exceeded